INDEX

SQLConnect(), SQLDisconnect(), SQLStringConnect()

These functions are the first and last ones you'll need to use SQL Pass-Through (the client-server mode in which you send commands directly to the back end). SQLConnect() and SQLStringConnect() let you hook up with remote data via ODBC. SQLDisconnect() closes a connection opened by one of the others. Think of SQLConnect() and SQLStringConnect() as dialing a phone and SQLDisconnect() as hanging up the phone.

Usage

nConnectionHandle = SQLConnect( cDataSource [, cUserId [,                                  cPassword ] ] ) nConnectionHandle = SQLConnect( [ cNamedConnection ] ) nConnectionHandle = SQLStringConnect( [ cConnectionString ] )

Parameter	Value	Meaning
cDataSource	Character	The name of a data source as seen in the ODBC Administrator.
cUserId	Character	The user ID with which to log in to the server.
cPassword	Character	The password with which to log in to the server.
cNamedConnection	Character	A named connection from the current database.
	Omitted	Presents an ODBC dialog allowing the user to choose which ODBC Connection or Data Source to use.
cConnectionString	Character	A complete connection string, as needed to connect to the desired server. Specify the data source by including "dsn=cDataSource" in the string or leave it out and specify the driver name and other connection information to use a "DSN-less" connection.
	Omitted	A different dialog box than the one above is presented, allowing the operator to choose among file and machine data sources.
nConnectionHandle	–1	Connection attempt failed at the connection level.
	–2	Connection attempt failed at the environment level.
	Positive Number	Connection succeeded and can be accessed through nConnectionHandle.

Continuing the phone analogy, using SQLConnect() with a named connection is like pushing your speed dial button. Using SQLConnect() with a separate data source, user ID and password is like pushing all the buttons yourself. Using SQLStringConnect() is like calling the operator and giving her all the information.

If you omit the parameters, the two functions behave slightly differently. SQLConnect() brings up the "Select Connection or DataSource" dialog, letting you pick either. However, SQLStringConnect() offers the SQL Data Sources dialog, letting you choose from the existing ODBC data sources. Pressing Cancel in either dialog aborts the process, returning –1 to indicate no connection was established.

Be sure to hold on to the return value of these functions. The return value is your key to doing anything useful. All the other SQL Pass-Through functions take the connection handle as the first parameter. If you lose track of your connection handles, there's no way to find out what handles are out there, short of trying to use one and trapping for an error. The example at the end of this section remedies this problem—it shows a connection manager class that keeps track of connections for you. You instantiate the class once, and then let it manage all your connections. Using this class, you'd never call SQLConnect() or SQLStringConnect() yourself.

Usage

nReturnValue = SQLDisconnect( nConnectionHandle )

Parameter	Value	Meaning
nConnectionHandle	Positive	The connection handle for the connection to be closed.
	0	Close all open connections.
nReturnValue	1	Connection successfully closed.
	–1	Connection level error.
	–2	Environmental error.

SQLDisconnect() is the function to hang up the phone. You pass the handle and FoxPro says "goodbye" for you.

The documentation says this function returns a negative value when it can't close the connection. We haven't seen that in practice. Our experience is that, if something's going at on our end that prevents us from closing the connection (like an asynchronous command is still executing), we get an error message, not a return value of –1.

This is a documentation bug. There appears to be an error in the help topic for SQLDisconnect; it states "Use SQLCONNECT(nConnectionHandle) to reconnect to existing handles without generating duplicates." However, you get a "Function argument value, type, or count is invalid" error when you pass a numeric value to SQLCONNECT.

Example

