This example is a modified version of the example contained in the X/Open SQL CLI document. It shows the execution of interactive SQL statements, and follows the flow described in Chapter 2, Writing a DB2 CLI Application.
/* From CLI sample adhoc.c */
/* ... */
/*******************************************************************
** process_stmt
** - allocates a statement resources
** - executes the statement
** - determines the type of statement
** - if there are no result columns, therefore non-select statement
** - if rowcount > 0, assume statement was UPDATE, INSERT, DELETE
** else
** - assume a DDL, or Grant/Revoke statement
** else
** - must be a select statement.
** - display results
** - frees the statement resources
*******************************************************************/
int process_stmt( SQLHANDLE hstmt, SQLCHAR * sqlstr ) {
SQLSMALLINT nresultcols;
SQLINTEGER rowcount;
SQLRETURN rc;
/* execute the SQL statement in "sqlstr" */
rc = SQLExecDirect(hstmt, sqlstr, SQL_NTS);
if (rc != SQL_SUCCESS)
if (rc == SQL_NO_DATA_FOUND) {
printf("\nStatement executed without error, however,\n");
printf("no data was found or modified\n");
return (SQL_SUCCESS);
}
else CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLNumResultCols(hstmt, &nresultcols);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* determine statement type */
if (nresultcols == 0) { /* statement is not a select statement */
rc = SQLRowCount(hstmt, &rowcount);
if (rowcount > 0) /* assume statement is UPDATE, INSERT, DELETE */
printf("Statement executed, %ld rows affected\n", rowcount);
else /* assume statement is GRANT, REVOKE or a DLL statement */
printf( "Statement completed successful\n" ) ;
}
else print_results( hstmt ) ; /* display the result set */
/* end determine statement type */
/* free statement resources */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( 0 ) ;
} /* end process_stmt */
/* From CLI sample samputil.c */
/* ... */
/* print_results */
SQLRETURN print_results( SQLHANDLE hstmt ) {
SQLCHAR colname[32] ;
SQLSMALLINT coltype ;
SQLSMALLINT colnamelen ;
SQLSMALLINT nullable ;
SQLUINTEGER collen[MAXCOLS] ;
SQLSMALLINT scale ;
SQLINTEGER outlen[MAXCOLS] ;
SQLCHAR * data[MAXCOLS] ;
SQLCHAR errmsg[256] ;
SQLRETURN rc ;
SQLSMALLINT nresultcols, i ;
SQLINTEGER displaysize ;
rc = SQLNumResultCols( hstmt, &nresultcols ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
for ( i = 0; i < nresultcols; i++ ) {
SQLDescribeCol( hstmt,
( SQLSMALLINT ) ( i + 1 ),
colname,
sizeof(colname),
&colnamelen,
&coltype,
&collen[i],
&scale,
NULL
) ;
/* get display length for column */
SQLColAttribute( hstmt,
( SQLSMALLINT ) ( i + 1 ),
SQL_DESC_DISPLAY_SIZE,
NULL,
0,
NULL,
&displaysize
) ;
/*
Set column length to max of display length,
and column name length. Plus one byte for
null terminator.
*/
collen[i] = max( displaysize,
strlen( ( char * ) colname )
) + 1 ;
printf( "%-*.*s",
( int ) collen[i],
( int ) collen[i],
colname
) ;
/* allocate memory to bind column */
data[i] = ( SQLCHAR * ) malloc( ( int ) collen[i] ) ;
/* bind columns to program vars, converting all types to CHAR */
SQLBindCol( hstmt,
( SQLSMALLINT ) ( i + 1 ),
SQL_C_CHAR,
data[i],
collen[i],
&outlen[i]
) ;
}
printf( "\n" ) ;
/* display result rows */
while ( SQLFetch( hstmt ) != SQL_NO_DATA ) {
errmsg[0] = '\0' ;
for ( i = 0; i < nresultcols; i++ ) {
/* Check for NULL data */
if ( outlen[i] == SQL_NULL_DATA )
printf( "%-*.*s",
( int ) collen[i],
( int ) collen[i],
"NULL"
) ;
else { /* Build a truncation message for any columns truncated */
if ( outlen[i] >= collen[i] ) {
sprintf( ( char * ) errmsg + strlen( ( char * ) errmsg ),
"%d chars truncated, col %d\n",
( int ) outlen[i] - collen[i] + 1,
i + 1
) ;
}
/* Print column */
printf( "%-*.*s",
( int ) collen[i],
( int ) collen[i],
data[i]
) ;
}
} /* for all columns in this row */
printf( "\n%s", errmsg ) ; /* print any truncation messages */
} /* while rows to fetch */
/* free data buffers */
for ( i = 0; i < nresultcols; i++ ) {
free( data[i] ) ;
}
return( SQL_SUCCESS ) ;
} /* end print_results */