/******************************************************************************
**
** Source File Name = spclient.c
**
** 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: This sample program demonstrates stored procedures.
**
**
** EXTERNAL DEPENDENCIES :
** - Compile and link loop with the compiler supported
** on your platform.
**
** For more information about these samples refer to the README file.
**
** For more information on programming in C, refer to the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, refer to the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language refer to the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include <sqlca.h>
#include "utilcli.h" /* Header file for CLI sample code */
SQLCHAR stmt[50];
SQLINTEGER out_sqlcode;
char out_buffer[33];
char out_lang[9];
SQLINTEGER indicator;
struct sqlca sqlca;
SQLRETURN rc,rc1 ;
char procname[254];
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handle */
SQLHANDLE hstmt1; /* statement handle */
SQLHANDLE hstmt2; /* statement handle */
SQLRETURN sqlrc = SQL_SUCCESS;
double out_median;
int calloutlang(SQLHANDLE);
int calloutpara(SQLHANDLE);
int callinpara(SQLHANDLE);
int callinout(SQLHANDLE, double );
int call_one_result_set(SQLHANDLE);
int main(int argc, char *argv[])
{
SQLHANDLE hstmt; /* statement handle */
SQLHANDLE hstmt_one_result; /* statement handle */
SQLHANDLE hstmt_two_result; /* statement handle */
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
sqlint16 testlang;
/* Declare variable for passing data to INOUT_PARAM */
double inout_median;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
if ( rc != 0 ) return( 1 ) ;
/* allocate an environment handle */
printf("\n Allocate an environment handle.\n");
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 ) ;
}
/* allocate a database connection handle */
printf(" Allocate a database connection handle.\n");
sqlrc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_ENV, henv, sqlrc, &henv, &hdbc ) ;
/* connect to the database */
printf( " Connecting to the database %s ...\n", dbAlias ) ;
sqlrc = SQLConnect( hdbc,
(SQLCHAR *)dbAlias, SQL_NTS,
(SQLCHAR *)user, SQL_NTS,
(SQLCHAR *)pswd, SQL_NTS
) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
printf( " Connected to the database %s.\n", dbAlias ) ;
/* set AUTOCOMMIT off */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
/* allocate one or more statement handles */
printf(" Allocate a statement handle.\n");
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt_one_result ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt_two_result ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
/********************************************************\
* Call OUT_LANGUAGE stored procedure *
\********************************************************/
rc = calloutlang(hstmt);
/********************************************************\
* Call OUT_PARAM stored procedure *
\********************************************************/
rc= calloutpara(hstmt);
/********************************************************\
* Call IN_PARAMS stored procedure *
\********************************************************/
rc = callinpara( hstmt);
/********************************************************\
* Call INOUT_PARAM stored procedure *
\********************************************************/
printf("\nCALL stored procedure INOUT_PARAM \n");
printf("Using the median returned by the call to OUT_PARAM\n");
inout_median = out_median;
rc = callinout(hstmt, inout_median);
/********************************************************\
* Call INOUT_PARAM stored procedure again *
\********************************************************/
printf("\nCALL stored procedure INOUT_PARAM again \n");
printf("Using a value that can cause an error in the "
"stored procedure\n");
inout_median=300000;
rc = callinout(hstmt, inout_median);
/********************************************************\
* Call CLOB_EXTRACT stored procedure *
\********************************************************/
rc = extract_from_clob(hstmt);
/********************************************************\
* Call ONE_RESULT_SET stored procedure *
\********************************************************/
rc = call_one_result_set(hstmt_one_result);
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt_one_result ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
/********************************************************\
* Call TWO_RESULT_SETS stored procedure *
\********************************************************/
rc = call_two_result_sets(hstmt_two_result);
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt_two_result ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = alltypes(hstmt);
testlang = strncmp(out_lang, "C", 1);
if (testlang != 0) {
/* Stored procedures of PARAMETER STYLE DB2SQL, DBINFO,
or PROGRAM TYPE MAIN can only be implemented by
LANGUAGE C stored procedures. If out_lang != "C",
we know that those stored procedures are not
implemented, and therefore do not call them. */
}
else {
/********************************************************\
* Call DB2SQL_EXAMPLE stored procedure *
\********************************************************/
rc = db2sqlexample(hstmt,"CLERK");
rc = db2sqlexample(hstmt,"");
/********************************************************\
* Call DBINFO_EXAMPLE stored procedure *
\********************************************************/
rc = dbinfoexample(hstmt);
/********************************************************\
* Call MAIN_EXAMPLE stored procedure *
\********************************************************/
rc = mainexample(hstmt);
}
/* Roll back stored procedure actions
to preserve the state of the database */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
printf("\nStored procedure rolled back.\n\n");
/* Disconnect from Remote Database */
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
printf( "\n>Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS ) return( SQL_ERROR ) ;
return( SQL_SUCCESS ) ;
}
int call_one_result_set(hstmt)
SQLHANDLE hstmt; /* statement handle */
{
/********************************************************\
* Call ONE_RESULT_SET stored procedure *
\********************************************************/
double in_salary, out_salary;
SQLSMALLINT num_cols;
char name[40];
char job[10];
in_salary = out_median;
strcpy(procname, "ONE_RESULT_SET"); /* :rk.1:erk. */
printf("\nCALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL ONE_RESULT_SET ( ?,? )");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &in_salary,
0, NULL);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLNumResultCols( hstmt, &num_cols ) ;
STMT_HANDLE_CHECK( hstmt, rc);
printf("Result set returned %d columns\n", num_cols);
/* bind columns to variables */
rc = SQLBindCol( hstmt, 1, SQL_C_CHAR, name, 40, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindCol( hstmt, 2, SQL_C_CHAR, job, 10, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindCol( hstmt, 3, SQL_C_DOUBLE, &out_salary, 0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
/* fetch result set returned from stored procedure */
rc = SQLFetch( hstmt ); /* :rk.2:erk. */
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
STMT_HANDLE_CHECK( hstmt, rc);
printf("\nFirst result set returned from %s", procname);
printf("\n------Name------, --JOB--, ---Salary-- \n");
while (rc == SQL_SUCCESS && rc != SQL_NO_DATA_FOUND ) /* :rk.3:erk. */
{
printf("%15s,%10s, %.2lf\n",name,job,out_salary);
rc = SQLFetch( hstmt );
}
STMT_HANDLE_CHECK( hstmt, rc);
/* Check that the stored procedure executed successfully */
if (rc == SQL_SUCCESS) {
printf("Stored procedure returned successfully.\n");
}
else {
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
}
rc = SQLCloseCursor(hstmt);
return(rc);
}
int call_two_result_sets(SQLHANDLE hstmt)
{
/********************************************************\
* Call TWO_RESULT_SETS stored procedure *
\********************************************************/
double in_salary, out_salary;
SQLSMALLINT num_cols;
char name[40];
char job[10];
in_salary = out_median;
strcpy(procname, "TWO_RESULT_SETS");
printf("\nCALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL TWO_RESULT_SETS ( ?,? )");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &in_salary,
0, NULL);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
STMT_HANDLE_CHECK( hstmt, rc);
/* bind columns to variables */
rc = SQLBindCol( hstmt, 1, SQL_C_CHAR, name, 40, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindCol( hstmt, 2, SQL_C_CHAR, job, 10, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindCol( hstmt, 3, SQL_C_DOUBLE, &out_salary, 0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
/* fetch first result set returned from stored procedure */
rc = SQLFetch( hstmt );
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
STMT_HANDLE_CHECK( hstmt, rc);
printf("\nFirst result set returned from %s", procname);
printf("\n------Name------, --JOB--, ---Salary-- \n");
while (rc == SQL_SUCCESS && rc != SQL_NO_DATA_FOUND )
{
printf("%15s,%10s, %.2lf\n",name,job,out_salary);
rc = SQLFetch( hstmt );
}
/* Get next result set, until no more result sets are available */
while ((SQLMoreResults(hstmt) != SQL_NO_DATA_FOUND))
{
/* fetch second result set returned from stored procedure */
rc = SQLFetch( hstmt );
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
printf("\nNext result set returned from %s", procname);
printf("\n------Name------, --JOB--, ---Salary-- \n");
while (rc == SQL_SUCCESS && rc != SQL_NO_DATA_FOUND )
{
printf("%15s,%10s, %.2lf\n",name,job,out_salary);
rc = SQLFetch( hstmt );
}
}
STMT_HANDLE_CHECK( hstmt, rc);
/* Check that the stored procedure executed successfully */
if (rc == SQL_SUCCESS) {
printf("Stored procedure returned successfully.\n");
}
else {
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
}
rc = SQLCloseCursor(hstmt);
return(rc);
}
int extract_from_clob(SQLHANDLE hstmt)
{
char in_employee[7];
char out_resume[1001];
/********************************************************\
* Call CLOB_EXTRACT stored procedure *
\********************************************************/
strcpy(procname, "CLOB_EXTRACT");
strcpy(in_employee, "000140");
printf("\nCALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL CLOB_EXTRACT ( ?,?,? )");
printf(" Allocate a statement handle.\n");
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, in_employee,
7, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_VARCHAR,1000,
0, out_resume,
1001, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 3,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
/* Ignore Warnings */
if (rc != SQL_SUCCESS & rc != SQL_SUCCESS_WITH_INFO)
STMT_HANDLE_CHECK( hstmt, rc);
/* Check that the stored procedure executed successfully */
if (out_sqlcode == 0)
{
printf("Stored procedure returned successfully.\n");
/**********************************************************\
* Display the section of the resume returned from the CLOB *
\**********************************************************/
printf("Resume section returned from CLOB_EXTRACT = \n%s\n", out_resume);
}
else
{ /* print the error message and roll back the transaction */
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
printf("from procedure section labelled \"%s\".\n", out_buffer);
strcpy((char*)stmt,"ROLLBACK");
rc = SQLExecDirect(hstmt,stmt,SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
}
rc = SQLCloseCursor(hstmt);
return(rc);
}
int alltypes(SQLHANDLE hstmt)
{
short inout_smallint;
SQLINTEGER inout_integer;
sqlint64 inout_bigint;
float inout_real;
double inout_double;
char out_char[2];
char out_chars[16];
char out_varchar[13];
char out_date[11];
char out_time[9];
/********************************************************\
* Call ALL_DATA_TYPES stored procedure *
\********************************************************/
strcpy(procname, "ALL_DATA_TYPES");
inout_smallint = 32000;
inout_integer = 2147483000;
inout_bigint = 2147480000;
/* Maximum value of BIGINT is 9223372036854775807 */
inout_real = 100000;
inout_double = 2500000;
printf("\nCALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL ALL_DATA_TYPES ( ?,?,?,?,?,?,?,?,?,?,?,? )");
printf(" Allocate a statement handle.\n");
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT_OUTPUT, SQL_C_SHORT,
SQL_SMALLINT,0,
0, &inout_smallint,
0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_INPUT_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &inout_integer,
0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 3,
SQL_PARAM_INPUT_OUTPUT, SQL_C_SBIGINT,
SQL_BIGINT,0,
0, &inout_bigint,
0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 4,
SQL_PARAM_INPUT_OUTPUT, SQL_C_FLOAT,
SQL_REAL,0,
0, &inout_real,
0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 5,
SQL_PARAM_INPUT_OUTPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &inout_double,
0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 6,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,1,
0, out_char,
2, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 7,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,15,
0, out_chars,
16, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 8,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_VARCHAR,12,
0, out_varchar,
13, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 9,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_TYPE_DATE,10,
0, out_date,
11, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 10,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_TYPE_TIME,8,
0, out_time,
9, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 11,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 12,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,32,
0, out_buffer,
33, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
/* Ignore Warnings */
if (rc != SQL_SUCCESS & rc != SQL_SUCCESS_WITH_INFO)
STMT_HANDLE_CHECK( hstmt, rc);
/* Check that the stored procedure executed successfully */
if (out_sqlcode == 0)
{
printf("Stored procedure returned successfully.\n");
/********************************************************\
* Display the sum salaries for the affected department *
\********************************************************/
printf("Value of SMALLINT = %d\n", inout_smallint);
printf("Value of INTEGER = %ld\n", inout_integer);
printf("Value of BIGINT = %lld\n", inout_bigint);
printf("Value of REAL = %.2f\n", inout_real);
printf("Value of DOUBLE = %.2lf\n", inout_double);
printf("Value of CHAR(1) = %s\n", out_char);
printf("Value of CHAR(15) = %s\n", out_chars);
printf("Value of VARCHAR(12) = %s\n", out_varchar);
printf("Value of DATE = %s\n", out_date);
printf("Value of TIME = %s\n", out_time);
}
else
{ /* print the error message and roll back the transaction */
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
printf("from procedure section labelled \"%s\".\n", out_buffer);
strcpy((char*)stmt,"ROLLBACK");
rc = SQLExecDirect(hstmt,stmt,SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
}
rc = SQLCloseCursor(hstmt);
return(rc);
}
int callinpara( SQLHANDLE hstmt)
{
double sum_salary;
/* Declare variables for passing data to IN_PARAMS */
double in_lowsal, in_medsal, in_highsal;
char in_dept[4];
/********************************************************\
* Call IN_PARAMS stored procedure *
\********************************************************/
in_lowsal = 15000;
in_medsal = 20000;
in_highsal = 25000;
strcpy(in_dept, "E11");
strcpy(procname, "IN_PARAMS");
printf("\nCALL stored procedure: %s\n", procname);
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt1 ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
/* select values from table employee */
strcpy((char *)stmt,"SELECT SUM(salary) FROM employee WHERE workdept = '");
strcat((char *)stmt, in_dept);
strcat((char *)stmt, "'");
sqlrc = SQLExecDirect( hstmt1, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt1, 1, SQL_C_DOUBLE, &sum_salary, 0, NULL);
STMT_HANDLE_CHECK( hstmt1, sqlrc);
/* fetch each row, and display */
sqlrc = SQLFetch( hstmt1 );
STMT_HANDLE_CHECK( hstmt1, sqlrc);
printf("\nSum of salaries for dept. %s = %8.2f before calling procedure %s\n",
in_dept, sum_salary, procname);
strcpy((char*)stmt,"CALL IN_PARAMS ( ?,?,?,?,?,?)");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &in_lowsal,
0, NULL);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &in_medsal,
0, NULL);
rc = SQLBindParameter(hstmt, 3,
SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &in_highsal,
0, NULL);
rc = SQLBindParameter(hstmt, 4,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, in_dept,
4, NULL);
rc = SQLBindParameter(hstmt, 5,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, NULL);
rc = SQLBindParameter(hstmt, 6,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, out_buffer,
33, NULL);
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = SQLExecute(hstmt);
/* Ignore Warnings */
if (rc != SQL_SUCCESS & rc != SQL_SUCCESS_WITH_INFO)
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
/* Check that the stored procedure executed successfully */
if ( rc == SQL_SUCCESS)
{
printf("Stored procedure returned successfully.\n");
/********************************************************\
* Display the sum salaries for the affected department *
\********************************************************/
/* select values from table employee */
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt2 ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
strcpy((char *)stmt,"SELECT SUM(salary) FROM employee WHERE workdept = '");
strcat((char *)stmt, in_dept);
strcat((char *)stmt, "'");
rc = SQLExecDirect( hstmt2, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt2, rc);
sum_salary=0;
/* bind columns to variables */
rc = SQLBindCol( hstmt2, 1, SQL_C_DOUBLE, &sum_salary, 0, NULL);
STMT_HANDLE_CHECK( hstmt2, rc);
/* fetch each row, and display */
rc = SQLFetch( hstmt2 );
STMT_HANDLE_CHECK( hstmt2, rc);
printf("\nSum of salaries for dept. %s = %8.2f after calling procedure %s\n",
in_dept, sum_salary, procname);
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt2 ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
}
else
{ /* print the error message, roll back the changes */
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
printf("from procedure section labelled \"%s\".\n", out_buffer);
strcpy((char*)stmt,"ROLLBACK");
rc = SQLExecDirect(hstmt,stmt,SQL_NTS);
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
}
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt1 ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
return (rc);
}
int calloutpara(SQLHANDLE hstmt)
{
/********************************************************\
* Call OUT_PARAM stored procedure *
\********************************************************/
strcpy(procname, "OUT_PARAM");
printf("\nCALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL OUT_PARAM ( ?,?,? )");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_OUTPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &out_median,
0, NULL);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, NULL);
rc = SQLBindParameter(hstmt, 3,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, out_buffer,
33, NULL);
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = SQLExecute(hstmt);
/* Ignore Warnings */
if (rc != SQL_SUCCESS & rc != SQL_SUCCESS_WITH_INFO)
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
/* Check that the stored procedure executed successfully */
if (out_sqlcode == 0)
{
printf("Stored procedure returned successfully.\n");
/***********************************************************\
* Display the median salary returned as an output parameter *
\***********************************************************/
printf("Median salary returned from OUT_PARAM = %8.2f\n", out_median);
}
else
{ /* print the error message, roll back the transaction */
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
printf("from procedure section labelled \"%s\".\n", out_buffer);
strcpy((char*)stmt,"ROLLBACK");
rc = SQLExecDirect(hstmt,stmt,SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
}
return (rc);
}
int callinout(SQLHANDLE hstmt, double inout_median)
{
/********************************************************\
* Call INOUT_PARAM stored procedure *
\********************************************************/
strcpy(procname, "INOUT_PARAM");
strcpy((char*)stmt,"CALL INOUT_PARAM ( ?,?,? )");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT_OUTPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &inout_median,
0, NULL);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, NULL);
rc = SQLBindParameter(hstmt, 3,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, out_buffer,
33, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
/* Ignore Warnings */
if (rc != SQL_SUCCESS & rc != SQL_SUCCESS_WITH_INFO)
STMT_HANDLE_CHECK( hstmt, rc);
/* Check that the stored procedure executed successfully */
if (out_sqlcode == 0)
{
printf("Stored procedure returned successfully.\n");
printf("Median salary returned from INOUT_PARAM = %8.2f\n", inout_median);
}
else
{ /* print the error message, roll back the transaction */
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
printf("from procedure section labelled \"%s\".\n", out_buffer);
strcpy((char*)stmt,"ROLLBACK");
rc = SQLExecDirect(hstmt,stmt,SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
}
return (rc);
}
int calloutlang(SQLHANDLE hstmt)
{
/********************************************************\
* Call OUT_LANGUAGE stored procedure *
\********************************************************/
strcpy(procname, "OUT_LANGUAGE");
printf("CALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL OUT_LANGUAGE ( ? )");
/* Prepare the call statement */
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,0,
0,out_lang,
9, NULL);
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = SQLExecute(hstmt);
/* Ignore Warnings */
if (rc != SQL_SUCCESS & rc != SQL_SUCCESS_WITH_INFO)
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
if (rc == SQL_SUCCESS)
printf("Stored procedure returned successfully.\n");
/********************************************************\
* Display the language of the stored procedures *
\********************************************************/
printf("Stored procedures are implemented in LANGUAGE: %s\n", out_lang);
return (rc);
}
int dbinfoexample(hstmt)
SQLHANDLE hstmt; /* statement handle */
{
/********************************************************\
* Call DBINFO_EXAMPLE stored procedure *
\********************************************************/
char injob[9];
double out_salary;
char out_dbname[129]; /* Name of database from DBINFO structure */
char out_dbversion[9]; /* Version of database from DBINFO structure */
strcpy(procname, "DBINFO_EXAMPLE");
printf("\nCALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL DBINFO_EXAMPLE ( ?,?,?,?,? )");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
strcpy(injob, "MANAGER");
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, injob,
9, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &out_salary,
0, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 3,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, out_dbname,
129, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 4,
SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, out_dbversion,
9, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 5,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
STMT_HANDLE_CHECK( hstmt, rc);
/* Check that the stored procedure executed successfully */
if (rc == SQL_SUCCESS) {
printf("Stored procedure returned successfully with SQLCODE = %d.\n", out_sqlcode);
printf("Average salary for job %s = %9.2lf\n", injob, out_salary);
printf("Database name from DBINFO structure = %s\n", out_dbname);
printf("Database version from DBINFO structure = %s\n", out_dbversion);
}
else {
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
}
return(rc);
}
int mainexample(hstmt)
SQLHANDLE hstmt; /* statement handle */
{
/********************************************************\
* Call MAIN_EXAMPLE stored procedure *
\********************************************************/
char injob[9];
double out_salary;
strcpy(procname, "MAIN_EXAMPLE");
printf("\nCALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL MAIN_EXAMPLE ( ?,?,?)");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
strcpy(injob, "DESIGNER");
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, injob,
9, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &out_salary,
0, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 3,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
STMT_HANDLE_CHECK( hstmt, rc);
/* Check that the stored procedure executed successfully */
if (rc == SQL_SUCCESS) {
printf("Stored procedure returned successfully.\n");
printf("Average salary for job %s = %9.2lf\n", injob, out_salary);
}
else {
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
}
return(rc);
}
int db2sqlexample(hstmt, job_name)
SQLHANDLE hstmt; /* statement handle */
char job_name[9];
{
char injob[9];
sqlint16 in_jobind;
double out_salary = 0;
sqlint16 out_salaryind;
/********************************************************\
* Call DB2SQL_EXAMPLE stored procedure *
\********************************************************/
strcpy(procname, "DB2SQL_EXAMPLE");
printf("\nCALL stored procedure named %s\n", procname);
/* out_salary is an OUT parameter, so set the
null indicator to -1 to indicate no input value */
out_salaryind = -1;
strcpy(injob, job_name);
if (strlen(injob) == 0)
{
/* injob is null, so set the null indicator
to -1 to indicate there is no input value */
in_jobind = -1;
printf("with NULL input, to return a custom SQLSTATE and diagnostic message.\n");
}
else
{
/* injob is not null, so set the null indicator
to 0 to indicate there is an input value */
in_jobind = 0;
}
strcpy((char*)stmt,"CALL DB2SQL_EXAMPLE ( ?, ?)");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,0,
0, injob,
9, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &out_salary,
0, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
STMT_HANDLE_CHECK( hstmt, rc);
/* Call SQLGetDiagRec() to check the SQLSTATE;
if SQLSTATE = '00000' then stored procedure returned successfully;
if SQLSTATE = '38400' (custom SQLSTATE set in stored procedure),
then display the custom SQLSTATE and diagnostic message. */
/* Check that the stored procedure executed successfully */
if (rc == SQL_SUCCESS) {
printf("Stored procedure returned successfully.\n");
printf("Average salary for job %s = %9.2lf\n", injob, out_salary);
}
else {
printf("Stored procedure returned RC = %d\n", rc);
}
return(rc);
}