IT-Consultant-Forum (IT-Berater)

[José Roca]

 
The attached file contains the ODBC API constants, structures and declarations needed to use ODBC with the PowerBASIC compilers. It combines sqltypes.h, sql.h, sqlext.h and odbcinst.h.

The attachment also includes biblio.mdb, the Access database used in the examples.

An easier to use alternative is my TB_ODBC352 library, a collection of wrapper functions on top of the ODBC raw API. However, some PBer's prefer not to use additional DLLs.

TB_ODBC352 is available in my website:
http://com.it-berater.org/data_access/TB_ODBC3.htm

[José Roca]

 

Basic ODBC Application Steps

This section describes the general flow of ODBC applications. It is unlikely that any application calls all of these functions in exactly this order. However, most applications use some variation of these steps.

Step 1: Connect to the Data Source

The first step in connecting to the data source is to load the Driver Manager and allocate the environment handle with SQLAllocHandle.

The application then registers the version of ODBC to which it conforms by calling SQLSetEnvAttr with the %SQL_ATTR_APP_ODBCVER environment attribute.

Next, the application allocates a connection handle with SQLAllocHandle and connects to the data source with SQLConnect, SQLDriverConnect, or SQLBrowseConnect.

The application then sets any connection attributes, such as whether to manually commit transactions.

Step 2: Initialize the Application

The second step is to initialize the application. Exactly what is done here varies with the application.

At this point, it is common to use SQLGetInfo to discover the capabilities of the driver.

All applications need to allocate a statement handle with SQLAllocHandle, and many applications set statement attributes, such as the cursor type, with SQLSetStmtAttr.

Step 3: Build and Execute an SQL Statement

The third step is to build and execute an SQL statement. The methods used to perform this step are likely to vary tremendously. The application might prompt the user to enter an SQL statement, build an SQL statement based on user input, or use a hard-coded SQL statement.

If the SQL statement contains parameters, the application binds them to application variables by calling SQLBindParameter for each parameter.

After the SQL statement is built and any parameters are bound, the statement is executed with SQLExecDirect. If the statement will be executed multiple times, it can be prepared with SQLPrepare and executed with SQLExecute.

The application might also forgo executing an SQL statement altogether and instead call a function to return a result set containing catalog information, such as the available columns or tables.

The application's next action depends on the type of SQL statement executed.

Step 4a: Fetch the Results

The next step is to fetch the results.

If the statement executed in "Step 3: Build and Execute an SQL Statement" was a SELECT statement or a catalog function, the application first calls SQLNumResultCols to determine the number of columns in the result set. This step is not necessary if the application already knows the number of result set columns, such as when the SQL statement is hard-coded in a vertical or custom application.

Next, the application retrieves the name, data type, precision, and scale of each result set column with SQLDescribeCol. Again, this is not necessary for applications such as vertical and custom applications that already know this information. The application passes this information to SQLBindCol, which binds an application variable to a column in the result set.

The application now calls SQLFetch to retrieve the first row of data and place the data from that row in the variables bound with SQLBindCol. If there is any long data in the row, it then calls SQLGetData to retrieve that data. The application continues to call SQLFetch and SQLGetData to retrieve additional data. After it has finished fetching data, it calls SQLCloseCursor to close the cursor.

The application now returns to "Step 3: Build and Execute an SQL Statement" to execute another statement in the same transaction; or proceeds to "Step 5: Commit the Transaction" to commit or roll back the transaction.

Step 4b: Fetch the Row Count

The next step is to fetch the row count.

If the statement executed in Step 3 was an UPDATE, DELETE, or INSERT statement, the application retrieves the count of affected rows with SQLRowCount.

The application now returns to step 3 to execute another statement in the same transaction or proceeds to step 5 to commit or roll back the transaction.

Step 5: Commit the Transaction

The next step is to commit the transaction.

The fifth step is to call SQLEndTran to commit or roll back the transaction. The application performs this step only if it set the transaction commit mode to manual-commit; if the transaction commit mode is auto-commit, which is the default, the transaction is automatically committed when the statement is executed.

To execute a statement in a new transaction, the application returns to step 3. To disconnect from the data source, the application proceeds to step 6.

Step 6: Disconnect from the Data Source

The final step is to disconnect from the data source. First, the application frees any statement handles by calling SQLFreeHandle.

Next, the application disconnects from the data source with SQLDisconnect and frees the connection handle with SQLFreeHandle.

Note: For a more detailed information, that includes some illustrations, see the Microsoft's ODBC Programmer's Reference:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcbasic_application_steps.asp

[José Roca]

 
The following example demonstrates how to connect with a database using the ODBC API, execute a query and retrieve the results.

Code:

' ****************************************************************************************
' Example of use of the ODBC raw API functions
' SED_PBCC  -  Use the PBCC compiler
' ****************************************************************************************

#COMPILE EXE
#INCLUDE "ODBCAPI352.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL r AS INTEGER
   LOCAL hEnv AS DWORD
   LOCAL hDbc AS DWORD
   LOCAL hStmt AS DWORD
   LOCAL szBuf AS ASCIIZ * 256
   LOCAL szInConnectionString AS ASCIIZ * 1025
   LOCAL szOutConnectionString AS ASCIIZ * 1025
   LOCAL strOutput AS STRING

   ' Allocates the environment handle
   r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
   IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION

   ' Tells to the driver manager that is an application that uses the ODBC driver 3.x
   r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
   IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate

   ' Allocates the connection handle
   r =  SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
   IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate

   ' Connection string
   szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
                          "DBQ=biblio.mdb;UID=;PWD=;"
   ' Connects with the ODBC driver
   r = SQLDriverConnect (hDbc, _
                        %HWND_DESKTOP, _
                        szInConnectionString, _
                        LEN(szInConnectionString), _
                        szOutConnectionString, _
                        SIZEOF (szOutConnectionString), _
                        BYVAL %NULL, _
                        %SQL_DRIVER_COMPLETE)
   ' Check for errors
   IF ISFALSE SQL_SUCCEEDED(r) THEN
      STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
      GOTO Terminate
   END IF

   ' Allocates an statement handle
   r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
   IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
   ' Cursor type
   r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
   ' Optimistic concurrency
   r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)

   ' Generates a result set
   r = SQLExecDirect (hStmt, "SELECT TOP 20 * FROM Authors ORDER BY Author", %SQL_NTS)
   ' Check for errors
   IF ISFALSE SQL_SUCCEEDED(r) THEN
      STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
      GOTO Terminate
   END IF

   ' Parse the result set using SQLGetData to retrieve the data
   DO
      r = SqlFetch(hStmt)
      IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
      r = SQLGetData(hStmt, 1, %SQL_C_CHAR, szBuf, SIZEOF(szBuf), BYVAL %NULL)
      strOutput = szBuf & " "
      r = SQLGetData(hStmt, 2, %SQL_C_CHAR, szBuf, SIZEOF(szBuf), BYVAL %NULL)
      strOutput = strOutput & szBuf & " "
      r = SQLGetData(hStmt, 3, %SQL_C_CHAR, szBuf, SIZEOF(szBuf), BYVAL %NULL)
      strOutput = strOutput & szBuf
      STDOUT strOutput
   LOOP

Terminate:

   ' Closes the cursor
   IF hStmt THEN SQLCloseCursor(hStmt)
   ' Closes the statement handle
   IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
   ' Closes the connection
   IF hDbc THEN
      SQLDisconnect(hDbc)
      SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
   END IF
   ' Frees the environment handle
   IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)

   WAITKEY$

END FUNCTION
' ========================================================================================