Use of ODBC to execute dynamic SQL
Open Database Connectivity (ODBC) lets you access data through ODBC function calls in your application. The ODBC interface eliminates the need for precompiling and binding your application and increases the portability of your application.
The ODBC interface is specifically designed for C and C++ applications to access relational databases. Applications that use the ODBC interface might be executed on a variety of data sources without being compiled against each of the databases. ODBC ideally suits the client/server environment in which the target data source might be unknown when the application is built.
You execute SQL statements by passing them to DB2® through an ODBC function call. The function calls allow an application to connect to the data source, issue SQL statements, and receive returned data and status information.
You can prepare an SQL statement by calling the ODBC SQLPrepare() function. You then execute the statement by calling the ODBC SQLExecute() function. In both cases, the application does not contain an embedded PREPARE or EXECUTE statement. You can execute the statement, without preparation, by passing the statement to the ODBC SQLExecDirect() function.
Another advantage of ODBC access is that it can help hide the differences between system catalogs of different database servers. Unlike embedded SQL, DB2 ODBC provides a consistent interface for applications to query and retrieve system catalog information across the DB2 Database family of database management systems. This capability reduces the need to write catalog queries that are specific to each database server. DB2 ODBC can return result tables to those programs.
This example shows a portion of an ODBC program for keeping an inventory of books.
/*********************************************************/
/* Determine which table to update */
/*********************************************************/
rc = SQLBindParameter( hStmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
50,
0,
bktitle,
sizeof(bktitle),
&bktitle_len);
if( rc != SQL_SUCCESS ) goto dberror;
rc = SQLExecDirect( hStmt,
"SELECT TYPE FROM BOOK_TYPES WHERE TITLE=?"
SQL_NTS );
if( rc != SQL_SUCCESS ) goto dberror;
rc = SQLBindCol( hStmt,
1,
SQL_C_CHAR,
book_type,
sizeof(book_type),
&book_type_len);
if( rc != SQL_SUCCESS ) goto dberror;
rc = SQLFetch( hStmt );
if( rc != SQL_SUCCESS ) goto dberror;
rc = SQLCloseCursor( hStmt );
if( rc != SQL_SUCCESS ) goto dberror;
/*********************************************************/
/* Update table */
/*********************************************************/
strcpy( (char *)update_sqlstmt, (char *)"UPDATE ");
if( strcmp( (char *)book_type, (char *)"FICTION") == 0)
{
strcat( (char *)update_sqlstmt, (char *)"FICTION_BOOKS" );
}
else
{
strcpy( (char *)update_sqlstmt, (char *)"NON_FICTION_BOOKS" );
}
strcat( (char *)update_sqlstmt,
(char *)" SET INVENTORY = INVENTORY-1 WHERE TITLE = ?");
rc = SQLPrepare( hStmt, update_sqlstmt, SQL_NTS );
if( rc != SQL_SUCCESS ) goto dberror;
rc = SQLExecute( hStmt );
if( rc != SQL_SUCCESS ) goto dberror;
rc = SQLEndTran( SQL_HANDLE_DBC, hDbc, SQL_COMMIT );
if( rc != SQL_SUCCESS ) goto dberror;