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 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.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 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( ).

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.

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.

See Also

Tasks

How to: Use Automatic Transaction Mode

Reference

Execution of ODBC Extensions to SQL

Other Resources

Using SQL Pass-Through Technology

Enhancing Applications Using SQL Pass-Through Technology

Planning Client/Server Applications

Upsizing Visual FoxPro Databases

Creating Views