Performing Queries Asynchronously

VFP makes it easy to perform asynchronous queries against remote databases such as SQL Server. Why would you want to do that? With a synchronous query (the default), you have to wait until SQLEXEC() returns before you can execute any other code. What if you want to display the progress of record retrieval? What if you want to give the user the opportunity to stop a long query, especially if they realize they made a mistake and don’t want to wait a long time and retrieve a lot of records because of it? Asynchronous queries give you these abilities because execution returns to VFP after retrieving a configurable number of records.

You can switch to async mode any time, even after opening a connection, using SQLSETPROP(). You likely also want to configure how many records to retrieve at a time using CURSORSETPROP(). Here’s an example (this code assumes the connection handle is stored in lnHandle and lnAsyncRecords contains the number of records to retrieve):

sqlsetprop(lnHandle, 'Asynchronous', .T.)
cursorsetprop('FetchSize', lnAsyncRecords, 0) 

You might also want to set the packet size; you may have to use trial and error to determine the optimum size for your network:

sqlsetprop(lnHandle, 'PacketSize', 12288)

When you’re in async mode, you have to retrieve records in a loop until either the query finishes or you decide to cancel it. You can tell when SQLEXEC() has completed because it returns a non-zero value; positive means it was successful, negative means it failed for some reason. You can also check to see how many records were retrieved on each pass by passing an array to SQLEXEC(); the second element in that array contains the number of records.

Here’s an example. This code assumes the SQL statement to execute is in lcSelect, the cursor to put the results into is in lcCursor, and the connection handle is in lnHandle. The commented area is where you’ll put code specific to your application to see if the user wants to cancel, such as if they click the Cancel button in a progress dialog you’ve displayed (such a dialog could also show the total number of records retrieved so far; that value is in lnCount). Once the loop is done, llReturn is .T. if the query succeeded, llCancelled is .T. if the user cancelled the process, and lnCount is the total number of records fetched (which of course you can also get from RECCOUNT(lcCursor)).

llDone      = .F.

lnCount     = 0

llCancelled = .F.
llReturn    = .F.
do while not llDone
    lnStatus = sqlexec(lnHandle, lcSelect, lcCursor, laInfo)
    if lnStatus <> 0
        llDone   = .T.
        llReturn = lnStatus > 0
    endif lnStatus <> 0
    if laInfo[2] > 0
        lnCount = lnCount + laInfo[2]
* see if user wants to cancel e.g. press Esc or click Cancel
* set llContinue to .F. if we’re supposed to cancel
        if not llContinue
            sqlcancel(lnHandle)
            llDone      = .T.
            llReturn    = .F.
            llCancelled = .T.
        endif not llContinue
    endif laInfo[2] > 0
enddo while not llDone

There’s one caveat to this approach: some database engines don’t like it when you use SQLCANCEL() on the connection handle. With Pervasive, for example, the next time you use SQLEXEC(), you get an “invalid cursor state” error. The only solution in that case is to close and reopen the connection.

Fortunately, there’s a workaround for this. Rather than performing a query on the connection handle, use a new statement handle. To do that, start by making the connection handle sharable by passing .T. as the last parameter in SQLCONNECT() or SQLSTRINGCONNECT(). Then, just before starting the loop, get a new statement handle by calling SQLCONNECT() with the existing connection handle. In the following code, assume the connection handle is stored in lnConnectionHandle rather than in lnHandle:

lnHandle = sqlconnect(lnConnectionHandle)

Now SQLEXEC() is using a new statement handle shared from the original connection handle.

At the end of the loop, close the statement handle using SQLDISCONNECT(lnHandle); that doesn’t disconnect the connection, it just frees the resources used by the statement handle.