Basic Program Flow for a CLI Application
Coding CLI applications involves writing C/C++ modules that contain DB2 UDB CLI functions. All of the available function calls are described in detail in the DB2 UDB Call Level Interface Guide and Reference. Coding ODBC applications is very similar to CLI because the function calls are essentially the same. The primary difference is in the way the application is linked. CLI applications are linked using the DB2 UDB supplied libraries and ODBC applications are linked with ODBC libraries. Some functions are supported by CLI and not by ODBC and vice-versa, but we will not be covering any of those functions here.
All CLI applications contain three main tasks: initialization, transaction processing, and termination. General tasks also exist throughout an application such as message processing. Initialization involves allocating and initializing environment and connection handles as well as connecting to one or more databases. Transaction processing consists of the main tasks of the application; SQL statements are passed to DB2 to query and modify data against a database. Finally, the termination phase involves disconnecting the application from the database and freeing allocated resources. Figure 11.2 shows the basic function call sequences for the initialization and termination tasks. The transaction processing task is illustrated later in Figure 11.3.
Figure 11.2. CLI initialization and termination.
Figure 11.3. Overview of transaction processing in CLI applications.
Initializing Handle Types and Connecting
The initialization task consists of allocating and initializing environment and connection handles (which are later freed in the termination task). An application then passes the appropriate handle when it calls other DB2 CLI functions. A handle is a variable that refers to a data object controlled by DB2 CLI. Using handles relieves the application from having to allocate and manage global variables or data structures, such as the SQLDA or SQLCA, used in IBM's embedded SQL interfaces.
The SQLAllocHandle() function is called with a handle type and parent handle arguments to create environment, connection, statement, or descriptor handles. The function SQLFreeHandle() is used to free the resources allocated to a handle.
There are four types of handles:
Environment Handle— The data object that contains information regarding the global state of the application, such as attributes and connections. An environment handle must be allocated before a connection handle can be allocated.
Connection Handle— A data object that contains information associated with a connection to a particular data source (database). This includes connection attributes, general status information, transaction status, and diagnostic information.
An application can be connected to several servers at the same time, and can establish several distinct connections to the same server. An application requires a connection handle for each concurrent connection to a database server. For information on multiple connections, refer to the next section “Connecting to One or More Data Sources.”
Call SQLGetInfo() to determine whether a user-imposed limit on the number of connector handles has been set.
Statement Handle— Statement handles are discussed in the upcoming section, Transaction Processing.
Descriptor Handle— A data object that contains information about columns in a result set and dynamic parameters in an SQL statement.
Connecting to One or More Data Sources
To connect concurrently to one or more data sources (or multiple concurrent connections to the same data source), an application calls SQLAllocHandle(), with a HandleType of SQL_HANDLE_DBC, once for each connection. The subsequent connection handle is used with SQLConnect() to request a database connection and with SQLAllocHandle(), with a HandleType of SQL_HANDLE_STMT, to allocate statement handles for use within that connection. An extended connect function, SQLDriverConnect(), allows for additional connect options and the ability to directly open a connection dialog box in environments that support a GUI. The function SQLBrowseConnect() can be used to discover all of the attributes and attribute values required to connect to a data source.
The use of connection handles ensures that multithreaded applications that use one connection per thread are thread-safe since separate data structures are allocated and maintained by DB2 CLI for each connection.
Disconnecting and Termination
The termination phase involves disconnecting your application from the database(s) and freeing allocated resources after the transaction processing has completed. The SQLDisconnect() API closes a connection. The corresponding connection handle can then be freed using the SQLFreeHandle() with the argument SQL_HANDLE_DBC. Only after all the connection handles have been freed, the SQLFreeHandle() function can be called with argument SQL_HANDLE_ENV to successfully free the environment handle.
Transaction Processing
The main task of the application is accomplished during the transaction processing phase. Figure 11.3 gives an overview of transaction processing within a CLI application. SQL statements are passed to DB2 CLI to query and modify the data using a five-step process:
1. | Allocate statement handle(s). |
2. | Prepare and execute SQL statements. |
3. | Process results. |
4. | End the transaction. |
5. | Free statement handle(s). |
Allocation Statement Handle
Statement handles need to be allocated before any SQL statements can be executed. A statement handle refers to the data object that is used to track the execution of a single statement. This includes information such as statement attributes, SQL statement text, cursor information, result values, and status information. The API SQLAllocHandle() is called with a HandleType of SQL_HANDLE_STMT to allocate a statement handle.
Preparing and Executing SQL Statements
After a statement handle has been allocated, SQL statements can be specified and executed using one of two methods:
Execute directly, which combines the prepare and execute steps into one. You would use this method if the statement will be executed only once or if the column information is not needed prior to statement execution. The function SQLExecDirect() is used for executing statements directly.
Prepare then execute, which splits the preparation of the statement from the execution. This method is useful if the statement will be executed repeatedly, usually with different parameter values. This avoids having to prepare the same statement more than once. The subsequent executions make use of the access plans already generated by the prepare. The prepare followed by execute is accomplished by the function calls SQLPrepare() and SQLExecute().
As in dynamic embedded SQL, it might be necessary to include parameter markers, denoted by the question mark (?) within the SQL statement. An application variable is associated or bound with a parameter marker using the SQLBindParameter() function. For example, if a statement, such as the following, is to be executed repeatedly with different values, it can be prepared once using the parameter marker, and the new value can be bound to the parameter marker before each execution.
SELECT column1 from table WHERE column2 = ?
DB2 provides dynamic statement caching at the server. Applications that repeatedly execute the same SQL statement across multiple transactions can realize better performance by preparing these statements once at the beginning of the application and then executing the statements as many times as is needed throughout the application.
Processing Results
The next step after the statement has been executed depends on the type of the SQL statement. If the statement does not query or modify data, no action may be required other than the normal check for the diagnostic messages. Such could be the case for SQL that creates or drops a table. For statements that update data, for example UPDATE, DELETE, or INSERT statements, the function SQLRowCount() can be used to obtain the number of rows affected by the SQL statement.
int main( ) {
SQLHANDLE henv;
SQLHANDLE hdbc;
SQLHANDLE hstmt;
SQLCHAR * sqlstmt = (SQLCHAR *)"SELECT col1
FROM table1 WHERE col2 = ?";
SQLCHAR value1[30];
SQLINTEGER ind;
/**************** Initialization ***************/
/* allocate henv, hdbc, connect to database */
/************* End of Initialization ***********/
...
/********* Start Transaction Processing ********/
/* allocate statement handle */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
/* prepare the statement */
SQLPrepare( hstmt, sqlstmt, SQL_NTS);
/* bind a value to parameter marker in sqlstmt */
SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 9, 0,
"data", 4, NULL);
/* execute the statement */
SQLExecute( hstmt);
/* For the sake of simplicity we will leave out */
/* steps to find out the structure of the */
/* result set. */
/* Bind the first (col1) column of the result */
SQLBindCol( hstmt, 1, SQL_C_CHAR, value1, 30, &ind);
/* fetch and print each row */
while( SQLFetch(hstmt) == SQL_SUCCESS)
printf( "%s
", firstname);
/* explicitly commit or rollback the transaction */
SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
/* free the statement handle */
SQLFreeHandle( SQL_HANDLE_STMT, hstmt);
/********** End Transaction Processing ***********/
/***************** Termination *******************/
/* disconnect, free connection and environment */
/* handles */
return( SQL_SUCCESS);
}
If the statement is a query, you usually need to perform the following steps to retrieve each row of the result set:
1. | Establish or describe the structure of the result set. The number of the columns on the result set is found using SQLNumResultCols(). Information about the columns in the result set, such as name, column type, or length, is obtained using SQLDescribeCol() or SQLColAttributes(). |
2. | To receive the data into the application, bind the application variables to columns in the result set using SQLBindCol(). |
3. | Call SQLFetch() to fetch the first or next row of the result set. If any columns have been bound in the second step, data is retrieved into the bound application variables. |
4. | If the application does not bind any columns in the second step, as in the case when it needs to retrieve columns of long data pieces, it can use SQLGetData() after the fetch. |
Both the SQLBindCol() and SQLGetData() techniques can be combined if some columns are bound and some are unbound.
Ending the Transaction
The transaction ends when it is either implicitly or explicitly committed or rolled back. DB2 CLI applications can switch between two commit modes: auto-commit and manual-commit. In auto-commit mode, every SQL statement is a complete transaction, and a commit is issued implicitly at the end of each statement execution, which for query statements is the point when the cursor is freed. Typically, a query-only application might want to use auto-commit mode, which is the default.
In manual-commit mode, a transaction can span execution of multiple SQL statements. In this mode, an explicit commit or rollback using the SQLEndTran() function needs to be issued to end the transaction. Calling SQLEndTran() affects all statements of a specified connection because each connection is considered as having only one outstanding transaction.
Diagnostics and Error Processing
Every CLI function returns a function return code. This provides basic diagnostic information to the application. The application should examine the return code before proceeding to the next function call. Table 11.2 lists the possible CLI return codes. Not all return codes are applicable to each CLI function. If the application encounters an unexpected return code from a function call, typically anything other than SQL_SUCCESS, it can call SQLGetDiagRec() or SQLGetDiagField() to retrieve detailed diagnostic information. The details include SQLSTATE, the native error or SQLCODE, and the message text.
Embedded SQL applications rely on the SQLCA for all diagnostic information. DB2 CLI applications can retrieve much of the same information by using SQLGetDiagRec(), hence it is not necessary to examine the SQLCA in most cases. However, if you need to examine the SQLCA from within a CLI application, the SQLGetSQLC() function can be used. SQLGetSQLCA() should never be used as a substitute for SQLGetDiagRec() or SQLGetDiagField().
The following example contains sample code to illustrate error handling and retrieval of diagnostic information by a CLI application:
/* Call a CLI function , for example: */
rc = SQLConnect( hdbc, "DB2MALL", SQL_NTS, "baduid",
SQL_NTS, "badpwd", SQL_NTS );
/* If not successful, call an error checking routine */
if ( rc != SQL_SUCCESS )
errprint( SQL_HANDLE_DBC, hdbc, rc);
...
/* Example of a simple error routine to print the error: */
SQLRETURN errprint( SQLSMALLINT htype, /* A handle type */
SQLHANDLE hndl, /* A handle */
SQLRETURN erc ) /* Return code */
{
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER sqlcode;
SQLSMALLINT length, i=1;
printf( ">--- ERROR -- RC = %d ------------
", erc);
while ( SQLGetDiagrec( htype, hndl, i, sqlstate
&sqlcode, buffer,
SQL_MAX_MESSAGE_LENGTH + 1,
&length) == SQL_SUCCESS )
{
printf( " SQLSTATE: %s
", sqlstate );
printf( "Native error code: %ld
", sqlcode );
printf( "%s
", buffer );
i++;
}
return( SQL_ERROR );
}
This example code checks to see whether the CLI call to connect to the database returned successfully. If the wrong userid/password is specified, an error condition occurs, and the errprint() function is called to print out the diagnostic information. Here is the sample output of this routine:
>--- ERROR -- RC = -1 ------------
SQLSTATE: 08004
Native error code: -1403
[IBM][CLI Driver] SQL1403N The username and/or password supplied is incorrect.
SQLSTATE=08004
You can include additional logic for your error handling. For instance, because the call for connecting to the database failed in the example, we could have exited the program or prompted the user to supply the userid and password again.