/*******************************************************************************
**
** Source File Name = spcall.c
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
** PURPOSE:
** Call stored procedures (eg. spcall <procname> <args>
** or spcall <schema>.<procname> <args>)
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "utilcli.h"
#define MAX_NUM_PARAMS 25
#define MAX_BUF_LEN 1024
#define MAIN_PANIC \
sqlrc = SQLEndTran( SQL_HANDLE_ENV, henv, SQL_ROLLBACK ); \
HANDLE_CHECK( SQL_HANDLE_ENV, henv, sqlrc, &henv, &hdbc ) ;
int main( int argc, char * argv[] )
{
int rc = 0;
SQLHANDLE henv, hdbc, hstmt, hstmt1;
SQLRETURN sqlrc ;
SQLCHAR sqlstmt[1024];
char *procname;
char **procargs;
short input_args = 0;
int separator = 0;
char* temp_str = 0;
/*
Variables for database name, user id and password.
*/
SQLCHAR db[SQL_MAX_DSN_LENGTH + 1] ;
SQLCHAR uid[MAX_UID_LENGTH + 1] ;
SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
SQLCHAR colNamePattern[] = "%";
char colSchemaNamePattern[] = "%";
struct
{ SQLINTEGER ind ;
SQLCHAR val[129] ;
} colName, schemaName;
struct
{ SQLINTEGER ind ;
SQLINTEGER val;
} colLength, colOrdinal;
struct
{ SQLINTEGER ind ;
SQLSMALLINT val;
} colScale, colType, colDataType;
char tempSchema[129] = "\0";
SQLUSMALLINT ParameterNumber; /* ipar */
SQLSMALLINT InputOutputType[MAX_NUM_PARAMS]; /* fParamType of each param */
SQLSMALLINT ValueType = SQL_C_CHAR; /* fCType; always pass strings to CLI */
SQLSMALLINT ParameterType[MAX_NUM_PARAMS]; /* fSqlType of each param */
SQLUINTEGER ColumnSize; /* cbColDef */
SQLSMALLINT DecimalDigits; /* ibScale */
SQLPOINTER ParameterValuePtr;/* rgbValue */
SQLINTEGER BufferLength = MAX_BUF_LEN; /* cbValueMax */
SQLINTEGER StrLen_or_IndPtr[MAX_NUM_PARAMS];/* pcbValue */
SQLCHAR ParameterName[MAX_NUM_PARAMS][129]; /* fSqlType of each param */
sqlint32 longval;
SQLCHAR bufs[MAX_NUM_PARAMS][MAX_BUF_LEN]; /* buffers for parameters */
short i, has_out_parms = 0, invalid_cmd_line = 0, flen;
int nparams = argc - 2; /* # proc parameters doesn't include
'spcall' and proc name */
char *field;
db[0] = '\0';
uid[0] = '\0';
pwd[0] = '\0';
/*************************************************************/
/* Parse command-line parameters */
/*************************************************************/
if (argc < 2)
{
invalid_cmd_line = 1;
}
else
{
i = 1;
while(*argv[i] == '-')
{
if (argc < i + 2)
{
invalid_cmd_line = 1;
break;
} /* if */
switch(*(argv[i]+1))
{
case 'd':
flen = SQL_MAX_DSN_LENGTH;
field = (char *)db;
break;
case 'u':
flen = MAX_UID_LENGTH;
field = (char *)uid;
break;
case 'p':
flen = MAX_PWD_LENGTH;
field = (char *)pwd;
break;
default:
invalid_cmd_line = 1;
break;
} /* switch */
strncpy(field, (const char *)argv[i+1], flen);
field[flen] = 0;
i += 2;
nparams -= 2;
} /* while */
} /* if */
if (invalid_cmd_line)
{
printf ("\nUSAGE: spcall [-d <db name>] [-u <user name>] [-p <password>] procname [input-arg1 input-arg2 ...]\n\n");
return 1;
} /* if */
/* Remember the procedure to call, and its parameters. */
procname = argv[i];
procargs = &argv[i+1];
/* upper case the proc name */
for(i = 0; i < strlen(procname); i++)
{
if(isalpha(procname[i]))
procname[i] = toupper(procname[i]);
} /* for */
/* separate the schema name if specified */
if ((temp_str = strstr(procname, ".")) != NULL)
{
separator = strlen(procname) - strlen(temp_str);
strncpy(colSchemaNamePattern, procname, separator);
procname = temp_str+1;
}
/*************************************************************/
/* Setup CLI environment */
/*************************************************************/
/* allocate an environment handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( sqlrc != SQL_SUCCESS )
{ printf( "\n--ERROR while allocating the environment handle.\n" ) ;
printf( " sqlrc = %d\n", sqlrc);
printf( " line = %d\n", __LINE__);
printf( " file = %s\n", __FILE__);
return( 1 ) ;
} /* if */
/* enable sending column names over the network */
sqlrc = SQLSetEnvAttr( henv,
SQL_ATTR_USE_LIGHT_OUTPUT_SQLDA,
( SQLPOINTER ) SQL_FALSE,
SQL_FALSE
) ;
HANDLE_CHECK( SQL_HANDLE_ENV, henv, sqlrc, &henv, &hdbc ) ;
/*************************************************************/
/* Connect to the Database at which the SP will execute */
/*************************************************************/
/* allocate a database connection handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_ENV, henv, sqlrc, &henv, &hdbc ) ;
/* set AUTOCOMMIT off */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
if(db[0] == '\0')
{
strcpy((char *)db, "sample");
} /* if */
/* connect to the database */
printf( "\nConnecting to %s ...\n", db ) ;
sqlrc = SQLConnect( hdbc,
(SQLCHAR *)db, SQL_NTS,
(SQLCHAR *)uid, SQL_NTS,
(SQLCHAR *)pwd, SQL_NTS
) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
printf( "Connected to %s.\n", db ) ;
/*************************************************************/
/* Obtain the SP's parameters */
/*************************************************************/
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
sqlrc = SQLProcedureColumns( hstmt,
NULL, 0, /* catalog name not used */
(unsigned char *)colSchemaNamePattern, SQL_NTS, /* schema name not currently used */
(unsigned char *)procname, SQL_NTS,
colNamePattern, SQL_NTS /* all columns */
);
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/* Bind the result col for schema name. */
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR, schemaName.val, 129, &schemaName.ind);
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/* Bind the result col for column name. */
sqlrc = SQLBindCol( hstmt, 4, SQL_C_CHAR, colName.val, 129, &colName.ind );
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/* Bind the result col for column type (IN, OUT, INOUT). */
sqlrc = SQLBindCol( hstmt, 5, SQL_C_SHORT,
(SQLPOINTER)&colType.val, sizeof(colType.val), &colType.ind );
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/* Bind the result col for data type (SQL data type). */
sqlrc = SQLBindCol( hstmt, 6, SQL_C_SHORT, (SQLPOINTER)&colDataType.val,
sizeof(colDataType.val), &colDataType.ind );
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/* Bind the result col for column size. */
sqlrc = SQLBindCol( hstmt, 8, SQL_C_LONG, (SQLPOINTER)&colLength.val,
sizeof(colLength.val), &colLength.ind ) ;
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/* Bind the result col for the scale of the parameter. */
sqlrc = SQLBindCol( hstmt, 10, SQL_C_SHORT, (SQLPOINTER)&colScale.val,
sizeof(colScale.val), &colScale.ind ) ;
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/* Bind the result col for parameter ordinal. */
sqlrc = SQLBindCol( hstmt, 18, SQL_C_LONG, (SQLPOINTER)&colOrdinal.val,
sizeof(colOrdinal.val), &colOrdinal.ind ) ;
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/*************************************************************/
/* Fetch each parameter for the SP, and: */
/* o build the CALL statement */
/* o configure input/output parameters for SP call */
/*************************************************************/
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt1 );
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt1, sqlrc, &henv, &hdbc );
strcpy((char *)sqlstmt, "CALL ");
strcat((char *)sqlstmt, (const char *)procname);
strcat((char *)sqlstmt, " (");
ParameterNumber = 0;
input_args = 0;
sqlrc = SQLFetch( hstmt );
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
while ( sqlrc != SQL_NO_DATA_FOUND )
{
if (ParameterNumber >= MAX_NUM_PARAMS)
{
printf ("\nERROR: cannot handle more than %d parameters ...\n\n", MAX_NUM_PARAMS);
MAIN_PANIC;
} /* if */
/**************************************************/
/* Compose CALL SP statement, parm by parm */
/**************************************************/
strcat((char *)sqlstmt, " ?,");
/**************************************************/
if (strlen(tempSchema) > 0) {
if (strcmp((char*)schemaName.val, tempSchema)) {
printf ("\nERROR: Procedure exists in multiple schema ...\n\n");
MAIN_PANIC;
}
}
else {
strncpy(tempSchema, (const char*)schemaName.val, schemaName.ind);
}
/**************************************************/
/* Remember Column name for printing SP output */
/**************************************************/
strncpy((char *) ParameterName[ParameterNumber],
(const char *)colName.val, colName.ind);
ParameterName[ParameterNumber][colName.ind] = '\0';
/**************************************************/
/* Obtain parameter type, and data if input. */
/**************************************************/
InputOutputType[ParameterNumber] = colType.val;
ParameterValuePtr = (SQLPOINTER) bufs[ParameterNumber];
/* Input parameters of the SP must have
corresponding cmd-line parameters. */
if (( colType.val == SQL_PARAM_INPUT_OUTPUT ) ||
( colType.val == SQL_PARAM_INPUT ))
{
/* Do we have a cmd-line parm for this SP parm? */
if ( input_args >= nparams )
{
printf ("\nERROR: Too few parameters in the command line ...\n\n");
MAIN_PANIC;
}
else /* map cmd-line input parm to SP input parm */
{
strcpy((char *)bufs[ParameterNumber], procargs[input_args++]);
} /* if */
/* Also, if the 'NULL' keyword is supplied on the
cmd-line, this means a NULLable input parameter. */
if ( strcmp((const char *)bufs[ParameterNumber], "NULL") == 0 )
{
StrLen_or_IndPtr[ParameterNumber] = SQL_NULL_DATA;
}
else /* null-terminated string */
{
StrLen_or_IndPtr[ParameterNumber] = SQL_NTS;
} /* if */
} /* if */
/* Remember that there was some output
to process later on. */
if (( colType.val == SQL_PARAM_INPUT_OUTPUT ) ||
( colType.val == SQL_PARAM_OUTPUT ))
{
has_out_parms = 1;
} /* if */
/* SQL data type of the parameter. */
ParameterType[ParameterNumber] = colDataType.val;
/**************************************************/
/* Parameter output sizes */
/**************************************************/
/* ColumnSize is only relevant for CHARACTER, DECIMAL and NUMERIC */
ColumnSize = colLength.val; /* maximum length / maximum decimal precision */
/* DecimalDigits is only relevant for DECIMAL and NUMERIC */
DecimalDigits = colScale.val; /* scale */
/**************************************************/
/* Bind the SP parameter */
/**************************************************/
sqlrc = SQLBindParameter( hstmt1,
ParameterNumber + 1, /* NOTE that here the 1st is 1 */
InputOutputType[ParameterNumber],
ValueType,
ParameterType[ParameterNumber],
ColumnSize,
DecimalDigits,
ParameterValuePtr,
BufferLength,
&(StrLen_or_IndPtr[ParameterNumber])
);
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt1, sqlrc, &henv, &hdbc ) ;
/**************************************************/
/* Get the next SP parameter */
/**************************************************/
ParameterNumber++;
sqlrc = SQLFetch( hstmt );
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
} /* while */
/*************************************************************/
/* Final processing on SP's parameters */
/*************************************************************/
/* Finished getting SP's parameters, so must
free the associated stmt handle. */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt, sqlrc, &henv, &hdbc ) ;
/* If there's a mismatch between cmd-line parameters
supplies as SP input parameters, and the actual
number of input parameters required by SP, then
signal the error. */
if (input_args != nparams)
{ /* too many args in the command line */
printf ("\nWARNING: Wrong number of input parameters (expected %d)...\n\n", input_args);
}
/*************************************************************/
/* Invoke the SP */
/*************************************************************/
/* Generate the closing bracket (overwrite the last ',') */
sqlstmt[strlen((const char *)sqlstmt) - 1] = ')';
/* Prepare the call statement */
sqlrc = SQLPrepare(hstmt1, sqlstmt, SQL_NTS);
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt1, sqlrc, &henv, &hdbc ) ;
/* Execute the call */
sqlrc = SQLExecute( hstmt1 ) ;
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt1, sqlrc, &henv, &hdbc ) ;
/*************************************************************/
/* Print SP's output parameters, if any. */
/*************************************************************/
if( has_out_parms )
{
/* printf ("\n----- OUTPUT PARAMETERS -----"); */
for(i = 0; i < ParameterNumber; i++)
{
if(InputOutputType[i] != SQL_PARAM_INPUT)
{
printf("\n%s: ", ParameterName[i]);
switch(StrLen_or_IndPtr[i])
{
case SQL_NULL_DATA:
{
printf ("NULL");
break;
}
case SQL_NO_TOTAL:
{
printf ("UNKNOWN LENGTH");
break;
}
default:
{
switch(ParameterType[i])
{
case SQL_CHAR:
case SQL_VARCHAR:
case SQL_LONGVARCHAR:
case SQL_INTEGER:
case SQL_SMALLINT:
case SQL_BIGINT:
case SQL_FLOAT:
case SQL_DOUBLE:
case SQL_REAL:
case SQL_TYPE_DATE:
case SQL_TYPE_TIME:
case SQL_TYPE_TIMESTAMP:
case SQL_DECIMAL:
case SQL_NUMERIC:
printf ("%*.*s", StrLen_or_IndPtr[i], StrLen_or_IndPtr[i], bufs[i]);
break;
default:
printf( "\nERROR: Unknown type code %d for OUTPUT paramenter %d. Rolling back ...\n",
ParameterType[i], i );
MAIN_PANIC;
} /* switch */
break;
} /* default */
} /* switch */
} /* if */
}/* for */
printf ("\n\n");
} /* if */
/*************************************************************/
/* Print SP's result sets, if any. */
/*************************************************************/
do
{
rc = StmtResultPrint( hstmt1);
}
while( SQLMoreResults( hstmt1) == SQL_SUCCESS);
/*************************************************************/
/* Cleanup */
/*************************************************************/
/* Commit the transaction */
sqlrc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
/* Finished calling SP, so uncallocate handle. */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt1 ) ;
HANDLE_CHECK( SQL_HANDLE_STMT, hstmt1, sqlrc, &henv, &hdbc ) ;
/* Disconnect from the db */
sqlrc = SQLDisconnect( hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
/* free connection handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
/* free env handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
HANDLE_CHECK( SQL_HANDLE_ENV, henv, sqlrc, &henv, &hdbc ) ;
return 0;
} /* end main */