Managing Connections with SQL Pass-Through

Article
07/09/2007

When you create a remote view, you choose an ODBC data source name or a connection name that is then used as a pipeline to the remote server upon activation of the view. To access remote data directly with SQL pass-through, you must have the handle for an active connection. A handle is a value that refers to an object; in this case, the handle refers to a data source connection. To obtain a handle, you request a connection to the data source using the SQLCONNECT( ) or SQLSTRINGCONNECT( ) function. If the connection is successful, your application receives a connection handle for use in subsequent Visual FoxPro calls.

Your application can request multiple connections for one data source. You can also work with multiple ODBC data sources by requesting a connection to each data source you want to access. If you want to reduce the number of connections used, you can configure remote views to share the same connection. You disconnect from a data source with the SQLDISCONNECT( ) function.

Tip

Visual FoxPro relies on the definition of the ODBC data source that is stored in Windows to connect to a data source. If you change the name or the logon information for a data source, keep in mind that these changes might affect whether an application using that data source can connect to the desired remote server.

Controlling Environment and Connection Properties

The client/server environment is established each time you open Visual FoxPro. The environment exists for that session of Visual FoxPro and disappears when you close Visual FoxPro. The client/server environment contains:

Global properties that act as the prototypes for new connections.
Error values for errors that occur outside a specific connection.

You can use a handle of 0, the environment handle, to refer to global property settings. You use the SQLSETPROP( ) function to control default property settings in the connection environment and properties within individual connections. The methods you use for entering SQLSETPROP( ) values are consistent for both the environment and individual connections:

Properties specified with one of two values can use a logical value (.F. or .T.) for eExpression.
A property name can be abbreviated to its shortest unambiguous truncation. For example, you can use "Asynchronous", "Asynch", or "A" to specify the Asynchronous property. Property names aren't case-sensitive.

When you initiate a connection, the connection inherits default connection property values. You can use SQLSETPROP( ) to change these values.

Setting Connection Properties

To view the current settings for a connection, use the SQLGETPROP( ) function with the respective connection handle. The following table lists the connection settings you can access with SQLGETPROP( ).

Visual FoxPro Connection Properties

To	Use this setting	Purpose
Display the information used to create the active connection	ConnectString	The login connection string.
	DataSource	The name of the data source as defined by ODBC.
	Password	The connection password.
	UserID	The user identification.
Work with shared connections	ConnectBusy	True (.T.) if a shared connection is busy; false (.F.) otherwise.
Control interface display	DispLogin	Controls when the ODBC Login dialog box is displayed.
	DispWarnings	Controls whether non-fatal warning messages are displayed or not.
Control time intervals	ConnectTimeout	Specifies the time (in seconds) to wait before returning a connection time-out error.
	IdleTimeout	Specifies the idle time-out interval (in seconds). Qualifying active connections are deactivated after the specified time interval.¹
	WaitTime	Controls the amount of time in milliseconds that elapses before Visual FoxPro checks whether the SQL statement has completed executing.
	QueryTimeout	Controls the time (in seconds) to wait before returning a general time-out error.
Manage transactions	Transactions	Determines how the connection manages transactions on the remote table.
Control fetching of result sets into view cursors	Asynchronous	Specifies if result sets are returned synchronously (the default) or asynchronously.
	BatchMode	Specifies if SQLEXEC( ) returns result sets all at once (the default), or individually with SQLMORERESULTS( ).
	PacketSize	Specifies the size of the network packet used by the connection.
Display internal ODBC handles	ODBChdbc²	The internal ODBC connection handle that can be used by external library files (.fll files) to call the ODBC API functions.
	ODBChstmt²	The internal ODBC statement handle that can be used by external library files (.fll files) to call the ODBC API functions.

1. If in manual transaction mode, the connection is not deactivated.

2. If a connection is deactivated, the ODBChdbc and ODBChstmt values are no longer valid. Do not free or drop these values in a user library.

For more information on connection properties and their default settings, see SQLSETPROP( ).

Controlling Environment Property Settings

The values you set in the Visual FoxPro environment using handle 0 are used as prototypes or default values for each subsequent connection or attachment.

To view the current environment property settings, use SQLGETPROP( ) with 0 as the value for the handle.

The following example displays the current environment's WaitTime property setting:

? SQLGETPROP(0, "WaitTime")

If you set the DispWarnings property to true (.T.), Visual FoxPro displays any environment errors from that point on, and also sets DispWarnings to true (.T.) for newly created connections.

Although the values you set for handle 0 are used as prototype values for each connection, you can also set custom properties for an individual connection by issuing SQLSETPROP( ) for that connection handle. The exceptions are the ConnectTimeout, PacketSize, and DispLogin properties, whose settings the connection inherits at connect time. If you change the setting of the ConnectTimeout, PacketSize, or DispLogin property, the new setting isn't used until you reconnect.

Controlling Connection and View Objects

You can control connections and views by setting properties on the connection or view object. Properties that control databases, tables, table fields, view definitions, view fields, named connections, active connections, or active view cursors are called engine properties. You can display or set engine properties with one of the following Visual FoxPro functions:

To display engine properties use	To set engine properties use
CURSORGETPROP( )	CURSORSETPROP( )
DBGETPROP( )	DBSETPROP( )
SQLGETPROP( )	SQLSETPROP( )

