/******************************************************************************
**
** Source File Name = adhoc.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
** PURPOSE :
** this is an interactive shell for SQL statements, displaying the
** result sets and simple transaction management.
**
** STRUCTURES USED :
** sqlca
** sqlda
**
** APIs USED :
**
** FUNCTIONS DECLARED :
** 'C' COMPILER LIBRARY :
** stdio.h - printf
** string.h - strncpy
**
** OTHER :
** external : [in the file UTIL.C]
** check_error : Checks for SQLCODE error, and prints out any
** related information available.
** init_da: Initialiaze the memory space for an SQLDA.
** alloc_host_vars: Allocate memory to be filled with SQLDA
** data.
** free_da: Frees up memory which has been allocated
** as an SQLDA structure.
** display_col_titles: Display column titles.
** display_da: Display row information.
**
** EXTERNAL DEPENDENCIES :
** - Ensure existence of database for precompile purposes.
** - Precompile with the SQL precompiler (PREP in DB2)
** - Bind to a database (BIND in DB2)
** - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
** or the Microsoft Visual C++ compiler (Windows)
** or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlcodes.h>
#include <sqlda.h> /* :rk.1:erk. */
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA ; /* :rk.2:erk. */
#define SQLSTATE sqlca.sqlstate
int process_statement( char * ) ;
int main( int argc, char *argv[] ) {
int rc ;
char sqlInput[256] ;
char st[1024] ;
EXEC SQL BEGIN DECLARE SECTION ; /* :rk.3:erk. */
char userid[9] ;
char passwd[19] ;
EXEC SQL END DECLARE SECTION ;
printf( "Sample C program : ADHOC interactive SQL\n" ) ;
/* Initialize the connection to a database. */
if ( argc == 1 ) {
EXEC SQL CONNECT TO sample ;
EMB_SQL_CHECK( "CONNECT TO SAMPLE" ) ;
}
else if ( argc == 3 ) {
strcpy( userid, argv[1] ) ;
strcpy( passwd, argv[2] ) ;
EXEC SQL CONNECT TO sample USER :userid USING :passwd ; /* :rk.4:erk. */
EMB_SQL_CHECK( "CONNECT TO SAMPLE" ) ; /* :rk.5:erk. */
}
else {
printf( "\nUSAGE: adhoc [userid passwd]\n\n" ) ;
return( 1 ) ;
} /* endif */
printf( "Connected to database SAMPLE\n" ) ;
/* Enter the continuous command line loop. */
*sqlInput = '\0' ;
while ( ( *sqlInput != 'q' ) && ( *sqlInput != 'Q' ) ) { /* :rk.6:erk. */
printf( "Enter an SQL statement or 'quit' to Quit :\n" ) ;
gets( sqlInput ) ;
if ( ( *sqlInput == 'q' ) || ( *sqlInput == 'Q' ) ) break ;
if ( *sqlInput == '\0' ) { /* Don't process the statement */
printf( "No characters entered.\n" ) ;
continue ;
}
strcpy( st, sqlInput ) ;
while ( sqlInput[strlen( sqlInput ) - 1] == '\\' ) {
st[strlen( st ) - 1] = '\0' ;
gets( sqlInput ) ;
strcat( st, sqlInput ) ;
}
/* Process the statement. */
rc = process_statement( st ) ;
}
printf( "Enter 'c' to COMMIT or Any Other key to ROLLBACK the transaction :\n" ) ;
gets( sqlInput ) ;
if ( ( *sqlInput == 'c' ) || ( *sqlInput == 'C' ) ) {
printf( "COMMITING the transactions.\n" ) ;
EXEC SQL COMMIT ; /* :rk.7:erk. */
EMB_SQL_CHECK( "COMMIT" ) ;
}
else { /* assume that the transaction is to be rolled back */
printf( "ROLLING BACK the transactions.\n" ) ;
EXEC SQL ROLLBACK ; /* :rk.8:erk. */
EMB_SQL_CHECK( "ROLLBACK" ) ;
}
EXEC SQL CONNECT RESET ; /* :rk.9:erk. */
EMB_SQL_CHECK( "CONNECT RESET" ) ;
return( 0 ) ;
}
/******************************************************************************
* FUNCTION : process_statement
* This function processes the inputted statement and then prepares the
* procedural SQL implementation to take place.
******************************************************************************/
int process_statement ( char * sqlInput ) {
int counter = 0 ;
struct sqlda * sqldaPointer ;
short sqlda_d ;
EXEC SQL BEGIN DECLARE SECTION ; /* :rk.3:erk. */
char st[1024] ;
EXEC SQL END DECLARE SECTION ;
strcpy( st, sqlInput ) ; /* :rk.10:erk. */
/* allocate an initial SQLDA temp pointer to obtain information
about the inputted "st" */
init_da( &sqldaPointer, 1 ) ; /* :rk.11:erk. */
EXEC SQL PREPARE statement1 from :st ;
/* EMB_SQL_CHECK( "PREPARE" ) ; */
EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer ;
/* Expecting a return code of 0 or SQL_RC_W236,
SQL_RC_W237, SQL_RC_W238, SQL_RC_W239 for cases
where this statement is a SELECT statment. */
if ( SQLCODE != 0 &&
SQLCODE != SQL_RC_W236 &&
SQLCODE != SQL_RC_W237 &&
SQLCODE != SQL_RC_W238 &&
SQLCODE != SQL_RC_W239
) {
/* An unexpected warning/error has occurred. Check the SQLCA. */
EMB_SQL_CHECK( "DESCRIBE" ) ;
} /* end if */
sqlda_d = sqldaPointer->sqld ;
free( sqldaPointer ) ;
if ( sqlda_d > 0 ) { /* :rk.12:erk. */
/* this is a SELECT statement, a number of columns
are present in the SQLDA */
if ( SQLCODE == SQL_RC_W236 || SQLCODE == 0)
/* this out only needs a SINGLE SQLDA */
init_da( &sqldaPointer, sqlda_d ) ;
if ( SQLCODE == SQL_RC_W237 ||
SQLCODE == SQL_RC_W238 ||
SQLCODE == SQL_RC_W239 )
/* this output contains columns that need a DOUBLED SQLDA */
init_da( &sqldaPointer, sqlda_d * 2 ) ;
/* need to reassign the SQLDA with the correct number
of columns to the SQL statement */
EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer ;
EMB_SQL_CHECK( "DESCRIBE" ) ;
/* allocating the proper amount of memory
space needed for the variables */
alloc_host_vars( sqldaPointer ) ; /* :rk.14:erk. */
/* Don't need to check the SQLCODE for declaration of cursors */
EXEC SQL DECLARE pcurs CURSOR FOR statement1 ; /* :rk.15:erk. */
EXEC SQL OPEN pcurs ; /* :rk.15:erk. */
EMB_SQL_CHECK( "OPEN" ) ;
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer; /* :rk.16:erk. */
EMB_SQL_CHECK( "FETCH" ) ;
/* if the FETCH is successful, obtain data from SQLDA */
/* display the column titles */
display_col_titles( sqldaPointer ) ; /* :rk.17:erk. */
/* display the rows that are fetched */
while ( SQLCODE == 0 ) {
counter++ ;
display_da( sqldaPointer ) ; /* :rk.18:erk. */
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer ;
} /* endwhile */
EXEC SQL CLOSE pcurs ; /* :rk.19:erk. */
EMB_SQL_CHECK( "CLOSE CURSOR" ) ;
printf( "\n %d record(s) selected\n\n", counter ) ;
/* Free the memory allocated to this SQLDA. */
free_da( sqldaPointer ) ;
} else { /* this is not a SELECT statement, execute SQL statement */ /* :rk.13:erk. */
EXEC SQL EXECUTE statement1 ;
EMB_SQL_CHECK( "Executing the SQL statement" ) ;
} /* end if */
return( 0 ) ;
} /* end of program : adhoc.sqc */