A statement handle is a pointer to a data structure that contains information about a single SQL statement. This information includes:
Statement handles are the workhorses of ODBC. They are used to process and contain the information derived from all the SQL statements you use in your application. Notably, statement handles are used in ODBC API function calls to bind parameter markers, result data set columns to application variables, prepare and execute SQL statements, retrieve metadata about result data sets, retrieve (fetch) results from result data sets, and retrieve diagnostic information.
Each SQL statement used in an application must have its own statement handle, and each statement handle used needs to be associated with only one connection handle. However, multiple statement handles can be associated with a single connection handle.
Some drivers limit the number of active statements they support. The SQL_MAX_CONCURRENT_ACTIVITIES option in f90SQLGetInfo specifies how many active statements a driver supports on a single connection. A statement is defined as active if it has results pending, where results are either a result set or the count of rows affected by an INSERT, UPDATE, or DELETE statement, or if data is being sent with multiple calls to f90SQLPutData.
In the following example program, you set up a connection to the database (BOOKSALES.MDB) that comes with your distribution of f90SQL and pass it a SQL statement that requests a recordset. Again we are jumping ahead of ourselves by using f90SQL subroutines that have not been explained yet, so do not worry if you do not completely understand everything the program is doing. The important thing here is that you recognize that the program allocates a statement handle. This handle is associated to a SQL statement, and the recordset resulting from the execution of the statement. These associations are indicated when we call the f90SQL subroutine f90SQLPrepare, f90SQLBindCol, f90SQLBindCol, f90SQLExecute and f90SQLFetch.
|
This is the output I get in my system when I run this program:
Name of first 10 authors in BookSales database
1 Adams, Pat
2 Adrian, Merv
3 Ageloff, Roy
4 Andersen, Virginia
5 Antonovich Michael
6 Arnott, Steven E.
7 Arntson, L. Joyce
8 Ault, Michael R
9 Avison, D. E.
10 Bard, Dick
Because this is the first functional program in this manual, I will give a little description of what the program is doing. The program starts by allocating an environment handle (first call to f90SQLAllocHandle), and indicating the driver manager that it wants to use the ODBC 3.x convention (call to f90SQLSetEnvAttr with option SQL_OV_ODBC3). Next the program allocates a connection handle (second call to f90SQLAllocHandle) and sets up the connection to the data source (call to f90SQLConnect). Note that when it sets up the connection to the data source, the program passes the connection handle, indicating to ODBC that this data source must be associated to the passed handle. The other parameters passed in the call to f90SQLConnect are: the name of a data source (as shown in the ODBC administrator), a user ID (in this case Admin) and a password (an empty string because this database does not have access restrictions). The program then checks the status condition returned in parameter iRet to make sure a valid connection was established.
The next step in the program allocates a statement handle (the third call to f90SQLAllocHandle). Note that each time you call f90SQLAllocHandle, you tell the subroutine the type of handle it has to allocate by passing the constants SQL_HANDLE_ENV, SQL_HANDLE_DBC or SQL_HANDLE_STMT. These constants are defined in the module f90SQLConstants you load at the beginning of your f90SQL applications. You can also see that when you call f90SQLAllocHandle to allocate a connection handle, you pass the environment handle to indicate that the new connection handle is in the context of the allocated environment. Usually, you can have several different connections, each one pointing to a different or the same data source. The same principle applies when allocating the statement handle. You can have several statement handles in the context of a single connection. This hierarchy is very useful when dealing with complex queries.
The program then proceeds to execute the query calling f90SQLExecute. The passed SQL statement will return a record set (a table) with ten rows and only one column containing the names of the first 10 authors in the table Authors of the database. This subroutine is called using a statement handle, so the SQL statement is associated to this handle. The recordset resulting from this query is stored in a special area of the ODBC driver. To access the recordset you must first associate application variables to the columns in the record set. This is done through f90SQLBindCol.
The subroutine f90SQLBindCol is likely the most complicated subroutine in this program. It is used to create an association between columns in a recordset and variables in your application. When the program in Example 4.4 calls f90SQLBindCol, it is telling the driver to associate the program variable AuthorsStr to the first column in the recordset that will be created when the SQL query is executed. You should notice that this association is done in the context of the statement that contains the query. Thus, the program passes this statement handle to f90SQLBindCol. A typical call to f90SQLBindCol takes this form:
| call f90SQLBindCol | (StmtHandle, ColumnNumber, TargetType, TargetVar, StrLenIndPtr, iRet) |
where StmtHandle is a statement handle. ColumnNumber is an integer*2 argument indicating the column number of the result set which will be bound to the variable passed in TargetVar. TargetType is the type of variable TargetVar. f90SQL defines constants that identify the different types. For example, constant SQL_F_CHAR tells f90SQLBindCol that variable TargetVar is a character variable. Argument StrLenIndPtr is a pointer to an integer variable. This variable will contain the length of the data stored in variable TargetVar when the rows of the recordset are fetched. In Fortran, to pass a pointer you use the function loc(var). If your are not interested in this value, you can pass a null pointer (a 0 or the constant f90SQL_NULL_PTR). The previous explanation of f90SQLBindCol is just to get you started in understanding the program in Example 4.4. A more comprehensive explanation of this and other f90SQL subroutines is given in a later chapter.
You access the rows in a recordset one at a time using the subroutine f90SQLFetch. Each time the program calls f90SQLFetch, the ODBC driver fetches the next record from the result recordset and puts the information in its columns into the variables you have previously bounded to those columns. Now you see what the real function of the subroutine f90SQLBindCol is. The concept of binding columns to variables will be explained later in more detail, but the general idea is that you use variables as buffers to hold information from columns in a result recordset.
When the ODBC API returns character strings, it usually does so following the internal representation of strings used by C language. In C, strings are terminated with a NULL character, while Fortran pads strings with spaces. This incompatibility is taken care of by calling the subroutine f90SQLStrFormat. In most cases the subroutines in f90SQL will do this for you automatically, but f90SQLFetch is one of the few exceptions.
The last few instructions in Example 4.4 release the space allocated for the environment and the connection by calling subroutines f90SQLFreeHandle and f90SQLDisconnect.