* We recommend you use something like the Connection Manager * class shown here to manage your connections. This solves * the problem of there being no way to find out what connections * exist. You'll probably want to beef this class up, at least  * with a custom error handler. * We created this class as a visual class. The code shown here * was exported by the Class Browser. You'll find this class * in the Downloads as Connect.VCX. ************************************************** *-- Class:        connmgr (h:\hacker\testcode\connect.vcx) *-- ParentClass:  custom *-- BaseClass:    custom *-- Connection Manager. Used to keep track of all active  *-- connections. * DEFINE CLASS connmgr AS custom        *-- The number of open connections    PROTECTED nconnectioncount    nconnectioncount = 0    Name = "connmgr"      *-- Holds the open connections    PROTECTED aconnections[1]        *-- Open a connection    PROCEDURE openconnection       * Open a connection - based on parameters       LPARAMETERS cSource, cUserId, cPassword          * cSource is either datasource name or named connection         LOCAL nHandle         DO CASE          CASE NOT EMPTY(DBC()) AND ;             INDBC(cSource, "CONNECTION")             * named connection - go for it             nHandle = SQLCONNECT(cSource)          CASE TYPE("cUserId") = "C" AND TYPE("cPassword") = "C"             nHandle = SQLCONNECT(cSource, cUserId, cPassword)          CASE TYPE("cUserId") = "C"             nHandle = SQLCONNECT(cSource, cUserId)          CASE TYPE("cPassword") = "C"             nHandle = SQLCONNECT(cSource, "", cPassword)          OTHERWISE             nHandle = SQLCONNECT(cSource)       ENDCASE         IF nHandle > 0          * successful connection          * so add this connection to our list          This.nConnectionCount = This.nConnectionCount+1          DIMENSION This.aConnections[This.nConnectionCount, 4]          This.aConnections[This.nConnectionCount,1] = nHandle          This.aConnections[This.nConnectionCount,2] = cSource          This.aConnections[This.nConnectionCount,3] = cUserId          This.aConnections[This.nConnectionCount,4] = cPassword         ENDIF       RETURN nHandle    ENDPROC        *-- Close an open connection    PROCEDURE closeconnection       * Close an open connection. Make sure to remove it from        * the open list       LPARAMETERS nHandle          * Which connection to close         * Find out if we have such a connection       LOCAL nConnectionRow, nResult       nConnectionRow = ASCAN(This.aConnections, nHandle, -1, ;          -1, 1, 8)             IF nConnectionRow > 0          * Found it, now get rid of it          nResult = SQLDISCONNECT(nHandle)          ADEL(This.aConnections, nConnectionRow)          This.nConnectionCount = This.nConnectionCount-1          IF This.nConnectionCount > 0             DIMENSION This.aConnections[This.nConnectionCount,4]          ENDIF       ELSE          * Turn off the error handler and send the code for          * error we want. This lets AERROR() return the right          * information, but keeps the user from seeing a           * message.          LOCAL cOldError          cOldError=ON("ERROR")          ON ERROR *          ERROR 1466          ON ERROR &cOldError          nResult = -2       ENDIF         RETURN nResult      ENDPROC        *-- List all open connections    PROCEDURE list       * List the open connections in the active window       ?"Connection handle", ;        "Data Source/Connection","Userid","Password"         IF This.nConnectionCount>0          LOCAL nConn          FOR nConn = 1 TO This.nConnectionCount             ? This.aConnections[nConn, 1]             ?? This.aConnections[nConn, 2] AT 19             IF TYPE("This.aConnections[nConn, 3]") = "C"                ?? This.aConnections[nConn, 3] AT 42             ENDIF             IF TYPE("This.aConnections[nConn, 4]") = "C"                ?? This.aConnections[nConn, 4] AT 49             ENDIF          ENDFOR         ELSE          ? "No Open Connections"       ENDIF    ENDPROC        PROCEDURE count       * Return the number of connections       RETURN This.nConnectionCount    ENDPROC        PROCEDURE aconnect       * Return an array containing all active connection        * information.       * Since we're already storing it in an array,        * this is as simple as copying the array         PARAMETER aReturn       EXTERNAL ARRAY aReturn         LOCAL nReturn   && Return value is # of rows or -1         DO CASE           CASE TYPE("aReturn[1]") = "U"          * Bad parameter, can't do it             nReturn = -1          CASE This.nConnectionCount = 0             nReturn = 0          OTHERWISE             * Redim the array to get rid of old data             DIMENSION aReturn[1]             * Copy from the property into the parameter             ACOPY(This.aConnections, aReturn)             nReturn = ALEN(aReturn, 1)       ENDCASE         RETURN nReturn    ENDPROC     ENDDEFINE * *-- EndDefine: connmgr **************************************************

See Also

Create Connection, SQLExec(), SQLGetProp(), SQLSetProp()

View Updates

Copyright © 2002 by Tamar E. Granor, Ted Roche, Doug Hennig, and Della Martin. All Rights Reserved.