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 ) ;
}