You can combine embedded static SQL with Db2 ODBC to write a mixed application.For
64-bit applications, you cannot use embedded static SQL statements.
With a mixed application, you can take advantage of both the ease
of use that Db2 ODBC functions
provide and the performance enhancement that embedded SQL offers.
Important: To mix Db2 ODBC
with embedded SQL, you must not enable Db2 ODBC
support for multiple contexts. The initialization file for mixed applications
must specify MULTICONTEXT=0 or exclude MULTICONTEXT keyword.
To mix Db2 ODBC and embedded
SQL in an application, you must limit how you combine these interfaces:
Handle all connection management and transaction management with
either Db2 ODBC or embedded
SQL exclusively. You must perform all connections, commits, and rollbacks
with the same interface.
Use only one interface (Db2 ODBC
or embedded SQL) for each query statement. For example, an application
cannot open a cursor in an embedded SQL routine, and then call the Db2 ODBC SQLFetch() function
to retrieve row data.
Because Db2 ODBC permits
multiple connections, you must call SQLSetConnection() before
you call a routine that is written in embedded SQL. SQLSetConnection() allows
you to explicitly specify the connection on which you want the embedded
SQL routine to run. If your application makes only a single connection,
or if you write your application entirely in Db2 ODBC, you do not need to include a SQLSetConnection() call.
Tip: When you write a mixed application, divide this application
into a main program that makes separate function calls. Structure
the mixed application as a Db2 ODBC
application that calls functions that are written with embedded SQL,
or as an embedded SQL application that calls functions that are written
with Db2 ODBC. With this kind
of structure, you can perform transaction management separately in
the main program, while you make query statements in individual functions
written in a single interface. Functions that are written with Db2 ODBC must use null connections.
The following
example shows an application that connects to two data sources and
executes both embedded SQL and dynamic SQL using Db2 ODBC. Figure 1. An application that mixes embedded and dynamic
SQL
/* ... */
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
/* Connect to first data source */
DBconnect(henv, &hdbc[0]);
/* Connect to second data source */
DBconnect(henv, &hdbc[1]);
/********* Start processing step *************************/
/* NOTE: at this point two connections are active */
/* Set current connection to the first database */
if ( (rc = SQLSetConnection(hdbc[0])) != SQL_SUCCESS )
printf("Error setting connection 1\n");
/* Call function that contains embedded SQL */
if ((rc = Create_Tab() ) != 0)
printf("Error Creating Table on 1st connection, RC=
/* Commit transaction on connection 1 */
SQLEndTran(SQL_HANDLE_DBC, hdbc[0], SQL_COMMIT);
/* set current connection to the second database */
if ( (rc = SQLSetConnection(hdbc[1])) != SQL_SUCCESS )
printf("Error setting connection 2\n");
/* call function that contains embedded SQL */
if ((rc = Create_Tab() ) != 0)
printf("Error Creating Table on 2nd connection, RC=
/* Commit transaction on connection 2 */
SQLEndTran(SQL_HANDLE_DBC, hdbc[1], SQL_COMMIT);
/* Pause to allow the existance of the tables to be verified. */
printf("Tables created, hit Return to continue\n");
getchar();
SQLSetConnection(hdbc[0]);
if (( rc = Drop_Tab() ) != 0)
printf("Error dropping Table on 1st connection, RC=
/* Commit transaction on connection 1 */
SQLEndTran(SQL_HANDLE_DBC, hdbc[0], SQL_COMMIT);
SQLSetConnection(hdbc[1]);
if (( rc = Drop_Tab() ) != 0)
printf("Error dropping Table on 2nd connection, RC=
/* Commit transaction on connection 2 */
SQLEndTran(SQL_HANDLE_DBC, hdbc[1], SQL_COMMIT);
printf("Tables dropped\n");
/********* End processing step ***************************/
/* ... */
/************* Embedded SQL functions *******************************
** This would normally be a separate file to avoid having to *
** keep precompiling the embedded file in order to compile the DB2 CLI *
** section50 *
************************************************************************/
EXEC SQL INCLUDE SQLCA;
int
Create_Tab( )
{
EXEC SQL CREATE TABLE mixedup
(ID INTEGER, NAME CHAR(10));
return( SQLCODE);
}
int
Drop_Tab( )
{
EXEC SQL DROP TABLE mixedup;
return( SQLCODE);
}
/* ... */