Call Level Interface Guide and Reference

Embedded SQL Example

This example is a modified version of the example contained in the X/Open SQL CLI document. It shows embedded statements in comments, and the equivalent DB2 CLI function calls.

/* From CLI sample embedded.c */
/* ... */
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "samputil.h"          /* Header file for CLI sample code */
 
/* ... */
 
/*
 Global Variables for user id and password.
 To keep samples simple, not a recommended practice.
*/
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
 
int main( int argc, char * argv[] ) {
 
    SQLHANDLE henv, hdbc, hstmt ;
    SQLRETURN rc ;
 
    SQLINTEGER id ;
    SQLCHAR name[51] ;
 
    SQLCHAR * create = "CREATE TABLE NAMEID (ID integer, NAME varchar(50))" ;
    SQLCHAR * insert = "INSERT INTO NAMEID VALUES (?, ?)" ;
    SQLCHAR * select = "select ID, NAME from NAMEID" ;
    SQLCHAR * drop   = "DROP TABLE NAMEID" ;
 
/* ... */
 
    /* EXEC SQL CONNECT TO :server USER :uid USING :authentication_string; */
    /* macro to initalize server, uid and pwd */
    INIT_UID_PWD ;
    /* allocate an environment handle */
    rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
    /* allocate a connect handle, and connect */
    rc = DBconnect( henv, &hdbc ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
    /* allocate a statement handle */
    rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
 
    /* EXEC SQL CREATE TABLE NAMEID (ID integer, NAME varchar(50)); */
    /* execute the sql statement */
    rc = SQLExecDirect( hstmt, create, SQL_NTS ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* EXEC SQL COMMIT WORK; */
    /* commit create table */
    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
 
    /* EXEC SQL INSERT INTO NAMEID VALUES ( :id, :name ); */
    /* show the use of SQLPrepare/SQLExecute method */
    /* prepare the insert */
    rc = SQLPrepare( hstmt, insert, SQL_NTS ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    /* Set up the first input parameter "id" */
    rc = SQLBindParameter( hstmt,
                           1,
                           SQL_PARAM_INPUT,
                           SQL_C_LONG,
                           SQL_INTEGER,
                           0,
                           0,
                           (SQLPOINTER) & id,
                           0,
                           NULL
                         ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    /* Set up the second input parameter "name" */
    rc = SQLBindParameter( hstmt,
                           2,
                           SQL_PARAM_INPUT,
                           SQL_C_CHAR,
                           SQL_VARCHAR,
                           51,
                           0,
                           name,
                           51,
                           NULL
                         ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    /* now assign parameter values and execute the insert */
    id = 500 ;
    strcpy( name, "Babbage" ) ;
    rc = SQLExecute( hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* EXEC SQL COMMIT WORK; */
    /* commit inserts */
    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
    /*  Reset input parameter.  */
    rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* EXEC SQL DECLARE c1 CURSOR FOR SELECT ID, NAME FROM NAMEID; */
    /* EXEC SQL OPEN c1; */
    /* The application doesn't specify "declare c1 cursor for" */
    rc = SQLExecDirect( hstmt, select, SQL_NTS ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* EXEC SQL FETCH c1 INTO :id, :name; */
    /* Binding first column to output variable "id" */
    SQLBindCol( hstmt, 1, SQL_C_LONG, ( SQLPOINTER ) & id, 0, NULL ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    /* Binding second column to output variable "name" */
    SQLBindCol( hstmt, 2, SQL_C_CHAR, name, 51, NULL ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    /* now execute the fetch        */
    while ( ( rc = SQLFetch( hstmt ) ) == SQL_SUCCESS )
       printf( "Result of Select: id = %ld name = %s\n", id, name ) ;
    if (rc != SQL_NO_DATA_FOUND)
       CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* finally, we should commit, discard hstmt, disconnect */
 
    /* EXEC SQL COMMIT WORK; */
    /*  Close cursor and free bound columns.  */
    /*  Free statement resources  */
    rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    /*  Drop table.  */
    rc = SQLExecDirect( hstmt, drop, SQL_NTS ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
    /* commit the transaction */
    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
 
    /* EXEC SQL CLOSE c1; */
    /* free the statement handle */
    rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* EXEC SQL DISCONNECT; */
    /* disconnect from the database */
    printf( "\n>Disconnecting .....\n" ) ;
    rc = SQLDisconnect( hdbc ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
    /* free the connection handle */
    rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
    /* free the environment handle */
    rc = SQLFreeHandle( SQL_HANDLE_ENV,  henv ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
 
    return( SQL_SUCCESS ) ;
 
}