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 ] )

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 )

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