/*******************************************************************************
**
** Source File Name = getsqlca.c 1.3
**
** 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 :
** Example of executing interactive SQL statements, displaying result sets
** and simple transaction management.
** This is indentical to adhoc.c, with the addition of estimate information
** for the cost and # of rows to be returned.
** Runstats should be run on the tables accessed in order to receive
** accurate estimate information.
**
** 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 "samputil.h" /* Header file for CLI sample code */
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
#define MAX_STMT_LEN 255
#define MAXCOLS 100
#ifndef max
#define max(a,b) (a > b ? a : b)
#endif
/*
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] ;
/* Function declarations: */
int process_stmt( SQLHANDLE, SQLHANDLE, SQLHANDLE, SQLCHAR * ) ;
/*******************************************************************
** main
** - initialize
** - start a transaction
** - get statement
** - another statement?
** - COMMIT or ROLLBACK
** - another transaction?
** - terminate
*******************************************************************/
int
main( int argc, char * argv[] )
{
SQLHANDLE henv, hdbc, hstmt ;
SQLCHAR sqlstmt[MAX_STMT_LEN + 1] = "";
SQLCHAR sqltrans[sizeof("ROLLBACK")];
SQLRETURN rc;
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Before making any API calls for 68K environment, need to initialize the
Library Manager */
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
/* 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 ) ) ;
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
printf("Enter an SQL statement to start a transaction(or 'q' to Quit):\n");
gets((char *)sqlstmt);
while (sqlstmt[0] != 'q') {
while (sqlstmt[0] != 'q') {
if ( process_stmt( henv, hdbc, hstmt, sqlstmt ) == SQL_ERROR )
return( SQL_ERROR ) ;
printf("Enter an SQL statement(or 'q' to Quit):\n");
gets((char *)sqlstmt);
}
printf("Enter 'c' to COMMIT or 'r' to ROLLBACK the transaction\n");
fgets((char *)sqltrans, sizeof("ROLLBACK"), stdin);
if (sqltrans[0] == 'c')
if ( ( rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT )
) == SQL_SUCCESS
) printf( "Transaction commit was successful\n" ) ;
else CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
if (sqltrans[0] == 'r')
if ( ( rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK )
) == SQL_SUCCESS
) printf( "Transaction roll back was successful\n" ) ;
else CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
printf("Enter an SQL statement to start a transaction or 'q' to quit\n");
gets((char *)sqlstmt);
}
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "\n>Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
return( SQL_SUCCESS ) ;
} /* end main */
/*******************************************************************
** process_stmt
** - allocates a statement handle
** - executes the statement
** - Gets SQLCA, prompts to continue
** - 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 handle
*******************************************************************/
int process_stmt( SQLHANDLE henv,
SQLHANDLE hdbc,
SQLHANDLE hstmt,
SQLCHAR * sqlstr
) {
SQLSMALLINT nresultcols;
SQLINTEGER rowcount;
SQLRETURN rc;
struct sqlca sqlca;
SQLCHAR prompt[255];
/*--> SQLL1X15.SCRIPT */
/* execute the SQL statement in "sqlstr" */
rc = SQLPrepare(hstmt, sqlstr, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf(" Relative Cost=[%ld] Estimated # rows=[%ld]\n"
" Continue with execution(Y or N)?\n",
sqlca.sqlerrd[3], sqlca.sqlerrd[2]);
gets((char *)prompt);
if (prompt[0] == 'n' || prompt[0] =='N')
return(0);
if ( rc != SQL_SUCCESS )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLExecute(hstmt);
/*<-- */
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 )
printf( "Statement executed, %ld rows affected\n", rowcount ) ;
/* assume statement is UPDATE, INSERT, DELETE */
else printf( "Statement completed successful\n" ) ;
/* assume statement is GRANT, REVOKE or a DLL statement */
} 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 */