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