Company logo

FOXSTUFF

Advice, tips, techniques and downloads for Visual Foxpro developers.

Home | Contact

Simplifying SQL Pass-Through

Use this time-saving class to eliminate SPT house-keeping

Version 1.1 of SQLSend is now available; see below for details of the new features.

If you have ever used SQL Pass-Through (SPT) in a Visual FoxPro application, you'll probably agree that it's a reasonably easy feature to use. But it does involves a certain amount of repetitive house-keeping. In particular, there are several chores that must be carried out each time you call SLQEXEC():

These tasks only need a few lines of code. But they are precisely the sort of repetitive actions that should be done just once, in a generic function or class. This is exactly what our SQLSend class does.

Note that SQLSend is not tied to any particular back end. It works just as happily with Microsoft SQL Server, MySQL, Oracle or any other ODBC-compliant database.

How it works

With SQLSend, you specify the connection parameters once only, usually near the start of your application. From then on, whenever you wish to send a command to the server, you simply place the command in a property of the class, then call the class's Send method. This opens the connection if it is not already open, sends the command to the server, and checks the reply. If the server reports an error, the Send method stores the error code and error message in a pair of properties where they are easily accessible.

The following is an example of the code that you might write to instantiate the class:

loSend = NEWOBJECT("sqlsend","sqlsend.prg") 
loSend.ConnectName = "connSQLData"

Here, the connection object is called connSQLData. This is assumed to be held in the currently selected database. If it is not, you can store the name, and optional path, of the database (that is, the DBC) in the DBCName property. The class will then open the database if it is not already open.

If you prefer to make the connection directly from a DSN, you would use the DSN, UserID and Password properties instead of ConnectName and DBCName. If you supply a DSN but omit the user ID and password, the user will be prompted for the missing items at run time.

The class does not actually open the connection until the first time you send a command to the server. You do that by storing the command in question in the SQL property, then calling the Send method, like so:

loSend.SQL = "SELECT * FROM authors" 
loSend.Send

What happened?

To determine the result of the command, check the SQLResult property after calling the Send method. If this contains 0, all went well; 1 indicates a connection error (usually something wrong with the connection object or your login parameters); and 2 means that the server (or ODBC driver) didn't like the command that you sent it. This last case could be anything from bad syntax to an attempt to access a non-existent table.

If SQLResult returns 2, the ErrorNo property will tell you the error code which the server reported. Also, the ErrorMess property will contain the server's error message. These codes and messages are specific to the server, so you will have to consult the relevant documentation to find out more about them.

More properties

There are a couple of other properties that you might wish to set. You can use the Warnings property to stipulate whether warning messages should be displayed. These messages relate to errors reported by the server. A value of 2 means that any such errors will be immediately displayed on the screen. They do not invoke your error handler, and your program will continue to run normally once the user has acknowledged the message. You can then use the ErrorNo and ErrorMess properties to deal with the errors programmatically.

If you set Warnings to 1, the messages will not be displayed, but ErrorNo and ErrorMess will still contain details of the error. The default setting of 0 means that the class will respect the DispWarnings setting in the connection object.

Another useful property is CursorName. You can use this to store the name of the cursor which is to hold the result set (if any) returned by the server. If more than one result set is returned, the cursors will all have the same name but with a sequence number appended to them (the second cursor will have 1 appended to its name, and so on). If you leave the property blank, the cursor name will be SQLResult, again with sequence numbers appended if there is more than one of them (this default name is supplied by VFP, not the SQLSend class).

A problem with data sessions

A common way of using the SQLSend class is to instantiate it as a global object but to call the Send method from within a form. This raises a problem. Any cursor created by the class will only be visible in the 'default' data session (that is, data session 1). If the form has its own data session, it won't be able to see the cursors.

To get round this, simply set the class's DataSession property before calling the Send method. Typically, you would set this to the form's Session ID, like so:

loSend.DataSession = THISFORM.DataSessionID

The class does not retain this setting, so you will have to execute the above line each time you call the Send method. Remember, you only need to do this if the SQLSend object's data session is different from the form's, and then only if the server returns one or more cursors from your command.

Disconnecting

You can call the Disconnect method if you want to close the connection. Usually, you won't need to do this as it will be done automatically when the SQLSend object is destroyed. However, in some circumstances, you might want to disconnect before your application terminates. You can call Disconnect at any time; if you subsequently call the Send method again, a new connection will be created automatically.

New features in version 1.1

We issued a new version of SQLSend in September 2002. This includes the following new features:

Note that, with both the InitSQL and the SQL properties, you can send multiple commands at the same time if the server supports this. For example, with SQL Server, you can place a whole series of commands in either of these properties, with no special delimiters or separators between them. SQLSend will send the commands as a batch.

How to download SQLSend

Click the link below to download SQLSEND.PRG version 1.1. This is an 8 KB program file that contains the entire class. Just copy it to your development directory, add it to your project, and instantiate it as described above. And don't forget to send us your feedback (see our Contacts page).

Download now

Mike Lewis Consultants Ltd. May 2002. Revised September 2002 and January 2006.

More Visual FoxPro articles | Crystal Reports articles | Recommended books | Visual FoxPro consultancy | Contact us

FoxStuff is maintained by Mike Lewis Consultants Ltd. as a service to the VFP community. Feel free to download and use any code or components, and to pass around copies of the articles (but please do not remove our copyright notices or disclaimers).

The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly in your applications.

© Copyright Mike Lewis Consultants Ltd.