How to get records in Asynchronous and non-batchmode?

[pcwc66]

I'm trying to get records in Asynchronous and non-batchmode. I cannot figure out how to use sqlmoreresults().

I have the following coding:

lnhandle = sqlconnect("test&quot
?sqlsetprop(lnhandle,"Asynchronous", .t.)
?sqlsetprop(lnhandle,"Batchmode", .f.)
if sqlexec(lnhandle, "select top 1000000 field1 from table1", "curtable1&quot = 1
do while .t.
if sqlmoreresults(lnhandle) = 2
exit
else
?"Still getting"
endif
enddo
endif

Thank you for any help

 

[MirekZvolsky]

I never worked with such something, however I understand it so:

sqlmoreresults() is designed to take more as one recordset into foxpro cursors, if the sql-command in sqlexec() returns more as one recordset.
But sql-command in your example returns 1 recordset (1 cursor) only. So, why to use sqlmoreresults() ?

sqlexec() in synchronous mode waits until sql-command is finished. So it can return only 1 (=OK) or -1 (=failed). But in asynchronous mode it can return 0 too - still executing. You need call sqlexec() again, f.e. in DO WHILE cycle, until you get result =1.

VFP help:
In asynchronous mode, you must call SQLEXEC( ) repeatedly until it returns a value other than 0

I understand it so (but I am not sure), that in repeated call of sqlexec() you should use only 1-st parameter (connection handle).

 

[pcwc66]

Hi mirekzvolsky,

I believe you're right that I don't need to use sqlmoreresults(). I have already changed the coding to use a loop. However, I have one question about this. Since it is in a loop, how to allow user to click on objects on a form when VFP is so busy to retrieving data?

Thank you.

 

[Mike Lewis]

Pcwc66,

Here is an example of the code which I use for this:

lnConn = SQLCONNECT(’MyConnect’)
SQLSETPROP(lnConn,’Asynchronous’,.T.)
lcCommand = ’SELECT * FROM BigTable’
DO WHILE .T.
lnReply = ;
SQLEXEC(nConn,lcCommand,’csrResult’)
DO CASE
CASE lnReply = 0
* query is still running
* perform some other processing here
* (eg, display a progress message)
CASE lnReply < 0
* error
MESSAGEBOX ;
(’Unable to retrieve data from server’)
EXIT
CASE lnReply > 0
* query has finished
EXIT
ENDCASE
ENDDO

Hope this helps.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[MirekZvolsky]

to pcw66:

no, no. VFP isn't busy. Busy is SQL Server.
DO WHILE SQLEXEC()=0 use only, if you have no other job for foxpro at that time.
Otherwise, you can do for example following:

DO WHILE .T.
IF oApp.lServerBusy AND SQLEXEC(Hnd)#0
oApp.lServerBusy = .F.
WAIT CLEAR
ENDIF
IF NOT oApp.lServerBusy ;
AND NOT EMPTY(oApp.cCommandToExecute)
SQLEXEC(Hnd,oApp.cCommandToExecute)
oApp.cCommandToExecute = ''
ENDIF
IF EMPTY( oApp.cCommandToExecute )
READ EVENTS && timer CLEARs EVENTS f.e. every 400 ms
ELSE
WAIT WINDOW 'Next SQL command is required, but we wait for the previous, which is not finished yet' NOWAIT
ENDIF
ENDDO

PROCEDURE GetDataButton.Click
oApp.cCommandToExecute = 'SELECT...'
CLEAR EVENTS

Then, I think, form controls can be active, while fetching data. F.e. if SELECT command needs 1000 ms, then timer will 3-times check, if the command has finished. If user presses same or other button, which requires new SELECT in next 1500 ms, READ EVENTS will be always active. But if user presses such button not 1500 ms, but 700 ms later (as the 1-st button), then he will for 300 ms see the WAIT WINDOW message. (Or, if you will make some stack array for non-started sql-commands, READ EVENTS can be active always.)