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.
cNamedConnection	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.
cConnectionString	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.
nConnectionHandle	0	Close all open connections.
nReturnValue	1	Connection successfully closed.
nReturnValue	–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
**************************************************