Accessing the ODBC API

The necessary include files for directly accessing the ODBC API are provided for advanced programmers with more complex database needs.  These include files are installed to your Emergence BASIC include directory and are named 'sql.inc', 'sqltypes.inc' and 'sqlext.inc'. These files define all of the constants and functions used by the API.

It is only necessary to include the main sqlext.inc file with $INCLUDE "sqlext.inc" to begin using the API. The other files are brought in automatically. The files are intended for use with ODBC version 3.0 or higher.  To define constants introduced in ODBC version 3.5 add

$define ODBCVER35

before the $INCLUDE statement for any file that may need those constants.

Here is a short example that uses the ODBC API directly and shows the basic steps involved in connecting to a database:
 

$INCLUDE "sqlext.inc"
 
DEF hEnv,hDBC as UINT
DEF hStmt as UINT
DEF rc,cbOut as SWORD
DEF strConnect[1023],strOut[1023] as ISTRING
 
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, hEnv)
IF(rc = SQL_SUCCESS) OR (rc = SQL_SUCCESS_WITH_INFO)
    SQLSetEnvAttr(hEnv,SQL_ATTR_ODBC_VERSION,SQL_OV_ODBC3,SQL_IS_INTEGER)
    rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, hDbc)
    IF(rc = SQL_SUCCESS) OR (rc = SQL_SUCCESS_WITH_INFO)
       'ODBC initialized try and connect to a database
        strConnect = "DSN=EBASIC TEST"
        rc = SQLDriverConnect(hDbc,NULL,strConnect,LEN(strConnect),strOut,1023,cbout,SQL_DRIVER_COMPLETE)
        IF rc = SQL_SUCCESS
            'connection established and at this point
            'SQL can be executed, etc. But we will just report success
            'and exit
            PRINT "Database connection established"
            SQLDisconnect(hDbc)
            SQLFreeHandle(SQL_HANDLE_DBC,hDbc)
            SQLFreeHandle(SQL_HANDLE_ENV,hEnv)
        ELSE
            'error so free the connection handle and environment handle
            PRINT "Error connecting"
            SQLFreeHandle(SQL_HANDLE_DBC,hDbc)
            SQLFreeHandle(SQL_HANDLE_ENV,hEnv)
        ENDIF
    ELSE
        'error so free the environment handle
        PRINT "Error allocating connection handle"
        SQLFreeHandle(SQL_HANDLE_ENV,hEnv)
    ENDIF
ELSE
    PRINT "Error allocating environment handle"
ENDIF
 
PRINT "Any key to close"
DO:UNTIL INKEY$ <> ""
END

Combining API access with the command pak.

You can mix direct API calls with commands from the command pak. For example dbGet, dbGetNext, and dbGetPrev take only a statement handle allocated with SQLAllocHandle.  To use commands that require a database pointer it is necessary to create a UDT that contains both the environment and connection handles.  The UDT is defined as:

TYPE DBConnection

  DEF hEnv as UINT

  DEF hDbc as UINT

ENDTYPE

Create a variable of type DBConnection and fill in the members with the connection and environment handles returned by SQLAllocHandle.  For example in the above code we use dbExecSQL after a connection is established with a few modifications:
 

DEF DBC as DBConnection
...
           'connection established and at this point
            DBC.hEnv = hEnv
            DBC.hDbc = hDbc
            hstmt = dbExecSQL(DBC, "SELECT * FROM addresses")