New FoxShow

Good news, everyone (any Futurama fans out there?): Andrew MacNeill has a new FoxShow discussing integrating VFP and Silverlight with Uwe Habermann. Uwe and Venelina Jordanova are presenting several sessions on this topic at Southwest Fox 2010, including a free one-day post-conference workshop I’m sure will be very popular (I’m planning on attending it).

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.

Southwest Fox 2010 News

Some exciting news about Southwest Fox 2010:

• Speakers and sessions have been announced. Organizers once again had a very tough time selecting from the excellent submissions. I’m personally really looking forward to the Windows 7 sessions presented by Craig Boyd and Steve Ellenoff and Bo Durban’s Direct2D session. Actually, I want to see every session, but of course that’s not possible. Thank goodness one of the requirements of speaking at Southwest Fox is providing white papers so you still get the content from every session, even those you miss.
• New this year is a Web Development track with seven regular sessions, two pre-conference sessions, and four post-conference sessions (see below) in this track alone.
• We also have three new (well, new to Southwest Fox) speakers, continuing our tradition of trying to invite new speakers every year: Kevin Cully, Uwe Habermann, and Venelina Jordanova. Kevin is likely familiar to many people, as he has attended Southwest Fox before and hosted a conference himself a few years ago. Although Uwe and Venelina are new to North American conferences, they both are veteran speakers at European conferences, including both German and Prague DevCons.
• A free one-day "VFP to Silverlight" workshop is being held the day after Southwest Fox ends (Monday, October 18), sponsored by the German FoxPro User Group (dFPUG) and presented by Uwe and Venelina. I’m planning on attending this and suspect it’ll be very popular.
• Southwest Fox platinum sponsor Tomorrow's Solutions, LLC is offering a scholarship of $150 for one new attendee. Also, White Light Computing has changed their scholarship to cover TWO attendees this year.

See you in Phoenix in October!