SQLGetProp(), SQLSetProp()

These two functions let you check and change properties of a remote connection. Unlike the other functions whose names begin with SQL, these apply both to views and to SQL Pass-Through, though some properties are relevant really for only one or the other.

Usage

uResult = SQLGetProp( nConnectionHandle, cProperty )
nSuccess = SQLSetProp( nConnectionHandle, cProperty
                       [, uValue ] )

A connection is Visual FoxPro’s way of communicating with ODBC and the back-end server. It has an assortment of properties that determine how the communication proceeds. The term “property” is used here somewhat loosely. Connections are not objects in the OOP sense. But these properties are characteristics of the connection, so it’s not a big stretch.

The Help file has a pretty good list of the meanings for these items and their legal values, as does IntelliSense, so we won’t repeat that here. Instead, we’ll focus on the mistakes.

In VFP 3, the optional third parameter in SQLSetProp() is a real hit-or-miss proposition. Sometimes when you omit it, things work as documented—the specified property gets restored to its default value. For WaitTime and BatchMode, the property changes, but the value it changes to is not the documented default. (WaitTime goes to 0; BatchMode goes to .F.) In the worst cases, you get an error message telling you "Property value is out of bounds"—we see this for Transactions and DispLogin. In Visual FoxPro 5 and later, these problems are fixed.

Some of these problems may be bad documentation, while others may reflect differences in ODBC drivers or servers. But the bottom line is the omitted third parameter doesn’t work as advertised in VFP 3.

DataSource is documented as read-write, but we haven’t been able to change it—we suspect the docs are wrong, not the product. QueryTimeOut and PacketSize are also documented as read-write. They are, but only for a connection handle of 0 to let you change the defaults. They’re read-only for an existing connection.

The optional 0 parameter for nConnectHandle lets you cut down on labor. If you always want asynchronous, non-batch connections, change your default and then all the new connections you create in this session will use the new defaults. The only way we’ve found to make these changes persist across sessions is to use the Tools-Options dialog. Set them there, then choose Set as Default, and your choices get saved in the Registry.

We’d advise you not to cut down on labor within your programs by trying to abbreviate the Property keywords, however, even though FoxPro seems perfectly willing to let you. You can reduce the property names to the minimum unique characters—DispLogin and DispWarnings can be abbreviated to DispL and DispW, while ConnectString and ConnectTimeOut can only be reduced to ConnectS and ConnectT, and QueryTimeOut can be reduced to the preposterous Q. C’mon, folks, translating this code into English is hard enough without cryptic pseudo-savings. In the Command Window, we’ll never type an extra character, either, but don’t let this unreadable gobbledygook enter your code! With the addition of IntelliSense in VFP 7, you have no excuses anymore, since you can type the minimum number of characters and end up with the full string in your code anyway.

Example

nHandle = SQLConnect("Northwinds")
? SQLGetProp(nHandle,"Asynchronous")&& Displays .F., the default
* Make it asynchronous
? SETSetProp(nHandle,"Asynchronous",.T.)

See Also

Create Connection, CursorGetProp(), CursorSetProp(), DBGetProp(), DBSetProp(), SQLConnect()