/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: spclient.sqc
**
** SAMPLE: Call the set of stored procedues implemented in spserver.sqc
**
** This file contains eight functions that call stored procedures:
**
** (1) callOutLanguage: Calls a stored procedure that returns the
** implementation language of the stored procedure library
** Parameter types used: OUT CHAR(8)
** (2) callOutParameter: Calls a stored procedure that returns median
** salary of employee salaries
** Parameter types used: OUT DOUBLE
** (3) callInParameters: Calls a stored procedure that accepts 3 salary
** values and updates employee salaries in the EMPLOYEE table based
** on these values for a given department.
** Parameter types used: IN DOUBLE
** IN DOUBLE
** IN DOUBLE
** IN CHAR(3)
** (4) callInoutParameter: Calls a stored procedure that accepts an input
** value and returns the median salary of those employees in the
** EMPLOYEE table who earn more than the input value. Demonstrates how
** to use null indicators in a client application. The stored procedure
** has to be implemented in the following parameter styles for it to be
** compatible with this client application.
** Parameter style for a C stored procedure: SQL
** Parameter style for a Java(JDBC/SQLJ) stored procedure: JAVA
** Parameter style for an SQL stored procedure: SQL
** Parameter types used: INOUT DOUBLE
** (5) callClobExtract: Calls a stored procedure that extracts and returns a
** portion of a CLOB data type
** Parameter types used: IN CHAR(6)
** OUT VARCHAR(1000)
** (6) callDBINFO: Calls a stored procedure that receives a DBINFO
** structure and returns elements of the structure to the client
** Parameter types used: IN CHAR(8)
** OUT DOUBLE
** OUT CHAR(128)
** OUT CHAR(8)
** (7) callProgramTypeMain: Calls a stored procedure implemented with
** PROGRAM TYPE MAIN parameter style
** Parameter types used: IN CHAR(8)
** OUT DOUBLE
** (8) callAllDataTypes: Calls a stored procedure that uses a variety of
** common data types (not DECIMAL, GRAPHIC, VARGRAPHIC, BLOB, CLOB, DBCLOB).
** This sample shows only a subset of DB2 supported data types. For a
** full listing of DB2 data types, please see the SQL Reference.
** Parameter types used: INOUT SMALLINT
** INOUT INTEGER
** INOUT BIGINT
** INOUT REAL
** INOUT DOUBLE
** OUT CHAR(1)
** OUT CHAR(15)
** OUT VARCHAR(12)
** OUT DATE
** OUT TIME
**
** The file "utilemb.sqc" contains functions for error-checking and
** rolling back a transaction in case of error. This file must be
** compiled and its object file linked to the "spclient" program.
**
** SQL STATEMENTS USED:
** CALL
** CONNECT
** ROLLBACK
** SELECT
**
** STRUCTURES USED:
** sqlca
** sqlda
**
** EXTERNAL DEPENDENCIES:
** For successful precompilation, the sample database must exist
** (see DB2's db2sampl command).
** The stored procedures called from this program must have been built
** and cataloged in the database (see the instructions in spserver.sqc).
** This program must have been precompiled (see DB2's PREPARE command).
** The packages associated with this program must be bound to the
** database (see DB2's BIND command).
** This program must have been compiled and linked with the supported
** compiler and linker for the current environment.
**
** spclient spclient
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running DB2
** applications, visit the DB2 Information Center at
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <string.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <sqlca.h>
#include <stdio.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int main(int argc, char *argv[])
{
int rc = 0;
double medSalary;
char dbAlias[9];
char user[128 + 1];
char pswd[19];
/* check the command line arguments */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
printf("HOW TO CALL VARIOUS STORED PROCEDURES.\n");
/* connect to database */
rc = DbConn(dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
/* call the store procedures */
callStorProcs();
return (0);
} /* main */
int callOutLanguage(char *language)
{
/********************************************************\
* Call OUT_LANGUAGE stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
char outOutLangLanguage[9]; /* host variable naming convention */
/* <param_mode><stored_proc_name><var_name>*/
sqlint16 outOutLangLanguageInd;
EXEC SQL END DECLARE SECTION;
printf("\nCALL stored procedure named OUT_LANGUAGE \n");
outOutLangLanguageInd = -1;
EXEC SQL CALL OUT_LANGUAGE(:outOutLangLanguage:outOutLangLanguageInd);
EMB_SQL_CHECK("CALL OUT_LANGUAGE");
if (outOutLangLanguageInd == 0)
{
/********************************************************\
* Display the language of the stored procedures *
\********************************************************/
strcpy(language, outOutLangLanguage);
printf("Stored procedures are implemented in LANGUAGE %s\n", language);
}
return (0);
} /* callOutLanguage */
double callOutParameter()
{
/********************************************************\
* Call OUT_PARAM stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
double outOutParamMedian;
sqlint16 outOutParamMedianInd;
EXEC SQL END DECLARE SECTION;
printf("\nCALL stored procedure named OUT_PARAM \n");
/* initialize variables */
outOutParamMedian = -1;
outOutParamMedianInd = -1;
/* OUT_PARAM is of parameter style SQL, so pass a null indicator */
EXEC SQL CALL OUT_PARAM(:outOutParamMedian:outOutParamMedianInd);
EMB_SQL_CHECK("CALL OUT_PARAM");
if (outOutParamMedianInd == 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",
outOutParamMedian);
}
return outOutParamMedian;
} /* callOutParameter */
int callInParameters(double lowsal,
double medsal,
double highsal,
char *dept)
{
/********************************************************\
* Call IN_PARAMS stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* declare host variables for passing data to IN_PARAMS */
double inInParamLowSal;
double inInParamMedSal;
double inInParamHighSal;
char inInParamDept[4];
/* declare NULL indicators for each parameter */
sqlint16 inInParamLowSalInd;
sqlint16 inInParamMedSalInd;
sqlint16 inInParamHighSalInd;
sqlint16 inInParamDeptInd;
/* declare host variables for showing results of IN_PARAMS */
double hvInParamDeptSumSalary;
EXEC SQL END DECLARE SECTION;
/* set the input parameters of the stored procedure */
strcpy(inInParamDept, dept);
inInParamLowSal = lowsal;
inInParamMedSal = medsal;
inInParamHighSal = highsal;
inInParamDeptInd = 0;
inInParamLowSalInd = 0;
inInParamMedSalInd = 0;
inInParamHighSalInd = 0;
EXEC SQL SELECT SUM(salary) INTO :hvInParamDeptSumSalary
FROM employee
WHERE workdept = :inInParamDept;
EMB_SQL_CHECK("SUM BEFORE");
printf("\nSum of salaries for dept. %s = %8.2f before calling IN_PARAMS\n",
inInParamDept, hvInParamDeptSumSalary);
printf("CALL stored procedure named IN_PARAMS\n");
/* IN_PARAMS is of parameter style SQL, so pass null indicators */
EXEC SQL CALL IN_PARAMS(:inInParamLowSal:inInParamLowSalInd,
:inInParamMedSal:inInParamMedSalInd,
:inInParamHighSal:inInParamHighSalInd,
:inInParamDept:inInParamDeptInd);
EMB_SQL_CHECK("CALL IN_PARAMS");
/* check that the stored procedure executed successfully */
if (sqlca.sqlcode == 0)
{
printf("Stored procedure returned successfully\n");
/********************************************************\
* Display the sum salaries for the affected department *
\********************************************************/
EXEC SQL SELECT SUM(salary) INTO :hvInParamDeptSumSalary
FROM employee
WHERE workdept =:inInParamDept;
EMB_SQL_CHECK("SUM AFTER");
printf("Sum of salaries for dept. %s = %8.2f after calling IN_PARAMS\n",
inInParamDept, hvInParamDeptSumSalary);
}
return (0);
} /* callInParameters */
int callInoutParameter(double median)
{
/********************************************************\
* Call INOUT_PARAM stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
double inoutInOutParamMedian;
sqlint16 inoutInOutParamMedianInd;
EXEC SQL END DECLARE SECTION;
if (median < 0)
{
/* salary was negative, indicating a probable error,
so pass a null value to the stored procedure instead
by setting inoutInOutParamMedianInd to a negative value */
inoutInOutParamMedianInd = -1;
}
else
{
/* salary was positive, so pass the value of median
to the stored procedure by setting inoutInOutParamMedianInd to 0 */
inoutInOutParamMedian = median;
inoutInOutParamMedianInd = 0;
}
/* INOUT_PARAM is of parameter style SQL, so pass null indicators */
EXEC SQL CALL INOUT_PARAM(:inoutInOutParamMedian:inoutInOutParamMedianInd);
EMB_SQL_CHECK("CALL INOUT_PARAM");
/* check that the stored procedure executed successfully */
if (sqlca.sqlcode == 0 && inoutInOutParamMedianInd == 0)
{
printf("Stored procedure returned successfully\n");
printf("Median salary returned from INOUT_PARAM = %8.2f\n",
inoutInOutParamMedian);
}
return (0);
} /* callInoutParameter */
int callClobExtract(char *empno)
{
/********************************************************\
* Call CLOB_EXTRACT stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* declare host variables for passing data to CLOB_EXTRACT */
char inExtFromClobEmpNum[7];
char outExtFromClobResume[1001];
/* declare NULL indicators for each parameter */
sqlint16 inExtFromClobEmpNumInd;
sqlint16 outExtFromClobResumeInd;
EXEC SQL END DECLARE SECTION;
if (empno == NULL)
{
inExtFromClobEmpNumInd = -1;
}
else
{
inExtFromClobEmpNumInd = 0;
strcpy(inExtFromClobEmpNum, empno);
}
printf("\nCALL stored procedure named CLOB_EXTRACT \n");
/* CLOB_EXTRACT is of parameter style SQL, so pass null indicators */
EXEC SQL CALL CLOB_EXTRACT(:inExtFromClobEmpNum:inExtFromClobEmpNumInd,
:outExtFromClobResume:outExtFromClobResumeInd);
EMB_SQL_CHECK("CALL CLOB_EXTRACT");
/* check that the stored procedure executed successfully */
if (sqlca.sqlcode == 0 && outExtFromClobResumeInd == 0)
{
printf("Stored procedure returned successfully\n");
printf("Resume section returned from CLOB_EXTRACT = \n%s\n",
outExtFromClobResume);
}
else
{
/* print the error message */
printf("Stored procedure returned SQLCODE %d\n", sqlca.sqlcode);
}
return (0);
} /* callClobExtract */
int callDBINFO(char *job)
{
/********************************************************\
* Call DBINFO_EXAMPLE stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
char inDbinfoJob[9];
double outDbinfoSalary;
char outDbinfoDbName[129]; /* name of database from DBINFO structure */
char outDbinfoDbVersion[9]; /* version of database from DBINFO struct */
/* declare NULL indicators for each parameter */
sqlint16 inDbinfoJobInd;
sqlint16 outDbinfoSalaryInd;
sqlint16 outDbinfoDbNameInd;
sqlint16 outDbinfoDbVersionInd;
EXEC SQL END DECLARE SECTION;
int i;
strcpy(inDbinfoJob, job);
inDbinfoJobInd = 0;
printf("\nCALL stored procedure named DBINFO_EXAMPLE \n");
/* DBINFO_EXAMPLE is of parameter style SQL, so pass null indicators */
EXEC SQL CALL DBINFO_EXAMPLE(:inDbinfoJob:inDbinfoJobInd,
:outDbinfoSalary:outDbinfoSalaryInd,
:outDbinfoDbName:outDbinfoDbNameInd,
:outDbinfoDbVersion:outDbinfoDbVersionInd);
EMB_SQL_CHECK("CALL DBINFO_EXAMPLE");
if (sqlca.sqlcode == 0 && outDbinfoSalaryInd == 0)
{
printf("Stored procedure returned successfully\n");
printf("Average salary for job %s = %9.2lf\n", inDbinfoJob,
outDbinfoSalary);
}
if (outDbinfoDbNameInd == 0)
{
printf("Database name from DBINFO structure = ");
for (i = 0; outDbinfoDbName[i] != ' '; i++)
printf("%c", outDbinfoDbName[i]);
}
if (outDbinfoDbVersionInd == 0)
{
printf("\nDatabase version from DBINFO structure = %s\n",
outDbinfoDbVersion);
}
return (0);
} /* callDBINFO */
int callProgramTypeMain(char *job)
{
/********************************************************\
* Call MAIN_EXAMPLE stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
char inMainJob[9];
double outMainSalary;
/* declare NULL indicators for each parameter */
sqlint16 inMainJobInd;
sqlint16 outMainSalaryInd;
EXEC SQL END DECLARE SECTION;
if (job == NULL)
{
inMainJobInd = -1;
}
else
{
strcpy(inMainJob, job);
inMainJobInd = 0;
}
printf("\nCALL stored procedure named MAIN_EXAMPLE \n");
/* MAIN_EXAMPLE is of parameter style SQL, so pass null indicators */
EXEC SQL CALL MAIN_EXAMPLE(:inMainJob:inMainJobInd,
:outMainSalary:outMainSalaryInd);
EMB_SQL_CHECK("CALL MAIN_EXAMPLE");
if (sqlca.sqlcode == 0)
{
printf("Stored procedure returned successfully\n");
printf("Average salary for job %s = %9.2lf\n", inMainJob,
outMainSalary);
}
return (0);
} /* callProgramTypeMain */
int callAllDataTypes()
{
/********************************************************\
* Call ALL_DATA_TYPES stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* declare host variables for passing data to ALL_DATA_TYPES */
sqlint16 inoutAllDataTypesSmallInt;
sqlint32 inoutAllDataTypesInteger;
sqlint64 inoutAllDataTypesBigInt;
float inoutAllDataTypesReal;
double inoutAllDataTypesDouble;
char outAllDataTypesChar[2];
char outAllDataTypesChars[16];
char outAllDataTypesVarchar[13];
char outAllDataTypesDate[11];
char outAllDataTypesTime[9];
EXEC SQL END DECLARE SECTION;
inoutAllDataTypesSmallInt = 32000;
inoutAllDataTypesInteger = 2147483000;
inoutAllDataTypesBigInt = 2147480000;
/* maximum value of BIGINT is 9223372036854775807
but some platforms only support 32-bit integers */
inoutAllDataTypesReal = 100000;
inoutAllDataTypesDouble = 2500000;
printf("\nCALL stored procedure named ALL_DATA_TYPES \n");
/* ALL_DATA_TYPES is of parameter style SQL */
EXEC SQL CALL ALL_DATA_TYPES(:inoutAllDataTypesSmallInt,
:inoutAllDataTypesInteger,
:inoutAllDataTypesBigInt,
:inoutAllDataTypesReal,
:inoutAllDataTypesDouble,
:outAllDataTypesChar,
:outAllDataTypesChars,
:outAllDataTypesVarchar,
:outAllDataTypesDate,
:outAllDataTypesTime);
EMB_SQL_CHECK("CALL ALL_DATA_TYPES");
/* check that the stored procedure executed successfully */
if (sqlca.sqlcode == 0)
{
printf("Stored procedure returned successfully\n");
/********************************************************\
* Display the sum salaries for the affected department *
\********************************************************/
printf("Value of SMALLINT = %d\n", inoutAllDataTypesSmallInt);
printf("Value of INTEGER = %ld\n", inoutAllDataTypesInteger);
printf("Value of BIGINT = %lld\n", inoutAllDataTypesBigInt);
printf("Value of REAL = %.2f\n", inoutAllDataTypesReal);
printf("Value of DOUBLE = %.2lf\n", inoutAllDataTypesDouble);
printf("Value of CHAR(1) = %s\n", outAllDataTypesChar);
printf("Value of CHAR(15) = %s\n", outAllDataTypesChars);
printf("Value of VARCHAR(12) = %s\n", outAllDataTypesVarchar);
printf("Value of DATE = %s\n", outAllDataTypesDate);
printf("Value of TIME = %s\n", outAllDataTypesTime);
}
else
{
/* print the error message */
printf("Stored procedure returned SQLCODE %d\n", sqlca.sqlcode);
}
return (0);
} /* callAllDataTypes */
int callStorProcs()
{
/* declare variables */
char outLang[9];
int testLangC;
int testLangJava;
int testLangSQL;
double median;
callOutLanguage(outLang);
/* we assume that all the remaining stored procedures are also written in
the same language as outLang and set the following variables accordingly.
This would help us in invoking only those stored procedures that are
supported in that particular language. */
testLangSQL = strncmp(outLang, "SQL", 3);
testLangC = strncmp(outLang, "C", 1);
testLangJava = strncmp(outLang, "JAVA", 4);
median = callOutParameter();
callInParameters(15000, 20000, 25000, "E11");
/* call INOUT_PARAM stored procedure */
printf("\nCALL stored procedure named INOUT_PARAM\n");
printf("using the median returned by the call to OUT_PARAM\n");
callInoutParameter(median);
/* call INOUT_PARAM stored procedure two more times to
intentionally show 2 different errors. The first error is
a user-defined error that is returned when an invalid parameter
value (value less than 0 not valid in the logic context) is passed
to the procedure. The second error shown is the result of a
NOT FOUND error that is raised when no rows are found to satisfy
a query in the procedure. No row is found because the query
depends on the procedure's input parameter value which is too high. */
printf("\nCALL stored procedure INOUT_PARAM again\n");
printf("using a NULL input value\n");
printf("\n-- The following error report is expected! --\n");
callInoutParameter(-99999);
printf("\nCALL stored procedure INOUT_PARAM again \n");
printf("using a value that returns a NOT FOUND error from the ");
printf("stored procedure\n");
printf("\n-- The following error report is expected! --\n");
callInoutParameter(99999.99);
if (testLangC == 0)
{
/* warn the user that the CLI stored procedure
requires a change to the UDF_MEM_SZ variable */
printf("\n If the CLOBEXTRACT stored procedure is implemented\n");
printf(" using CLI, you must increase the value of the UDF_MEM_SZ\n");
printf(" database manager configuration variable to at least two\n");
printf(" pages larger than the size of the input arguments and\n");
printf(" the result of the stored procedure. To do this, issue\n");
printf(" the following command from the CLP:\n");
printf(" db2 UPDATE DBM CFG USING UDF_MEM_SZ 2048\n");
printf(" For the change to take effect, you must then stop and\n");
printf(" restart the DB2 server by issuing the following\n");
printf(" commands from the CLP:\n");
printf(" db2stop\n");
printf(" db2start\n");
}
callClobExtract("000140");
if (testLangC != 0)
{
/* stored procedures of PROGRAM TYPE MAIN or those containing
the DBINFO clause can only be implemented with
LANGUAGE C stored procedures. If outLang != "C",
since there is no corresponding sample, we do nothing. */
}
else
{
callDBINFO("MANAGER");
callProgramTypeMain("DESIGNER");
}
/**************************************************************************
* Embedded C/C++ applications do not provide direct support for the DECIMAL
* data type.
* The following programming languages can be used to directly manipulate
* the DECIMAL type:
* - JDBC
* - SQLJ
* - SQL routines
* - .NET common language runtime languages (C#, Visual Basic)
* Please see the SpClient implementation for one of the above languages
* to see this functionality.
***************************************************************************/
callAllDataTypes();
/**************************************************************************
* Embedded C/C++ client applications do not support receiving result sets
* from stored procedures.
* The following programming languages can be used to recieve result sets
* from stored procedures:
* - CLI
* - JDBC
* - SQLJ
* - SQL routines
* - .NET common language runtime languages (C#, Visual Basic)
* Please see the SpClient implementation for one of the above languages
* to see this functionality.
***************************************************************************/
/* rollback any changes to the database made by this sample */
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("ROLLBACK");
printf("\nStored procedure rolled back\n");
/* disconnect from Remote Database */
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return (0);
} /* callStorProcs */