SQLExec(), SQLPrepare()

Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.

`SQLExec()`, `SQLPrepare()`

The “exec” here stands for “execute”. SQLExec() sends commands to the server to be executed. SQLPrepare() lets you speed up that execution by compiling the command before it’s executed. You send it on ahead to the server, which compiles it and keeps it ready to go.

Usage

nSuccess = SQLExec( nConnectionHandle [, cCommand
                    [, cResultCursor ] )
nSuccess = SQLPrepare( nConnectionHandle, cCommand
                       [, cResultCursor ] )

Parameter	Value	Meaning
nConnectionHandle	Numeric	The existing connection handle for the remote database being queried.
cCommand	Character	The command or commands to pass to the server.
cCommand	Omitted	Execute a command previously passed to the server with SQLPrepare().
cResultCursor	Character	The name of a cursor in which to store the results of cCommand.
cResultCursor	Omitted	Name the result cursor SQLResult.
nSuccess	Positive	The number of result sets returned.
	0	Still executing commands.
	-1	An error occurred.

If the server can handle it, you can pass multiple commands with a single SQLExec(). In this case, there may be multiple result sets to return. The way SQLExec() handles this depends on the settings for both Asynchronous and Batch—see “Your Server Will be With You in a Moment” for an explanation of these settings; you actually set them with SQLSetProp().

When you’re using asynchronous mode, you call SQLExec() over and over until it returns something other than 0. If you’re also in batch mode, you then use SQLMoreResults() to retrieve additional result sets.

The first result set returned goes in a cursor named SQLResult, unless you pass a different name in cResultCursor. In batch mode, additional results simply tack a digit on the end of the first cursor name. You can do the same thing in non-batch mode or you can pass a different cursor name with SQLMoreResults().

SQLPrepare() is handy for times when you’ll want to execute the same command over and over. Send it to the server once and get it compiled, then execute it as needed. It lets you create the equivalent of a parameterized view, but manage it with SPT. The biggest downside we see is that the compiled command is available only until you send along a different command.

In asynchronous mode, you can cancel a command before it finishes with SQLCancel().

If you’re an old FoxPro hacker, like we are, there’s one gotcha in passing commands to the server. ODBC doesn’t understand the abbreviations of FoxPro commands we’re all accustomed to. If you pass a query like “SELE * FROM Customers,” it’ll fail because ODBC has no clue what SELE means.

Example

* In synchronous mode
nHandle = SQLConnect("Northwinds")
IF nHandle > 0
   IF SQLExec(nHandle, "Select * FROM Customers") = 1
      BROWSE
   ELSE
      WAIT WINDOW "Trouble at the pass"
   ENDIF
ELSE
   WAIT WINDOW "Can't connect"
ENDIF

* Set up a parameterized SPT command
* Use the same connection as above
cCountry = ""
IF SQLPrepare(nHandle, ;
      "SELECT * FROM Customers WHERE Country = ?cCountry") = 1
   cCountry = "UK"
   IF SQLExec(nHandle) = 1
      * Got all UK Customers
      BROWSE TITLE "Customers in the UK"
   ENDIF
   cCountry = "USA"
   IF SQLExec(nHandle) = 1
      * Got all US Customers
      BROWSE TITLE "Customers in the USA"
   ENDIF
ENDIF