|
|
Open
Database Connectivity (ODBC) is a widely accepted application programming
interface (API) for database access. It is based on the Call-Level
Interface (CLI) specifications from X/Open and ISO/IEC for database APIs
and uses Structured Query Language (SQL) as its database access language.
TB_ODBC3.DLL is a collection of wrapper functions to ease the
use of the ODBC API—which has a deserved fame of being notoriously
difficult to use—,
with the PowerBasic compilers. The declarations, constants and structures,
both for the wrappers and the raw ODBC API functions are contained in the file
TB_ODBC3.INC.
The raw ODBC API functions always return an integer error code as the
result of the function, whereas the wrappers store this result internally
in the global integer variable ODBC_ERROR_HRESULT, allowing to return the
retrieved values as the result of the functions instead of having to pass
a variable by reference as one of the parameters. Another important
difference is that the raw ODBC API functions always use asciiz strings
and buffers, whereas the wrappers use dynamic strings in general. You can
freely mix the use of the wrappers with the API functions if needed.
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. The basic
application steps are shown in the following illustration.
Step
1: Connect to the Data Source
The first step in any application is to connect to the data source. This
phase, including the functions it requires, is shown in the following
illustration.
The first step in connecting to the data source is to load the Driver
Manager and allocate the environment handle with SQLAllocHandle. For more
information, see
Allocating the Environment Handle.
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. For more information, see
Allocating a Connection Handle and
Establishing a Connection.
The application then sets any connection attributes, such as whether to
manually commit transactions. For more information, see
Connection Attributes.
Note: Using the wrapper functions the code will be:
| |
Dim hEnv As Dword
Dim hDbc As Dword
Dim ConStr As String
' Allocates an environment handle (it also calls SQLSetEnvAttr
' to tell the driver manager that is an application that uses
' the ODBC driver 3.x).
hEnv = OdbcAllocEnv
' Allocates the connection handle
hDbc = OdbcAllocConnect(hEnv)
If IsFalse hDbc Then GoTo Terminate
' OdbcOpenConnection connects with the ODBC driver
' using SQLDriverConnect.
ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
OdbcOpenConnection(hDbc, ConStr)
If OdbcError Then
Print OdbcGetConnectionErrorInfo(hDbc)
GoTo Terminate
End If
|
Step
2: Initialize the Application
The second step is to initialize the application, as shown in the
following illustration. 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. For more information, see
Considering Database Features to Use.
All applications need to allocate a statement handle with SQLAllocHandle,
and many applications set statement attributes, such as the cursor type,
with SQLSetStmtAttr. For more information, see
Allocating a Statement Handle and
Statement Attributes.
| |
Dim hStmt as Dword
' Allocates an statement handle
hStmt = OdbcAllocStmt(hDbc)
' Cursor type
OdbcSetCursorType hStmt, %SQL_CURSOR_KEYSET_DRIVEN
' Concurrency type (optimistic)
OdbcSetCursorLockType hStmt, %SQL_CONCUR_VALUES
|
Step
3: Build and Execute an SQL Statement
The third step is to build and execute an SQL statement, as shown in the
following illustration. 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. For more information, see
Constructing SQL Statements.
If the SQL statement contains parameters, the application binds them to
application variables by calling SQLBindParameter for each parameter. For
more information, see
Statement Parameters.
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. For more information, see
Executing a Statement.
Note: The simplest way of doing it is to use OdbcExecDirect to
execute an SQL query, but as we have not bound the columns, we will have
to retrieve the column values using OdbcGetData.
| |
' Generates a result set
OdbcExecDirect hStmt, "SELECT TOP 20 * FROM Authors
ORDER BY Author"
If OdbcError Then
MsgBox OdbcGetStatementErrorInfo(hStmt)
End If
|
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. For more
information, see
Uses of Catalog Data.
The application's next action depends on the type of SQL statement
executed.
| |
Type
of SQL statement |
Proceed to |
| |
|
|
|
|
SELECT or catalog function |
"Step 4a: Fetch the Results" |
|
|
UPDATE, DELETE, or INSERT |
"Step 4b: Fetch the Row Count" |
|
|
All other SQL statements |
"Step 3: Build and Execute an SQL Statement" (this topic) or "Step 5:
Commit the Transaction" |
Step
4a: Fetch the Results
The next step is to fetch the results, as shown in the following
illustration.
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.
For a complete description of retrieving results, see
Retrieving Results (Basic) and
Retrieving Results (Advanced).
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.
Note: In our example, we are retrieving the data using the wrapper
function OdbcGetDataString.
| |
' Parses the result set
OdbcMoveFirst hStmt
While Not OdbcEof
Print OdbcGetDataString(hStmt, 1)" ";
Print OdbcGetDataString(hStmt, 2)" ";
Print OdbcGetDataString(hStmt, 3)
OdbcMoveNext hStmt
Wend
' Closes the cursor
OdbcCloseCursor hStmt
|
Step
4b: Fetch the Row Count
The next step is to fetch the row count, as shown in the following
illustration.
If the statement executed in Step 3 was an UPDATE, DELETE, or INSERT
statement, the application retrieves the count of affected rows with
SQLRowCount. For more information, see
Determining the Number of Affected Rows.
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, as shown in the following
illustration.
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. For more information, see
Transactions.
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, as shown in the
following illustration. First, the application frees any statement handles
by calling SQLFreeHandle. For more information, see
Freeing a Statement Handle.
Next, the application disconnects from the data source with SQLDisconnect
and frees the connection handle with SQLFreeHandle. For more information,
see
Disconnecting from a Data Source or Driver.
Finally, the application frees the environment handle with SQLFreeHandle
and unloads the Driver Manager. For more information, see
Allocating the Environment Handle.
Note: Using the wrapper functions, the code will be as follows:
| |
' Closes the statement handle
OdbcCloseStmt hStmt
' Closes the connection
If hDbc Then OdbcCloseConnection hDbc
' Frees the environment handle
If hEnv Then OdbcFreeEnv hEnv
|
Note:
To parse big result
sets, it is faster to first bind the columns instead of using OdbcGetData
or OdbcGetDataString.
|
|
Downloads