The function you use depends on whether you want to set properties on object 0 (connection 0 and cursor 0), the object definition in a database (named connection or view definition), or the active object (active connection or active view cursor). The following table lists objects and the functions you use to set properties on each object:

To set properties for	Connection	View
Object 0	SQLSETPROP( )	CURSORSETPROP( )
Object definition in a database	DBSETPROP( )	DBSETPROP( )
Active object	SQLSETPROP( )	CURSORSETPROP( )

Engine Properties

The following diagram lists engine properties alphabetically along with the objects that use each property.

FoxPro Properties Table Sample

The following table lists engine properties alphabetically along with the functions you can use to set those properties.

Engine property	Applies to
Asynchronous	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
BatchMode	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
BatchUpdateCount¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
Buffering	Active view cursors: see CURSORSETPROP( ).
Caption	Fields in tables, fields in view definitions: see DBSETPROP( ).
Comment	Databases, tables, fields in tables, view definitions, fields in view definitions, connection definitions: see DBSETPROP( ).
CompareMemo	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
ConnectBusy	Active connections: see SQLGETPROP( ).
ConnectHandle	Active view cursors: see CURSORGETPROP( ).
ConnectName¹	View definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ). Active view cursors: see CURSORGETPROP( ).
ConnectString	Connection definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ).
ConnectTimeout	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
Database	Active view cursors: see CURSORGETPROP( ).
DataSource	Connection definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ).
DataType	Fields in view definitions: see DBSETPROP( ).
DefaultValue	Fields in tables, fields in view definitions: see DBSETPROP( ).
DeleteTrigger	Tables: see DBGETPROP( ).
DispLogin	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
DispWarnings	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
FetchAsNeeded	View definitions: see DBSETPROP( ). Active view cursors: see CURSORGETPROP( ).
FetchMemo¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORGETPROP( ).
FetchSize¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
IdleTimeout	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
InsertTrigger	Tables: see DBGETPROP( ).
KeyField	Fields in view definitions: see DBSETPROP( ).
KeyFieldList²	Active view cursors: see CURSORSETPROP( ).
MaxRecords¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
ODBCHdbc	Active connections: see SQLGETPROP( ).
ODBCHstmt	Active connections: see SQLGETPROP( ).
Offline	View definitions: see DBGETPROP( ).
PacketSize	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
ParameterList	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
Password	Connection definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ).
Path	Tables: see DBGETPROP( ).
Prepared	View definitions: see DBSETPROP( ).
PrimaryKey	Tables: see DBGETPROP( ).
QueryTimeout	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
RuleExpression	Tables, fields in tables, view definitions, fields in view definitions: see DBSETPROP( ).
RuleText	Tables, fields in tables, view definitions, fields in view definitions: see DBSETPROP( ).
SendUpdates²	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
ShareConnection	View definitions: see DBSETPROP( ). Active view cursors: see CURSORGETPROP( ).
SourceName	Active view cursors: see CURSORGETPROP( ).
SourceType	View definitions: see DBGETPROP( ). Active view cursors: see CURSORGETPROP( ).
SQL	View definitions: see DBGETPROP( ). Active view cursors: see CURSORGETPROP( ).
Tables²	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
Transactions	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
Updatable	Fields in view definitions: see DBSETPROP( ).
UpdatableFieldList²	Active view cursors: see CURSORSETPROP( ).
UpdateName	Fields in view definitions: see DBSETPROP( ).
UpdateNameList²	Active view cursors: see CURSORSETPROP( ).
UpdateTrigger	Tables: see DBGETPROP( ).
UpdateType	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).
UseMemoSize¹	View definitions: see DBSETPROP( ). Active view cursors: see CURSORGETPROP( ).
UserID	Connection definitions: see DBSETPROP( ). Active connections: see SQLGETPROP( ).
Version	Databases: see DBGETPROP( ).
WaitTime	Connection definitions: see DBSETPROP( ). Active connections: see SQLSETPROP( ).
WhereType	View definitions: see DBSETPROP( ). Active view cursors: see CURSORSETPROP( ).

1. Property primarily useful for remote views; setting has no effect on performance of local views. You can set this property on local views if you want to pre-set the property on the local view and then upsize later to create a remote view.

2. Property must be set for updates to be sent to remote data source.

Using Transactions with Remote Data

You can wrap transactions around updates, deletes, and inserts to remote data using one of two methods:

Automatic transaction mode
Manual transaction mode

The transaction mode you select determines how Visual FoxPro handles transactions on your local machine.

Nested Transactions

Visual FoxPro supports transactions nested up to five levels for local data. A single level of transaction support is built into SQL pass-through.

If your server supports multiple levels of transactions, you can use SQL pass-through to manage transaction levels explicitly. Explicit transaction management is complex, however, because it can be difficult to control the interaction between the built-in transaction and the timing of remote server transactions. For more information on explicit transaction management, see your ODBC documentation.

Managing Connections with SQL Pass-Through

Controlling Environment and Connection Properties

Setting Connection Properties

Controlling Environment Property Settings

Controlling Connection and View Objects

Engine Properties

Using Transactions with Remote Data

Nested Transactions

See Also

Tasks

Reference

Other Resources

Additional resources