Need correct syntax for SELECT TOP 100 command

[jrumbaug]

I am using VFP 8
MSDE is the back end remote data.
I am trying SQL pass through.
This code works but I get 25,000 + names.

thisform.connectionhandle = SQLCONNECT( "DonnaSQL")
thisForm.tempcursor = "ListOfNames"

SQLEXEC( thisform.connectionhandle , ;
"SELECT lname , fname, mname, idnumber ;
FROM PT_INFO;
ORDER BY LNAME+FNAME+MNAME " , ;
thisForm.tempcursor )



I would like to limit the number of records returned to the first 100, but adding the "TOP 100" causes SQLEXEC() to return -1,which is suppose to mean "a connection level error occurs"

SQLEXEC( thisform.connectionhandle , ;
"SELECT TOP 100 lname , fname, mname, idnumber ;
FROM PT_INFO;
ORDER BY LNAME+FNAME+MNAME " , ;
thisForm.tempcursor )

I would appreciate any suggestions. Even a non pass through aproach.

Jim Rumbaugh

 

[bborissov]

The select statement is wrong, you must eneter the ORDER BY fields separated by comma, not as you do in INDEX ON

SQLEXEC( thisform.connectionhandle , ;
    "SELECT TOP 100  lname , fname, mname, idnumber  ;
     FROM PT_INFO;
        ORDER BY LNAME,FNAME,MNAME"  ,  ;
       thisForm.tempcursor   )

Also it sis a good practice to check what happens when you use SPT:

TEXT TO lcSQL NOSHOW
     SELECT TOP 100  lname , fname, mname, idnumber
            FROM PT_INFO
            ORDER BY LNAME,FNAME,MNAME
ENDTEXT

IF SQLEXEC( thisform.connectionhandle,m.lcSQL,thisForm.tempcursor) < 0
   AERROR(laError)
   MessageBox(laError[1,2])
ENDIF

Borislav Borissov

 

[jrumbaug]

Borislav
Thank you. I have learned 3 important things.
1) the correct ORDER BY syntax
2) using the TEXT TO command to set a variable
3) error check for successful execution

But the TOP 100 statement still causes an error. With some research I found an acceptable answer. I issue the command:

CURSORSETPROP("MaxRecords" ,100 , 0 )

This limits the cursour to the first 100 records. It is my opion that the TOP 100 statement does not work in the SQLEXEC function. Perhaps because the statement is executed by my MSDE backend, not by VFP. I am happy with the results from CURSORSETPROP() and consider this question answered.

Jim Rumbaugh

 

[baltman]

Try this. It isn't eactly what you want but it should help you to get there.

TEXT TO lcSQL NOSHOW
SELECT lname , fname, mname, idnumber
FROM PT_INFO
ORDER BY LNAME,FNAME,MNAME
WHERE LNAME in (
SELECT TOP 100 LNAME FROM
PT_INFO ORDER BY LNAME DESC)
ENDTEXT

The problem with CURSORSETPROP is that the whole query is downloaded before you throw most of it away. Not terribly efficient.

Brian

 

[bborissov]

Jim,
What is the error?
I didn't see what could cause the error in this statement.

Borislav Borissov

 

[Mike Lewis]

Jim,

It is my opion that the TOP 100 statement does not work in the SQLEXEC function.

I can assure you that it does work. The syntax is more strict than with VFP -- in particular, you must put the TOP N clause immediately after the keyword SELECT, whereas VFP lets you place it anywhere in the command. But I see you have got that right.

It's a pity that you are using MSDE rather than the full SQL Server. MSDE does not come with any of the interactive tools of the full product. If you had SQL Server, you would simply run the command in the Query Analyzer and see a diagnostic message to tell you what's wrong.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[mkrausnick]

Even though you don't have the Query Analyzer, you should still be able to use AERROR() to display information about the error. So what does AERROR() tell you?

Mike Krausnick
Dublin, California

 

[jrumbaug]

