SQLCONNECT( ) Function

Establishes a connection to a data source. There are two versions of the syntax.


SQLCONNECT([nStatementHandle])

SQLCONNECT([cConnectionName | cDataSourceName [, cUserID [, cPassword ]][, lShared]])

Parameters

nStatementHandle

Specifies that a new statement handle be created for the underlying shared connection that is represented by nStatementHandle. The new statement handle uses the settings provided from nStatementHandle instead of using the default settings.

NoteNote

You cannot create a new statement handle for a connection that is not shared. Visual FoxPro generates an error if you attempt to create a new statement handle against a nonshared connection.

You can also use SQLCONNECT( ) to obtain a new statement handle on a shared connection that was opened using SQLSTRINGCONNECT( ).

cConnectionName

Specifies a named connection created by the CREATE CONNECTION command.

When using CREATE CONNECTION or the Connection Designer to create a named connection, you can configure the connection by specifying a data source name, user ID, and password. When using the named connection in SQLCONNECT( ) as cConnectionName, you can pass values different from those in the named connection for the optional cUserID and cPassword parameters. These passed values are used instead of the user ID and password in the named connection specified by cConnectionName.

For example, suppose you create a named connection called myNamedConnection using CREATE CONNECTION or the Connection Designer and specify the values myUserID and myPassword for the named connection. For SQLCONNECT( ), you can specify myNamedConnection as cConnectionName and then specify myAltUserID and myAltPassword as an alternate user ID and password as in the following code:

SQLCONNECT("myNamedConnection", "myAltUserID", "myAltPassword")

However, if you create a named connection using a connection string with CREATE CONNECTION or the Connection Designer, use the named connection in SQLCONNECT( ), and attempt to pass optional user ID and password parameters to SQLCONNECT( ), Visual FoxPro generates the appropriate message.

When creating a new shared connection, the cConnectionName parameter refers to a named connection in the current database container (DBC).

When creating a new statement handle based on an existing shared connection, the cConnectionName parameter refers to a previous connection opened in shared mode.

If you use the cConnectionName parameter without the lShared parameter or with lShared parameter as False (.F.), you will always create a new connection that is not shared.

When using cConnectionName, if lShared is True (.T.) and the named connection is already open in shared mode, the user name and password provided should match those previously used. Otherwise, Visual FoxPro generates the appropriate message.

cDataSourceName

Specifies the name of a data source as defined in your Odbc.ini file.

You can also choose a data source from the Select Connection or Data Source Dialog Box, which appears when you call SQLCONNECT( ) without any additional arguments or with only the lShared parameter.

cUserID

Specifies the user identifier used to log on to the data source.

cPassword

Specifies the password for gaining access to the data source.

lShared

Specifies whether or not to create a shared connection.

lShared Description

False (.F.)

SQLCONNECT( ) does not create a shared connection. (Default)

True (.T.)

SQLCONNECT( ) creates a shared connection.

Numeric data type. SQLCONNECT( ) returns a positive nonzero numeric value as the statement handle if you successfully connect to the data source. SQLCONNECT( ) returns –1 if it cannot make the connection.

NoteTip

You should store this statement handle in a memory variable and use the variable in subsequent function calls that require a connection handle.

The SQLCONNECT( ) and SQLSTRINGCONNECT( ) functions return a numeric value as the statement handle rather than a connection handle. You cannot obtain a connection handle directly. You can still set and get connection properties using the SQLSETPROP( ) and SQLGETPROP( ) functions by passing the statement handle for that connection and the string, "Shared", as arguments. All other SQL functions use a statement handle instead of a connection handle.

If you issue a statement such as SQLCONNECT(cConnectionName, .T.), and a shared connection is already open with the same name, then the settings for that connection do not change to the settings stored for that connection in the database container (DBC). However, the new statement handle will use the statement settings from the DBC.

NoteNote

You must disable the Open Database Connectivity (ODBC) login dialog box to support SQL pass through with Microsoft Transaction Server. To disable the ODBC login dialog box, use the statement SQLSETPROP(nStatementHandle, 'DispLogin', 3), where cStatementHandle is the statement handle returned by SQLCONNECT( ). You can also disable the ODBC login dialog box in the Connection Designer.

Example 1

The following example assumes that an ODBC data source called MyFoxSQLNT exists and is available. SQLCONNECT( ) returns a numeric value, which is stored to a variable named gnConnHandle.

If you successfully connect to the data source, SQLCONNECT( ) returns a positive number, a dialog box appears, and SQLDISCONNECT( ) is called to disconnect from the data source.

If you cannot connect to the data source, SQLCONNECT( ) returns a negative number and displays a message.

STORE SQLCONNECT('MyFoxSQLNT', 'myUserID', 'myPassword') TO gnConnHandle
IF gnConnHandle <= 0
   = MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
   = MESSAGEBOX('Connection made', 48, 'SQL Connect Message')
   = SQLDISCONNECT(gnHandle)
ENDIF

Example 2

Each of the following examples create new shared connections. The Choose Data Source Dialog Box appears, and SQLCONNECT( ) creates the resulting connection as shared.

SQLCONNECT(.T.)
SQLCONNECT( myConnectionName, .T. )
SQLCONNECT( myDataSourceName, myUserID, myPassword, .T. )

Example 3

Each of the following examples creates a new statement handle based on an existing shared connection.

SQLCONNECT( nStatementHandleValue )
SQLCONNECT( myConnectionName, .T. )  

Community Additions

ADD
Show: