/*******************************************************************************
**
** Source File Name = dbuse.c 1.6
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** Shows how to use a database.
**
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "utilcli.h" /* Header file for CLI sample code */
int StmtExecDirect( SQLHANDLE) ;
int ConnExecTransact( SQLHANDLE) ;
int StmtBindParam( SQLHANDLE) ;
int StmtExecute( SQLHANDLE) ;
int StmtExecCompound( SQLHANDLE) ;
int StmtExecAsync( SQLHANDLE) ;
int DescSetGetRec( SQLHANDLE) ;
int DescSetGetField( SQLHANDLE );
int DescCopy( SQLHANDLE) ;
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] )
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handles */
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
if ( rc != 0 ) return( rc ) ;
printf("\n\nDATABASES: HOW TO USE A DATABASE.\n");
/* initialize the CLI application */
rc = CLIAppInit( dbAlias, user, pswd, &henv, &hdbc,
(SQLPOINTER)SQL_AUTOCOMMIT_ON);
if ( rc != 0 ) return( rc ) ;
/* using SQLExecDirect */
rc = StmtExecDirect( hdbc) ;
/* using SQLEndTrans for */
rc = ConnExecTransact( hdbc) ;
/* using SQLBindParameter */
rc = StmtBindParam( hdbc) ;
/* using SQLExecute */
rc = StmtExecute( hdbc) ;
/* using a compound SQL statement */
rc = StmtExecCompound( hdbc) ;
/* executing an SQL statement asynchronously */
rc = StmtExecAsync( hdbc) ;
/* using descriptors */
rc = DescSetGetRec( hdbc) ;
rc = DescSetGetField( hdbc) ;
rc = DescCopy( hdbc) ;
/* terminate the CLI application */
rc = CLIAppTerm( &henv, &hdbc, dbAlias);
return( rc ) ;
} /* end main */
/******************************************************************************
** StmtExecDirect - how to execute an SQL statement directly
******************************************************************************/
int StmtExecDirect( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt1 = ( SQLCHAR * ) "CREATE TABLE table1(col1 INTEGER)" ;
SQLCHAR * stmt2 = ( SQLCHAR * ) "DROP TABLE table1" ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLFreeHandle\n");
printf("TO EXECUTE SQL STATEMENTS DIRECTLY:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly statement 1*/
printf("\n Execute directly %s.\n", stmt1);
sqlrc = SQLExecDirect( hstmt, stmt1, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute directly statement 2 */
printf(" Execute directly %s.\n", stmt2);
sqlrc = SQLExecDirect( hstmt, stmt2, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** ConnExecTransact - how to perform a transaction
** on one connection
******************************************************************************/
int ConnExecTransact( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt1 = ( SQLCHAR * )"CREATE TABLE table1(col1 INTEGER)" ;
SQLCHAR * stmt2 = ( SQLCHAR * )"CREATE TABLE table2(col1 INTEGER)" ;
SQLCHAR * stmt3 = ( SQLCHAR * )"DROP TABLE table1" ;
SQLCHAR * stmt4 = ( SQLCHAR * )"DROP TABLE table2" ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLEndTrans\n-SQLFreeHandle\n");
printf("TO PERFORM A TRANSACTION ON ONE CONNECTION:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf(" Perform a transaction on this connection\n");
/* execute directly statement 1 */
printf(" executing %s...\n", stmt1);
sqlrc = SQLExecDirect( hstmt, stmt1, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute directly statement 2 */
printf(" executing %s...\n", stmt2);
sqlrc = SQLExecDirect( hstmt, stmt2, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Commiting the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction commited.\n");
printf(" Perform another transaction on this connection\n");
/* execute directly statement 3 */
printf(" executing %s...\n", stmt3);
sqlrc = SQLExecDirect( hstmt, stmt3, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute directly statement 4 */
printf(" executing %s...\n", stmt4);
sqlrc = SQLExecDirect( hstmt, stmt4, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Commiting the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction commited.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** StmtBindParam - how to bind parameteres to an SQL statement
******************************************************************************/
int StmtBindParam( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"DELETE FROM org WHERE deptnumb = ? AND division = ? " ;
SQLSMALLINT parameter1 = 0;
char parameter2[20];
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n-SQLBindParameter\n");
printf("-SQLExecDirect\n-SQLEndTrans\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO BIND PARAMETERS TO AN SQL STATEMENT:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* bind parameter1 and parameter2 to the statement */
printf(" Bind parameter1 and parameter2 to the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT,
SQL_SMALLINT, 0, 0, ¶meter1, 0, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 20, 0, parameter2, 20, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute the statement for parameter1 = 15 and parameter2 = 'Eastern' */
printf(" Execute the statement for\n");
printf(" parameter1 = 15 and\n");
printf(" parameter2 = 'Eastern'\n");
parameter1 = 15;
strcpy(parameter2, "Eastern");
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute the statement for parameter1 = 84 and parameter2 = 'Western' */
printf(" Execute the statement for\n");
printf(" parameter1 = 84 and\n");
printf(" parameter2 = 'Western'\n");
parameter1 = 84;
strcpy(parameter2, "Western");
sqlrc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** StmtExecute - how to execute a prepared SQL statement
******************************************************************************/
int StmtExecute( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"DELETE FROM org WHERE deptnumb = ? " ;
SQLSMALLINT parameter1 = 0;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLPrepare\n-SQLBindParameter\n");
printf("-SQLExecute\n-SQLEndTran\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO EXECUTE A PREPARED SQL STATEMENT:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare the statement */
printf(" Prepare the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind parameter1 to the statement */
printf(" Bind parameter1 to the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT,
SQL_SMALLINT, 0, 0, ¶meter1, 0, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute the statement for parameter1 = 15 */
printf(" Execute the prepared statement for\n");
printf(" parameter1 = 15\n");
parameter1 = 15;
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute the statement for parameter1 = 84 */
printf(" Execute the prepared statement for\n");
printf(" parameter1 = 84\n");
parameter1 = 84;
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** StmtExecCompound - how to execute a compound SQL statement
******************************************************************************/
int StmtExecCompound( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt, compnd_hstmt[4] ; /* statement hadles */
SQLCHAR * compnd_stmt[] = {
( SQLCHAR * ) "INSERT INTO awards (id, award) "
"SELECT id, 'Sales Merit' from staff "
"WHERE job = 'Sales' AND (comm/100 > years)",
( SQLCHAR * ) "INSERT INTO awards (id, award) "
"SELECT id, 'Clerk Merit' from staff "
"WHERE job = 'Clerk' AND (comm/50 > years)",
( SQLCHAR * ) "INSERT INTO awards (id, award) "
"SELECT id, 'Best ' concat job FROM STAFF "
"WHERE comm = (SELECT max(comm) FROM staff WHERE job = 'Clerk')",
( SQLCHAR * ) "INSERT INTO awards (id, award) "
"SELECT id, 'Best ' concat job FROM STAFF "
"WHERE comm = (SELECT max(comm) FROM STAFF WHERE job = 'Sales')",
} ;
SQLINTEGER i ;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLPrepare\n-SQLExecute\n");
printf("-SQLEndTrans\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO EXECUTE A COMPOUND SQL STATEMENT:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* crete the table AWARDS */
sqlrc = SQLExecDirect(
hstmt,
( SQLCHAR * ) "CREATE TABLE AWARDS (ID INTEGER, AWARD CHAR(12))" ,
SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* prepare 4 substatements */
for ( i = 0; i < 4; i++ )
{ sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &compnd_hstmt[i] ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
sqlrc = SQLPrepare( compnd_hstmt[i], compnd_stmt[i], SQL_NTS ) ;
STMT_HANDLE_CHECK( compnd_hstmt[i], sqlrc) ;
}
/* begin the COMPOUND statement */
printf(" Execute directly:\n");
printf(" BEGIN COMPOUND NOT ATOMIC STATIC\n");
sqlrc = SQLExecDirect( hstmt,
( SQLCHAR * ) "BEGIN COMPOUND NOT ATOMIC STATIC",
SQL_NTS );
STMT_HANDLE_CHECK( hstmt, sqlrc ) ;
/* execute 4 sub-statements */
for ( i = 0; i < 4; i++ )
{ printf(" Execute the sub-statement nb. %d\n", i+1);
printf(" of the COMPOUND statement\n" );
sqlrc = SQLExecute( compnd_hstmt[i] ) ;
STMT_HANDLE_CHECK( compnd_hstmt[i], sqlrc) ;
}
/* end the COMPOUND statement */
printf(" Execute directly:\n END COMPOUND COMMIT\n" );
sqlrc = SQLExecDirect( hstmt,
( SQLCHAR * ) "END COMPOUND COMMIT",
SQL_NTS
) ;
STMT_HANDLE_CHECK( hstmt, sqlrc ) ;
/* free the statement handles */
for ( i = 0; i < 4; i++ )
{ sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, compnd_hstmt[i]) ;
STMT_HANDLE_CHECK( compnd_hstmt[i], sqlrc) ;
}
/* drop the table AWARDS */
sqlrc = SQLExecDirect( hstmt,
( SQLCHAR * ) "DROP TABLE AWARDS" ,
SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** StmtExecAsync - how to execute an SQL statement asynchronously
******************************************************************************/
int StmtExecAsync( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt = ( SQLCHAR * )
"CREATE TABLE table1 ( col1 INTEGER) " ;
SQLINTEGER i;
SQLSMALLINT outlen; /* For calls to SQLGetInfo() */
SQLUINTEGER uAsyncMode ; /* For calls to SQLGetInfo() */
/* Make the result from SQLGetInfo() more meaningful by mapping */
/* the returned value to the string. */
static char STR_ASYNCMODE[][19] = { "SQL_AM_NONE",
"SQL_AM_CONNECTION",
"SQL_AM_STATEMENT" };
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLGetInfo\n-SQLSetStmtAttr\n");
printf("-SQLExecDirect\n-SQLEndTrans\n-SQLFreeHandle\n");
printf("TO SHOW HOW TO EXECUTE AN SQL STATEMENT ASYNCHRONOUSLY:\n");
/* set AUTOCOMMIT OFF */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf("\n Transactions enabled\n");
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* See what type of Asynchronous support is available,
* and whether or not the CLI/ODBC configuration keyword ASYNCENABLE
* is set on or off.
*/
sqlrc = SQLGetInfo( hdbc, /* Connection handle */
SQL_ASYNC_MODE, /* Query the support available */
&uAsyncMode, /* Store the result in this variable */
4,
&outlen);
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" SQL_ASYNC_MODE value from SQLGetInfo() is\n");
printf(" %s\n", STR_ASYNCMODE[uAsyncMode]);
if ( uAsyncMode == SQL_AM_NONE )
{ /* Async not supported */
printf(" Asynchronous execution is not supported \n");
printf(" by this datasource or has been turned off\n");
printf(" by the CLI/ODBC configuration keyword ASYNCENABLE.\n");
printf(" The application will continue,\n");
printf(" but SQLExecDirect()will not be run asynchronously.\n");
}
else
{ printf(" Set asynchronous execution ON at statement level\n");
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ASYNC_ENABLE,
(SQLPOINTER) SQL_ASYNC_ENABLE_ON,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* The while loop is new for the asynchronous sample, the */
/* SQLExecDirect() call remains the same. */
printf(" Call SQLExecDirect\n");
sqlrc = SQLExecDirect(hstmt, stmt, SQL_NTS) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
i = 0;
while ( sqlrc == SQL_STILL_EXECUTING)
{ i = i+1 ;
if ( i == 400 )
{ printf(" ..SQLExecDirect() still executing asynchronously..\n");
/* reset i */
i = 0;
}
/* Other processing can be performed here, between each call
* to see if SQLExecDirect() has finished running asynchronously.
* This section will never run if CLI runs the function
* synchronously.
*/
sqlrc = SQLExecDirect(hstmt, stmt, SQL_NTS) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
printf(" Rolling back the transaction...\n");
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
DBC_HANDLE_CHECK( hdbc, sqlrc);
printf( " Transaction rolled back.\n");
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** DescSetGetRec - Show how to use SQLGetDescRec and SQLSetDescRec
******************************************************************************/
int DescSetGetRec( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
SQLRETURN rc = 0;
SQLHANDLE hstmt, hstmt1; /* Statement handle */
SQLHANDLE hIRD,hARD; /* Descriptor handle */
SQLINTEGER indicator;
SQLSMALLINT i;
SQLCHAR colname[20];
SQLSMALLINT namelen;
SQLSMALLINT type;
SQLSMALLINT subtype;
SQLINTEGER width,length,datalen,nameleng;
SQLSMALLINT precision, scale, nullable;
SQLSMALLINT num_cols;
SQLSMALLINT id_no;
SQLCHAR thename[20];
struct sqlca sqlca;
char sp5[]=" ",sp8[]=" ";
SQLCHAR * stmt = (SQLCHAR *) "SELECT id,name FROM staff where dept = 10 ";
SQLCHAR * stmt1 = (SQLCHAR *) "SELECT id,name FROM staff where dept = 10 ";
printf("\nUSE THE CLI FUNCTIONS \n");
printf("-SQLGetDescRec\n-SQLSetDescRec");
printf("\nOther CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLGetStmtAttr\n");
printf("-SQLPrepare\n-SQLBindParameter\n");
printf("-SQLExecute\n-SQLBindCol\n");
printf("-SQLFetch\n-SQLFreeHandle\n");
printf("TO USE DESCRIPTORS:\n");
/* set AUTOCOMMIT on */
rc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, rc);
/* allocate a statement handle */
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, rc);
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt1 ) ;
DBC_HANDLE_CHECK( hdbc, rc);
printf("%sPrepare the statement\n",sp5);
printf("%s%s\n",sp8, stmt);
rc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, rc);
/* get implicit row descriptor handle */
rc = SQLGetStmtAttr ( hstmt,
SQL_ATTR_IMP_ROW_DESC,
&hIRD,
SQL_IS_INTEGER,
&indicator);
STMT_HANDLE_CHECK( hstmt, rc);
/* try to get each column information in the resultset */
rc = SQLNumResultCols( hstmt, &num_cols ) ;
STMT_HANDLE_CHECK( hstmt, rc);
printf("\n%sRecord field/Column information within descriptor: \n",sp5 );
for(i=1; i<= num_cols; i++){
rc = SQLGetDescRec(hIRD, i, colname, sizeof(colname),
&namelen, &type, &subtype, &width, &precision,
&scale, &nullable);
if (rc == SQL_SUCCESS) {
printf("%sColumn = %d: \n",sp5,i );
printf("%sName = %s \n", sp8,colname );
printf("%sData type = %d \n", sp8,type );
printf("%sSub type = %d \n", sp8,subtype );
printf("%sWidth = %d \n", sp8,width );
printf("%sPrecision = %d \n", sp8,precision );
printf("%sScale = %d \n", sp8,scale );
printf("%sNullable = %d \n", sp8,nullable );
}
STMT_HANDLE_CHECK( hstmt, rc);
}
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, rc);
strcpy((char*)colname,"Yes");
type=0;subtype=0;width=0;precision=0;
scale=0;nullable=0;
rc = SQLPrepare( hstmt1, stmt1, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt1, rc);
/* get implicit descriptor handle */
rc = SQLGetStmtAttr ( hstmt1,
SQL_ATTR_APP_ROW_DESC,
&hARD,
0,
NULL);
STMT_HANDLE_CHECK( hstmt1, rc);
/* Set record/column values via descriptor */
type = SQL_SMALLINT;
length = 2;
rc = SQLSetDescRec(hARD, 1, type, 0,
length, 0, 0, &id_no, &datalen, NULL);
STMT_HANDLE_CHECK( hstmt1, rc);
type = SQL_CHAR;
length = 20;
rc = SQLSetDescRec(hARD, 2, type, 0,
length, 0, 0, thename, &nameleng, NULL);
STMT_HANDLE_CHECK( hstmt1, rc);
printf("\n%sAfter setting record: \n",sp5 );
rc = SQLExecute( hstmt1 ) ;
STMT_HANDLE_CHECK( hstmt1, rc);
/* Get the record/column value after setting */
for(i=1; i<= num_cols; i++){
rc = SQLGetDescRec(hARD, i, colname, sizeof(colname),
&namelen, &type, &subtype, &width, &precision,
&scale, &nullable);
STMT_HANDLE_CHECK( hstmt1, rc);
if (rc == SQL_SUCCESS) {
printf("%sColumn = %d: \n",sp5,i );
printf("%sName = %s \n", sp8,colname );
printf("%sData type = %d \n", sp8,type );
printf("%sSub type = %d \n", sp8,subtype );
printf("%sWidth = %d \n", sp8,width );
printf("%sPrecision = %d \n", sp8,precision );
printf("%sScale = %d \n", sp8,scale );
printf("%sNullable = %d \n", sp8,nullable );
}
}
/* get the result set and print it without using bindcol */
rc = SQLFetch( hstmt1 );
STMT_HANDLE_CHECK( hstmt1, rc);
if (rc == SQL_SUCCESS){
printf("%sResultset after using SetDescRec \n",sp5);
printf("%s-ID-,---NAME---- \n",sp8);
}
while ( rc == SQL_SUCCESS){
printf("%s%d %s \n",sp8,id_no,thename);
rc = SQLFetch( hstmt1 );
}
STMT_HANDLE_CHECK( hstmt1, rc);
/* free the statement handle */
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt1 ) ;
STMT_HANDLE_CHECK( hstmt1, rc);
return(rc);
}
/******************************************************************************
** DescCopy - Show how to use SQLCopyDesc
******************************************************************************/
int DescCopy( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
SQLRETURN rc = 0;
SQLHANDLE hstmt, hstmt1; /* Statement handle */
SQLHANDLE hIRD,hARD; /* Descriptor handle */
SQLINTEGER indicator;
SQLSMALLINT i;
SQLCHAR colname[20];
SQLSMALLINT namelen;
SQLSMALLINT type;
SQLSMALLINT subtype;
SQLINTEGER width,length,datalen,nameleng;
SQLSMALLINT precision, scale, nullable;
SQLSMALLINT num_cols;
SQLSMALLINT id_no;
SQLCHAR thename[20];
struct sqlca sqlca;
char sp5[]=" ",sp8[]=" ";
SQLCHAR * stmt = (SQLCHAR *)"SELECT id,name FROM staff where dept = 20";
printf("\nUSE THE CLI FUNCTIONS \n");
printf("-SQLCopyDesc");
printf("\nOther CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLGetStmtAttr\n");
printf("-SQLPrepare\n-SQLExecute\n");
printf("-SQLGetDescRec\n-SQLSetDescRec\n");
printf("-SQLFetch\n-SQLFreeHandle\n");
printf("TO USE DESCRIPTORS:\n");
/* set AUTOCOMMIT on */
rc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, rc);
/* allocate a statement handle */
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, rc);
printf("%sPrepare the statement\n",sp5);
printf("%s%s\n",sp8, stmt);
rc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, rc);
/* get implicit row descriptor handle */
rc = SQLGetStmtAttr ( hstmt,
SQL_ATTR_IMP_ROW_DESC,
&hIRD,
SQL_IS_INTEGER,
&indicator);
STMT_HANDLE_CHECK( hstmt, rc);
/* to get each column information in the resultset */
rc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLNumResultCols( hstmt, &num_cols ) ;
STMT_HANDLE_CHECK( hstmt, rc);
printf("\n%sRecord field/Column information before copying descriptor: \n",sp5 );
for(i=1; i<= num_cols; i++){
rc = SQLGetDescRec(hIRD, i, colname, sizeof(colname),
&namelen, &type, &subtype, &width, &precision,
&scale, &nullable);
if (rc == SQL_SUCCESS) {
printf("%sColumn = %d: \n",sp5,i );
printf("%sName = %s \n", sp8,colname );
printf("%sData type = %d \n", sp8,type );
printf("%sSub type = %d \n", sp8,subtype );
printf("%sWidth = %d \n", sp8,width );
printf("%sPrecision = %d \n", sp8,precision );
printf("%sScale = %d \n", sp8,scale );
printf("%sNullable = %d \n", sp8,nullable );
}
STMT_HANDLE_CHECK( hstmt, rc);
}
hstmt1=hstmt;
strcpy((char*)colname,"Yes");
type=0;subtype=0;width=0;precision=0;
scale=0;nullable=0;
/* create explicit descriptor handle */
rc = SQLAllocHandle( SQL_HANDLE_DESC, hdbc, &hARD ) ;
DBC_HANDLE_CHECK( hdbc, rc);
/* copy descriptor */
rc = SQLCopyDesc(hIRD,hARD);
STMT_HANDLE_CHECK( hstmt1, rc);
rc = SQLSetStmtAttr(hstmt1, SQL_ATTR_APP_ROW_DESC,(SQLPOINTER)hARD, 0 );
STMT_HANDLE_CHECK( hstmt1, rc);
/* Set record/column values via descriptor */
type = SQL_SMALLINT;
length = 2;
rc = SQLSetDescRec(hARD, 1, type, 0,
length, 0, 0, &id_no, &datalen, NULL);
STMT_HANDLE_CHECK( hstmt1, rc);
type = SQL_CHAR;
length = 15;
rc = SQLSetDescRec(hARD, 2, type, 0,
length, 0, 0, thename, &nameleng, NULL);
STMT_HANDLE_CHECK( hstmt1, rc);
printf("\n%sRecord field/Column information after copying descriptor: \n",sp5 );
/* Get the record/column value after setting */
for(i=1; i<= num_cols; i++){
rc = SQLGetDescRec(hARD, i, colname, sizeof(colname),
&namelen, &type, &subtype, &width, &precision,
&scale, &nullable);
STMT_HANDLE_CHECK( hstmt1, rc);
if (rc == SQL_SUCCESS) {
printf("%sColumn = %d: \n",sp5,i );
printf("%sData type = %d \n", sp8,type );
printf("%sSub type = %d \n", sp8,subtype );
printf("%sWidth = %d \n", sp8,width );
printf("%sPrecision = %d \n", sp8,precision );
printf("%sScale = %d \n", sp8,scale );
printf("%sNullable = %d \n", sp8,nullable );
}
}
/* get the result set and print it without using bindcol */
rc = SQLFetch( hstmt1 );
STMT_HANDLE_CHECK( hstmt1, rc);
if (rc == SQL_SUCCESS){
printf("%sResultset after using copy descriptor \n",sp5);
printf("%s-ID-,---NAME---- \n",sp8);
}
while ( rc == SQL_SUCCESS){
printf("%s%4d %s \n",sp8,id_no,thename);
rc = SQLFetch( hstmt1 );
}
STMT_HANDLE_CHECK( hstmt1, rc);
/* free the statement handle */
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, rc);
return(rc);
}
/******************************************************************************
** DescrUse - how to use descriptors
******************************************************************************/
int DescSetGetField( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
SQLRETURN rc = 0;
SQLHANDLE hstmt, hstmt1 ; /* statement handle */
SQLHANDLE hIPD, hIRD, hIRD1, hARD; /* descriptor handle */
SQLSMALLINT descFieldAllocType;
SQLSMALLINT descFieldParameterType;
SQLCHAR * stmt = ( SQLCHAR * )
"SELECT deptnumb, location FROM org WHERE division = ?";
SQLCHAR * stmt1 = ( SQLCHAR * )
"SELECT deptnumb,location FROM org WHERE division = 'Western'";
char divisionParam[15] ;
struct
{ SQLINTEGER ind ;
SQLSMALLINT val ;
} deptnumb ; /* variable to be bound to the DEPTNUMB column */
struct
{ SQLINTEGER ind ;
SQLCHAR val[15] ;
} location ; /* variable to be bound to the LOCATION column */
static char ALLOCTYPES[][21] = { "- No 0 Value-",
"SQL_DESC_ALLOC_AUTO",
"SQL_DESC_ALLOC_USER" };
static char PARAMTYPE[][24] = { "- No 0 Value-",
"SQL_PARAM_INPUT",
"SQL_PARAM_INPUT_OUTPUT",
"- No 3 Value -",
"SQL_PARAM_OUTPUT" };
int colCount;
SQLCHAR descFieldTypeName[25];
SQLCHAR descFieldLabel[25];
SQLSMALLINT dept_no ;
char loc[15] ;
char sp5[]=" ",sp8[]=" ";
SQLINTEGER indicator;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLGetDescField\n-SQLSetDescField");
printf("\nOther CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLGetStmtAttr\n");
printf("-SQLPrepare\n-SQLBindParameter\n");
printf("-SQLExecute\n-SQLBindCol\n");
printf("-SQLFetch\n-SQLFreeHandle\n");
printf("TO USE DESCRIPTORS:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt1 ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* get implicit parameter descriptor handle */
sqlrc = SQLGetStmtAttr ( hstmt,
SQL_ATTR_IMP_PARAM_DESC,
&hIPD,
SQL_IS_POINTER,
NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* see how the header field SQL_DESC_ALLOC_TYPE is set. */
sqlrc = SQLGetDescField( hIPD,
0, /* ignored for header fields */
SQL_DESC_ALLOC_TYPE,
&descFieldAllocType, /* The result */
SQL_IS_SMALLINT,
NULL ); /* ignored */
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* print the descriptor information */
printf("\n The IPD header descriptor field \n");
printf(" SQL_DESC_ALLOC_TYPE is %s\n",
ALLOCTYPES[descFieldAllocType]);
/* prepare the statement */
printf(" Prepare the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind divisionParam to the statement */
printf(" Bind divisionParam to the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 15, 0, divisionParam, 15, NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* see how the field SQL_DESC_PARAMETER_TYPE is set. */
sqlrc = SQLGetDescField( hIPD,
1, /* Look at the parameter */
SQL_DESC_PARAMETER_TYPE,
&descFieldParameterType, /* The result */
SQL_IS_SMALLINT,
NULL ); /* ignored */
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* print the descriptor information */
printf(" The IPD record descriptor field \n");
printf(" SQL_DESC_PARAMETER_TYPE is %s\n",
PARAMTYPE[descFieldParameterType]);
/* execute the statement for divisionParam = Eastern */
printf(" Execute the prepared statement for\n");
printf(" divisionParam = 'Eastern'\n");
strcpy( divisionParam, "Eastern");
sqlrc = SQLExecute( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0, &deptnumb.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR, location.val, 15, &location.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch each row, and display */
printf(" Fetch each row and dispaly.\n");
printf(" DEPTNUMB LOCATION \n" ) ;
printf(" -------- -------------\n" ) ;
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while (sqlrc != SQL_NO_DATA_FOUND)
{ printf( " %-8d %-14.14s \n", deptnumb.val, location.val ) ;
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* get implicit row descriptor handle */
sqlrc = SQLGetStmtAttr ( hstmt,
SQL_ATTR_IMP_ROW_DESC,
&hIRD,
SQL_IS_POINTER,
NULL);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* print out some implementation row descriptor fields */
/* from the last SQLFetch() above */
for (colCount = 1; colCount <=2; colCount++)
{ printf(" Information for column %i\n", colCount);
/* see how the descriptor record field SQL_DESC_TYPE_NAME is set */
rc = SQLGetDescField( hIRD,
(SQLSMALLINT)colCount,
SQL_DESC_TYPE_NAME, /* record field */
descFieldTypeName, /* The result */
25,
NULL ); /* ignored */
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" IRD record descriptor field \n");
printf(" SQL_DESC_TYPE_NAME is %s\n",
descFieldTypeName);
/* see how the descriptor record field SQL_DESC_LABEL is set */
rc = SQLGetDescField( hIRD,
(SQLSMALLINT)colCount,
SQL_DESC_LABEL, /* record field */
descFieldLabel, /* The result */
25,
NULL ); /* ignored */
STMT_HANDLE_CHECK( hstmt, sqlrc);
printf(" IRD record descriptor field \n");
printf(" SQL_DESC_LABEL is %s\n",
descFieldLabel);
}
printf("%sPrepare the statement\n",sp5);
printf("%s%s\n", sp8,stmt1);
sqlrc = SQLPrepare( hstmt1, stmt1, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt1, sqlrc);
/* get implicit row descriptor handle */
sqlrc = SQLGetStmtAttr ( hstmt1,
SQL_ATTR_APP_ROW_DESC,
&hARD,
SQL_IS_INTEGER,
&indicator);
STMT_HANDLE_CHECK( hstmt1, sqlrc);
sqlrc = SQLExecute( hstmt1 ) ;
STMT_HANDLE_CHECK( hstmt1, sqlrc);
sqlrc = SQLBindCol( hstmt1, 2, SQL_C_CHAR,location.val,15,&indicator);
STMT_HANDLE_CHECK( hstmt1, sqlrc);
rc = SQLSetDescField( hARD,1,
SQL_DESC_TYPE,
(SQLPOINTER)SQL_SMALLINT,
SQL_IS_SMALLINT);
STMT_HANDLE_CHECK( hstmt1, rc);
rc = SQLSetDescField( hARD,1,
SQL_DESC_DATA_PTR,
&dept_no, /* value set to the field*/
SQL_IS_SMALLINT);
STMT_HANDLE_CHECK( hstmt1, rc);
rc = SQLSetDescField( hARD,2,
SQL_DESC_TYPE,
(SQLPOINTER)SQL_CHAR,
SQL_IS_SMALLINT);
STMT_HANDLE_CHECK( hstmt1, rc);
rc = SQLSetDescField( hARD,2,
SQL_DESC_LENGTH,
(SQLPOINTER)15,
SQL_IS_INTEGER);
STMT_HANDLE_CHECK( hstmt1, rc);
rc = SQLSetDescField( hARD,2,
SQL_DESC_DATA_PTR,
(SQLPOINTER)loc, 15);
STMT_HANDLE_CHECK( hstmt1, rc);
/* fetch each row, and display */
printf("\n%sFetch rows and dispaly after using SetDescField.\n",sp5);
printf("%sDEPTNUMB LOCATION \n",sp8 ) ;
printf("%s-------- -------------\n",sp8 ) ;
sqlrc = SQLFetch( hstmt1 );
STMT_HANDLE_CHECK( hstmt1, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND) {
printf("\n Data not found.\n");
}
if (sqlrc == SQL_SUCCESS_WITH_INFO) {
printf("\n SUCCESS_WITH_INFO \n");
}
while (sqlrc != SQL_NO_DATA_FOUND) {
printf( "%s%-8d %s \n", sp8,dept_no, loc ) ;
sqlrc = SQLFetch( hstmt1 );
}
STMT_HANDLE_CHECK( hstmt1, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt1 ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}