Thank you all for your help. Here's the code as it stand's now. In the form INIT:

LOCAL lcSQL, nCount
thisform.connectionhandle = SQLCONNECT( "DonnaSQL")
thisForm.tempcursor = "ListOfNames"

* load SQL search string into variable lcSQL
TEXT TO lcSQL NOSHOW
SELECT TOP 100 lname , fname, mname, idnumber
FROM PT_INFO
ORDER BY LNAME,FNAME,MNAME
ENDTEXT

CURSORSETPROP("MaxRecords" ,100 , 0 )

ThisForm.Mysqlexec( lcSQL )

ThisForm.textLastName.SetFocus

which calls

PARAMETERS lcSQL
LOCAL nCount
nCount = 0
DO WHILE SQLEXEC( thisform.connectionhandle , lcSQL , thisForm.tempcursor )< 0
IF nCount > 10
AERROR(laError)
MessageBox( " TRY AGAIN LATER : " + laError[1,2])
ThisForm.Destroy
ENDIF
WAIT "Re-try to connect to server, attempt number " +STR(nCount) WINDOW TIMEOUT 1
nCount = nCount + 1
ENDDO
Thisform.mygridsetup()

If I leave out "TOP 100", it works fine. If I put it in, I get the countdown to 10, then the message box with the AERROR() message:

CONECTIVITY ERROR:[MICROSOFT][ODBC SQL SERVER DRIVER][SQL SERVER] LINE1:INCORRECT SYNTAX NEAR 100

I think I'll try it at my other location and see if it happens with SQL 2005 Express

Jim Rumbaugh

 

[Mike Lewis]

Jim,

I must admit this is puzzling. I can't see any reason why you should get that syntax error.

I also can't see why you are doing the SQLExec() in a loop. If it gives an error the first time, it will always give an error. Still, that doesn't affect the main problem.

I'd still like to see the same query running in Query Analyzer.

MIke

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[mkrausnick]

I agree with Mike L. - the code looks OK to me. I don't think the CURSORSETPROP is doing anything (it requires a workarea number, which does not yet exist), but again, that's not the main problem.

Is it possible that there is a difference in SQL processing or syntax between MSDE and SQL Server? I don't use MSDE so I don't know the answer to that.

Or maybe it's some odd behavior related to the form. Try running the SQL statement in a small PRG from the VFP command window:

? SQLCONNECT( "DonnaSQL" )     && Returns handle#, s/b "1"
SQLEXEC(1,"SELECT TOP 100 lname , fname, mname, idnumber FROM PT_INFO ORDER BY LNAME,FNAME,MNAME","cursor1")
BROWSE

If that works, then try putting all the related code including the SQLCONNECT into a command button's click event and executing it manually by clicking the button. I'm thinking maybe it has to do with the code being in the INIT event - for example maybe it's referencing form variables that aren't created yet.

Another idea is to put ALL the code, including the SQLCONNECT, into the MYSQLEXEC function and use local variables instead of form variables - again, suspecting some unusual behavior in the form's INIT event relating to form variables.

Post back with your results.


Mike Krausnick
Dublin, California

 

[jrumbaug]

Thanks to all of you for your help and suggestions.

I ran the code at work where the backend is SQL 2005 Express. The code works as expected. I will now assume it is a MSDE problem.

I also can't see why you are doing the SQLExec() in a loop.

I'm still fresh at SQL pass through. It's a re-hash of my old FILELOCK routine. I had one case where I could not log on to the server (Donna's) computer because of something she was doing. So I thought I'd put in a retry loop. If it's a waiste of time I may try a simple abort if fail.

I don't think the CURSORSETPROP is doing anything (it requires a workarea number

VFP help says

If you specify 0 for nWorkArea, CURSORSETPROP( ) sets the environment setting used for all subsequent tables or cursors

It seems to work

Once again, thanks to all, especially Mike Lewis, that said," I can assure you that it does work " (TOP 100 with SQLExec ) . I consider this problem solved.

Jim Rumbaugh