/******************************************************************************
**
** Source File Name = spclient.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 sample program demonstrates stored procedures.
**
** 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 in to the "spclient" program.
**
** 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 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.
**
** spclient contains twelve functions that call stored procedures:
** (1) callOutLanguage: returns language of the stored procedure library
** Parameter types used: OUT CHAR(8)
** (2) callOutParameter: return median salary of EMPLOYEE table
** Parameter types used: OUT DOUBLE
** OUT INTEGER
** OUT CHAR(32)
** (3) callInParameters: accept 3 values and update salaries in EMPLOYEE table
** accordingly
** Parameter types used: IN DOUBLE
** IN CHAR(3)
** OUT INTEGER
** OUT CHAR(32)
** (4) callInoutParameter: accept an input value and return the median
** salary of the EMPLOYEE table for employees who make more
** than the input value. Demonstrates how to use null indicators
** in a client application.
** Parameter style (C): GENERAL WITH NULLS
** Parameter style (Java): JAVA
** Parameter style (SQL): SQL
** Parameter types used: INOUT DOUBLE
** OUT INTEGER
** OUT CHAR(32)
** (5) callOneResultSet: return a result set to the client application
** Parameter types used: IN DOUBLE
** OUT INTEGER
** (6) callTwoResultSets: return two result sets to the client application
** Parameter types used: IN DOUBLE
** OUT INTEGER
** (7) callClobExtract: return a sub-section of a CLOB data type
** Parameter types used: IN CHAR(6)
** OUT VARCHAR(1000)
** OUT INTEGER
** (8) callDecimalType: pass and receive a DECIMAL data type from a
** stored procedure
** Parameter types used: INOUT DECIMAL
** (9) callAllDataTypes: use all of the common data types in a stored procedure
** 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
** (10) callDB2SQL: calls a DB2SQL parameter style stored procedure
** Parameter style: DB2SQL
** Parameter types used: IN CHAR(8)
** OUT DOUBLE
** (11) 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)
** OUT INTEGER
** (12) callProgramTypeMain: calls a stored procedure implemented with
** PROGRAM TYPE MAIN parameter passing style
** Parameter types used: IN CHAR(8)
** OUT DOUBLE
*******************************************************************************/
#include <stdio.h> /* :rk.1:erk. */
#include <stdlib.h>
#include <sql.h>
#include <sqlda.h>
#include <sqlca.h>
#include <string.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
/* Declare constants */
const int NO_CLIENT_SUPPORT = 1;
/* Declare global variables */
double median;
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variable for stored procedure name */
char procname[254];
/* Declare host variables for stored procedure error handling */
sqlint32 out_sqlcode; /* :rk.4:erk. */
char out_buffer[33];
/* Declare host variable for language of stored procedure library */
char out_lang[9];
EXEC SQL END DECLARE SECTION;
int main(int argc, char *argv[]) {
int test_lang_c, test_lang_java, test_lang_sql;
EXEC SQL BEGIN DECLARE SECTION;
char database[9];
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
if (argc == 1)
{
EXEC SQL CONNECT TO sample; /* :rk.2:erk. */
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;
EMB_SQL_CHECK("CONNECT TO SAMPLE");
}
else if (argc == 4)
{
strcpy (database, argv[1]);
strcpy (userid, argv[2]);
strcpy (passwd, argv[3]);
/* Connect to Remote Database */
printf("CONNECT TO Database\n");
EXEC SQL CONNECT TO :database USER :userid USING :passwd;
EMB_SQL_CHECK("CONNECT TO DATABASE");
}
else
{
printf ("\nUSAGE: spclient [database] [userID password]\n\n");
return 1;
}
callOutLanguage();
test_lang_sql = strncmp(out_lang, "SQL", 3);
test_lang_c = strncmp(out_lang, "C", 1);
test_lang_java = strncmp(out_lang, "JAVA", 4);
callOutParameter();
callInParameters();
printf("\nCALL stored procedure named INOUT_PARAM\n");
printf("using the median returned by the call to OUT_PARAM\n");
callInoutParameter(median);
/* Cause callInoutParameter to pass a null value to the stored procedure */
printf("\nCALL stored procedure named INOUT_PARAM\n");
printf("using a NULL input value\n");
callInoutParameter(-99999);
/* Cause callInoutParameter to return a NOT FOUND error from the stored procedure */
printf("\nCALL stored procedure named INOUT_PARAM\n");
printf("using a value that returns a NOT FOUND error from the "
"stored procedure\n");
/* The "salary" column of the "staff" table is defined with a
DECIMAL(7,2) data type, so 99999.99 is the maximum value. */
callInoutParameter(99999.99);
if (test_lang_sql == 0)
{
/* SQL procedures cannot handle LOB data types,
so do not call the CLOB_EXTRACT stored procedure */
printf("\nStored procedures are implemented in SQL,\n"
"which cannot handle LOB datatypes\n"
"Skipping the call to CLOB_EXTRACT\n");
}
else if (test_lang_c == 0)
{
/* Warn the user that the CLI stored procedure
* requires a change to the UDF_MEM_SZ variable */
printf("\n\n If the CLOB EXTRACT stored procedure is implemented\n"
" using CLI, you must increase the value of the UDF_MEM_SZ\n"
" database manager configuration variable to at least two\n"
" pages larger than the size of the input arguments and\n"
" the result of the stored procedure. To do this, issue\n"
" the following command from the CLP:\n"
" db2 UPDATE DBM CFG USING UDF_MEM_SZ 2048\n"
" For the change to take effect, you must then stop and\n"
" restart the DB2 server by issuing the following\n"
" commands from the CLP:\n"
" db2stop\n"
" db2start\n");
callClobExtract();
}
else if (test_lang_java == 0)
{
callClobExtract();
}
else if (test_lang_sql == 0)
{
/* SQL procedures do not support CLOB data types,
* so do not call the CLOB_EXTRACT stored procedure.
*/
}
if (NO_CLIENT_SUPPORT)
{
/* Embedded SQL in C client applications cannot
* accept single result sets, so do not call
* the ONE_RESULT_SET stored procedure.
*/
}
if (NO_CLIENT_SUPPORT)
{
/* Embedded SQL in C client applications cannot
* accept multiple result sets, so do not call
* the TWO_RESULT_SETS stored procedure.
*/
}
if (test_lang_c != 0 || NO_CLIENT_SUPPORT)
{
/* C does not provide a native data type for decimal
* values, so neither a C client application nor a
* C stored procedure can pass DECIMAL SQL data types.
*
* Do not call the DECIMAL_TYPE stored procedure,
* because this client is written in C.
*/
}
callAllDataTypes();
if (test_lang_c != 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
{
callDB2SQL("CLERK");
/* Call DB2SQL_EXAMPLE with null input to return
a custom SQLSTATE and diagnostic message */
callDB2SQL("");
callDBINFO();
callProgramTypeMain();
}
/* Roll back stored procedure actions to preserve
the state of the database */
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("ROLLBACK");
printf("\nStored procedure rolled back\n\n");
/* Disconnect from Remote Database */
EXEC SQL CONNECT RESET; /* :rk.8:erk. */
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
int callOutLanguage()
{
/********************************************************\
* Call OUT_LANGUAGE stored procedure *
\********************************************************/
strcpy(procname, "OUT_LANGUAGE");
printf("CALL stored procedure named %s\n", procname);
/* OUT_LANGUAGE is PS GENERAL, so do not pass a null indicator */
EXEC SQL CALL :procname (:out_lang);
EMB_SQL_CHECK("CALL OUT_LANGUAGE");
/********************************************************\
* Display the language of the stored procedures *
\********************************************************/
printf("Stored procedures are implemented in LANGUAGE %s\n", out_lang);
return 0;
}
int callOutParameter()
{
/********************************************************\
* Call OUT_PARAM stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to OUT_PARAM */
double out_median;
EXEC SQL END DECLARE SECTION;
strcpy(procname, "OUT_PARAM");
printf("\nCALL stored procedure named %s\n", procname);
/* OUT_PARAM is PS GENERAL, so do not pass a null indicator */
EXEC SQL CALL :procname (:out_median, :out_sqlcode, :out_buffer); /* :rk.5:erk. */ /* :rk.6:erk. */
EMB_SQL_CHECK("CALL OUT_PARAM");
/* Check that the stored procedure executed successfully */
if (out_sqlcode == 0) /* :rk.7:erk. */
{
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);
}
median = out_median;
return 0;
}
int callInParameters()
{
/********************************************************\
* Call IN_PARAMS stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to IN_PARAMS */
double in_lowsal, in_medsal, in_highsal;
char in_dept[4];
/* Declare host variables for showing results of IN_PARAMS */
double sum_salary;
EXEC SQL END DECLARE SECTION;
in_lowsal = 15000;
in_medsal = 20000;
in_highsal = 25000;
strcpy(in_dept, "E11");
strcpy(procname, "IN_PARAMS");
EXEC SQL SELECT SUM(salary) INTO :sum_salary FROM employee WHERE workdept = :in_dept;
EMB_SQL_CHECK("SUM BEFORE");
printf("\nSum of salaries for dept. %s = %8.2f before %s\n", in_dept, sum_salary, procname);
printf("CALL stored procedure named %s\n", procname);
/* IN_PARAMS is PS GENERAL, so do not pass null indicators */
EXEC SQL CALL :procname (:in_lowsal, :in_medsal, :in_highsal, :in_dept, :out_sqlcode, :out_buffer);
EMB_SQL_CHECK("CALL IN_PARAMS");
/* 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 *
\********************************************************/
EXEC SQL SELECT SUM(salary) INTO :sum_salary FROM employee WHERE workdept = :in_dept;
EMB_SQL_CHECK("SUM AFTER");
printf("Sum of salaries for dept. %s = %8.2f after IN_PARAMS\n", in_dept, sum_salary);
}
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);
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("ROLLBACK");
}
return 0;
}
int callInoutParameter(double out_median)
{
/********************************************************\
* Call INOUT_PARAM stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to INOUT_PARAM */
double inout_median;
sqlint16 medianind, codeind, bufferind;
EXEC SQL END DECLARE SECTION;
strcpy(procname, "INOUT_PARAM");
if (out_median < 0)
{
/* Salary was negative, indicating a probable error,
so pass a null value to the stored procedure instead
by setting medianind to a negative value */
medianind = -1;
codeind = -1;
bufferind = -1;
}
else
{
/* Salary was positive, so pass the value of out_median
to the stored procedure by setting medianind to 0 */
inout_median = out_median;
medianind = 0;
codeind = -1;
bufferind = -1;
}
/* INOUT_PARAM is PS GENERAL WITH NULLS, so pass null indicators */
EXEC SQL CALL :procname (:inout_median:medianind, :out_sqlcode:codeind, :out_buffer:bufferind);
EMB_SQL_CHECK("CALL INOUT_PARAM");
/* Check that the stored procedure executed successfully */
if (out_sqlcode == 0 && medianind == 0)
{
printf("Stored procedure returned successfully\n");
printf("Median salary returned from INOUT_PARAM = %8.2f\n", inout_median);
}
else if (medianind < 0)
{
printf("Stored procedure returned NULL values\n");
}
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);
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("ROLLBACK");
}
return 0;
}
int callOneResultSet()
{
/* Embedded SQL in C client applications cannot
* accept a single result set from a stored procedure,
* so this function does not call ONE_RESULT_SET.
*/
return 0;
}
int callTwoResultSets()
{
/* Embedded SQL in C client applications cannot
* accept multiple result sets from a stored procedure,
* so this function does not call ONE_RESULT_SET.
*/
return 0;
}
int callClobExtract()
{
/********************************************************\
* Call CLOB_EXTRACT stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to CLOB_EXTRACT */
char in_empno[7]; /* CHAR(6) */
char out_resume[1001]; /* VARCHAR(1000) */
EXEC SQL END DECLARE SECTION;
strcpy(in_empno, "000140");
strcpy(procname, "CLOB_EXTRACT");
printf("\nCALL stored procedure named %s\n", procname);
/* CLOB_EXTRACT is PS GENERAL, so do not pass a null indicator */
EXEC SQL CALL :procname (:in_empno, :out_resume, :out_sqlcode);
EMB_SQL_CHECK("CALL CLOB_EXTRACT");
/* Check that the stored procedure executed successfully */
if (out_sqlcode == 0)
{
printf("Stored procedure returned successfully\n");
printf("Resume section returned from CLOB_EXTRACT = \n%s\n", out_resume);
}
else
{ /* print the error message */
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
printf("from procedure section labelled \"%s\"\n", out_buffer);
}
return 0;
}
int callDecimalType()
{
/* The C programming language does not provide native support
* for decimal values, so neither C client applications nor
* C stored procedures can pass DECIMAL parameters.
*
* Consider recoding your C stored procedure to cast the
* DECIMAL value to a DOUBLE, or some other combination of
* SQL data types, and pass that parameter or those parameters
* back instead of a DECIMAL parameter.
*
* This function does not call the DECIMAL_TYPE stored procedure.
*/
return 0;
}
int callAllDataTypes()
{
/********************************************************\
* Call ALL_DATA_TYPES stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to ALL_DATA_TYPES */
sqlint16 inout_smallint; /* SMALLINT */
sqlint32 inout_integer; /* INTEGER */
sqlint64 inout_bigint; /* BIGINT */
float inout_real; /* REAL */
double inout_double; /* DOUBLE */
char out_char; /* CHAR(1) */
char out_chars[16]; /* CHAR(15) */
char out_varchar[13]; /* VARCHAR(12) */
char out_date[11]; /* DATE */
char out_time[9]; /* TIME */
EXEC SQL END DECLARE SECTION;
strcpy(procname, "ALL_DATA_TYPES");
printf("\nCALL stored procedure named %s\n", procname);
inout_smallint = 32000;
inout_integer = 2147483000;
inout_bigint = 2147480000;
/* Maximum value of BIGINT is 9223372036854775807 */
/* but some platforms only support 32-bit integers */
inout_real = 100000;
inout_double = 2500000;
/* ALL_DATA_TYPES is PS GENERAL, so do not pass null indicators */
EXEC SQL CALL :procname (:inout_smallint, :inout_integer, :inout_bigint, :inout_real, :inout_double, :out_char, :out_chars, :out_varchar, :out_date, :out_time, :out_sqlcode, :out_buffer);
EMB_SQL_CHECK("CALL ALL_DATA_TYPES");
/* 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) = %c\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 */
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
printf("from procedure section labelled \"%s\"\n", out_buffer);
}
return 0;
}
int callDB2SQL(char job_name[9])
{
/* Variables used to parse SQLMSG */
char *token = "\xff";
char *sqlmsg[3];
char *message;
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to DB2SQL_EXAMPLE */
char in_job[9];
sqlint16 in_jobind;
double out_salary = 0;
sqlint16 out_salaryind;
EXEC SQL END DECLARE SECTION;
/********************************************************\
* 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(in_job, job_name);
/* in_job is an IN parameter, so check to see
if there is any input value */
if (strlen(in_job) == 0)
{
/* in_job 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");
printf(" and diagnostic message\n");
}
else
{
/* in_job is not null, so set the null indicator
to 0 to indicate there is an input value */
in_jobind = 0;
}
/* DB2SQL_EXAMPLE is PS DB2SQL, so pass
a null indicator for each parameter */
EXEC SQL CALL :procname (:in_job:in_jobind,
:out_salary:out_salaryind);
/* DB2SQL stored procedures can return a custom
SQLSTATE and diagnostic message, so instead of
using the EMB_SQL_CHECK macro to check the value
of the returned SQLCODE, check the SQLCA structure
for the value of the SQLSTATE and the diagnostic message */
/* Check value of returned SQLSTATE */
if (strncmp(sqlca.sqlstate, "00000", 5) == 0) {
printf("Stored procedure returned successfully\n");
printf("Average salary for job %s = %9.2f\n",
in_job, out_salary);
}
else {
printf("Stored procedure failed with SQLSTATE %s\n",
sqlca.sqlstate);
printf("Stored procedure returned the following ");
printf("diagnostic message:\n");
/* Allocate memory for diagnostic string */
message = (char *) malloc(sqlca.sqlerrml);
/* Copy diagnostic message into string for use with strtok */
strncpy((char *)message, (char *)sqlca.sqlerrmc, sqlca.sqlerrml);
/* First token is the procedure name */
sqlmsg[0] = strtok(message, token);
/* Second token is the procedure entry point */
sqlmsg[1] = strtok(NULL, token);
/* Third token is the diagnostic message */
sqlmsg[2] = strtok(NULL, token);
printf(" \"%s\"\n", sqlmsg[2]);
}
return 0;
}
int callDBINFO()
{
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to DBINFO_EXAMPLE */
char dbinfo_in_job[9];
double dbinfo_out_salary;
char out_dbname[129]; /* Name of database from DBINFO structure */
char out_dbversion[9]; /* Version of database from DBINFO structure */
EXEC SQL END DECLARE SECTION;
/********************************************************\
* Call DBINFO_EXAMPLE stored procedure *
\********************************************************/
strcpy(dbinfo_in_job, "MANAGER");
strcpy(procname, "DBINFO_EXAMPLE");
printf("\nCALL stored procedure named %s\n", procname);
/* DBINFO_EXAMPLE is PS GENERAL, so do not pass null indicators */
EXEC SQL CALL :procname (:dbinfo_in_job, :dbinfo_out_salary, :out_dbname, :out_dbversion, :out_sqlcode);
EMB_SQL_CHECK("CALL DBINFO_EXAMPLE");
printf("Stored procedure returned successfully with SQLCODE = %d\n", out_sqlcode);
printf("Average salary for job %s = %9.2lf\n", dbinfo_in_job, dbinfo_out_salary);
printf("Database name from DBINFO structure = %s\n", out_dbname);
printf("Database version from DBINFO structure = %s\n", out_dbversion);
return 0;
}
int callProgramTypeMain()
{
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to MAIN_EXAMPLE */
char main_in_job[9];
double main_out_salary;
EXEC SQL END DECLARE SECTION;
/********************************************************\
* Call MAIN_EXAMPLE stored procedure *
\********************************************************/
strcpy(main_in_job, "DESIGNER");
strcpy(procname, "MAIN_EXAMPLE");
printf("\nCALL stored procedure named %s\n", procname);
/* MAIN_EXAMPLE is PS DB2 GENERAL, so do not pass null indicators */
EXEC SQL CALL :procname (:main_in_job, :main_out_salary, :out_sqlcode);
EMB_SQL_CHECK("CALL MAIN_EXAMPLE");
/* Check value of returned SQLCODE */
if (out_sqlcode == 0) {
printf("Stored procedure returned successfully\n");
printf("Average salary for job %s = %9.2lf\n", main_in_job, main_out_salary);
}
else {
printf("Stored procedure failed with SQLCODE %d\n", out_sqlcode);
}
return 0;
}
/* end of program : spclient.sqc */