Call Level Interface Guide and Reference

Mixing Embedded SQL and DB2 CLI

It is possible, and sometimes desirable, for an application to use DB2 CLI in conjunction with embedded static SQL. Consider the scenario where the application developer wishes to take advantage of the ease of use provided by the DB2 CLI catalog functions and maximize the portion of the application's processing where performance is critical. In order to mix the use of DB2 CLI and embedded SQL, the application must comply to the following rules:

• All connection management and transaction management must be performed completely using either DB2 CLI or embedded SQL. Either the DB2 CLI application performs all the Connects and Commits/Rollback and calls functions written using embedded SQL; or an embedded SQL application performs all the Connects and Commits/Rollback and calls functions written in DB2 CLI which use a null connection (see Writing a Stored Procedure in CLI for details on null connections).

• Query statement processing must not and cannot straddle across DB2 CLI and embedded SQL interfaces for the same statement; for example, the application cannot open a cursor in an embedded SQL routine, and then call the DB2 CLI SQLFetch() function to retrieve row data.

Since DB2 CLI permits multiple connections, the SQLSetConnection() function must be called prior to making a function call to a routine written in embedded SQL. This allows the application to explicitly specify the connection under which the embedded SQL routine should perform its processing.

If the DB2 CLI application is multithreaded and also makes embedded SQL calls or DB2 API calls, then each thread must have a DB2 context. See Writing Multi-Threaded Applications for complete details.

Mixed Embedded SQL and DB2 CLI Example

The following example demonstrates an application that connects to two data sources, and executes both embedded SQL and dynamic SQL using DB2 CLI.

/* From CLI sample mixed.sqc */
/* ... */
    /* allocate an environment handle   */
    SQLAllocEnv(&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 there are two active connections */
     
    /* 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=%d\n", rc);
 
   /*  Commit transation on connection 1 */
    SQLTransact(henv, 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=%d\n", rc);
 
   /*  Commit transation on connection 2 */
    SQLTransact(henv, 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=%d\n", rc);
 
   /*  Commit transation on connection 1 */
    SQLTransact(henv, hdbc[0], SQL_COMMIT);
 
    SQLSetConnection(hdbc[1]);
    if (( rc = Drop_Tab() ) != 0)
        printf("Error dropping Table on 2nd connection, RC=%d\n", rc);
 
   /*  Commit transation on connection 2 */
    SQLTransact(henv, hdbc[1], SQL_COMMIT);
 
    printf("Tables dropped\n");
 
    /*********   End Processing Step  ***************************/
/* ... */
/*************   Embedded SQL Functions  *******************************
** This would normally be a seperate file to avoid having to           *
** keep precompiling the embedded file in order to compile the DB2 CLI *
** section.                                                            *
************************************************************************/
 
#include "sql.h"
#include "sqlenv.h"
 
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);
}
 

[ DB2 List of Books | Search the DB2 Books ]