Share via


Managing Connections with SQL Pass-Through

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's stored in your Windows Odbc.ini file or Windows NT registry to connect to a data source. If you change the name or the login 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 property settings for a connection, use SQLGETPROP( ) with the respective connection handle. The following table lists the connection properties you can access with SQLGETPROP( ).

Visual FoxPro Connection Properties

To Use this property 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.1
  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 ODBChdbc2 The internal ODBC connection handle that can be used by external library files (.fll files) to call the ODBC API functions.
  ODBChstmt2 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

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 table lists engine properties alphabetically along with the objects that use each property.

Engine property Applies to
Asynchronous Connection definitions: see DBSETPROP( ).
Active connections: see SQLSETPROP( ).
BatchMode Connection definitions: see DBSETPROP( ).
Active connections: see SQLSETPROP( ).
BatchUpdateCount1 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( ).
ConnectName1 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( ).
FetchMemo1 View definitions: see DBSETPROP( ).
Active view cursors: see CURSORGETPROP( ).
FetchSize1 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( ).
KeyFieldList2 Active view cursors: see CURSORSETPROP( ).
MaxRecords1 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( ).
SendUpdates2 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( ).
Tables2 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( ).
UpdatableFieldList2 Active view cursors: see CURSORSETPROP( ).
UpdateName Fields in view definitions: see DBSETPROP( ).
UpdateNameList2 Active view cursors: see CURSORSETPROP( ).
UpdateTrigger Tables: see DBGETPROP( ).
UpdateType View definitions: see DBSETPROP( ).
Active view cursors: see CURSORSETPROP( ).
UseMemoSize1 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.

See Also

Execution of ODBC Extensions to SQL | Using Automatic Transaction Mode | Using SQL Pass-Through Technology | Implementing a Client/Server Application | Designing Client/Server Applications | Upsizing Visual FoxPro Databases | Creating Views