DSN8ED3
Demonstrates how to call the sample PSM stored procedure DSN8ES1 using static SQL.
/********************************************************************* 00010000
* Module name = DSN8ED3 (DB2 sample program) * 00020000
* * 00030000
* DESCRIPTIVE NAME = Client for sample PSM Stored Procedure DSN8ES1 * 00040000
* * 00050000
* LICENSED MATERIALS - PROPERTY OF IBM * 00070000
* 5675-DB2 * 00080000
* (C) COPYRIGHT 2000 IBM CORP. ALL RIGHTS RESERVED. * 00090000
* * 00100000
* STATUS = VERSION 7 * 00110000
* * 00120000
* Function: Demonstrates how to call the sample PSM stored procedure * 00130000
* DSN8ES1 using static SQL. * 00140000
* * 00150000
* Notes: * 00160000
* Dependencies: Requires IBM C/C++ for OS/390 V1R3 or higher * 00170000
* * 00180000
* Restrictions: * 00190000
* * 00200000
* Module type: C program * 00210000
* Processor: IBM C/C++ for OS/390 V1R3 or higher * 00220000
* Module size: See linkedit output * 00230000
* Attributes: Re-entrant and re-usable * 00240000
* * 00250000
* Entry Point: DSN8ED3 * 00260000
* Purpose: See Function * 00270000
* Linkage: DB2SQL * 00280000
* Invoked via SQL UDF call * 00290000
* * 00300000
* * 00310000
* Parameters: DSN8ED3 uses the C "main" argument convention of * 00320000
* argv (argument vector) and argc (argument count). * 00330000
* * 00340000
* - ARGV[0] = (input) pointer to a char[9], null- * 00350000
* terminated string having the name of * 00360000
* this program (DSN8ED3) * 00370000
* - ARGV[1] = (input) pointer to a char[4], null- * 00380000
* terminated string having the department * 00390000
* number to be passed to DSN8ES1. * 00400000
* - ARGV[2] = (input) pointer to a char[16], null- * 00410000
* terminated string having the location * 00420000
* name of a server to connect to process * 00430000
* the current request. This parameter is * 00440000
* optional. In its absence, the current * 00450000
* location is used. * 00460000
* * 00470000
* Normal Exit: Return Code: 0000 * 00480000
* - Message: none * 00490000
* * 00500000
* Error Exit: Return Code: 0008 * 00510000
* - Message: DSN8ED3 failed: Invalid parameter count * 00520000
* * 00530000
* - Message: <formatted SQL text from DSNTIAR> * 00540000
* * 00550000
* * 00560000
* External References: * 00570000
* - Routines/Services: DSNTIAR: DB2 msg text formatter * 00580000
* - Data areas : None * 00590000
* - Control blocks : None * 00600000
* * 00610000
* Pseudocode: * 00620000
* DSN8ED3: * 00630000
* - Verify that number of input parameters passed is either: * 00640000
* - 2 (program name and department number); or * 00650000
* - 3 (program name, department number, and (remote) server name * 00660000
* - Other: issue diagnostic message and end with code 0008 * 00670000
* - Connect to server location, if one was passed in. * 00680000
* - Call sample stored procedure DSN8ES1, passing the department * 00690000
* number as the argument of the first (input) parameter. * 00700000
* - if unsuccessful, call sql_error to issue a diagnostic mes- * 00710000
* sage, then end with code 0008. * 00720000
* - Report the following parameters, passed back from DSN8ES1: * 00730000
* - Total of salary and bonusses for department members * 00740000
* - Number of employees in the department who received a bonus * 00750000
* - If a result set was returned, call processResultSet to handle * 00760000
* it * 00770000
* End DSN8ED3 * 00780000
* * 00790000
* processResultSet: * 00800000
* - Associate a locator with the result set passed from DSN8ES1, * 00810000
* which contains the serial number, first and last name, salary, * 00820000
* and bonus for each department member who got a bonus. * 00830000
* - if unsuccessful, call sql_error to issue a diagnostic mes- * 00840000
* sage, then end with code 0008. * 00850000
* - Allocate DSN8ES1_RS_CSR as a cursor for the locator * 00860000
* - if unsuccessful, call sql_error to issue a diagnostic mes- * 00870000
* sage, then end with code 0008. * 00880000
* - Do while not end of cursor * 00890000
* - Read the cursor * 00900000
* - If successful, print the row as a report line item * 00910000
* - else if not end of cursor, call sql_error to issue a diag- * 00920000
* nostic message, then end with code 0008. * 00930000
* - Close the cursor * 00940000
* - if unsuccessful, call sql_error to issue a diagnostic mes- * 00950000
* sage, then end with code 0008. * 00960000
* End processResultSet * 00970000
* * 00980000
* sql_error: * 00990000
* - call DSNTIAR to format the unexpected SQLCODE. * 01000000
* End sql_error * 01010000
* * 01020000
*********************************************************************/ 01030000
/********************** C library definitions ***********************/ 01040000
#include <stdio.h> 01050000
#include <stdlib.h> 01060000
#include <string.h> 01070000
#include <decimal.h> 01080000
01090000
/***************************** Equates ******************************/ 01100000
#define NULLCHAR '\0' /* Null character */ 01110000
01120000
#define OUTLEN 80 /* Length of output line */ 01130000
#define DATA_DIM 10 /* Number of message lines */ 01140000
01150000
#define NOT_OK 0 /* Run status indicator: Error*/ 01160000
#define OK 1 /* Run status indicator: Good */ 01170000
01180000
01190000
/******************** DB2 SQL Communication Area ********************/ 01200000
EXEC SQL INCLUDE SQLCA; 01210000
01220000
01230000
/************************ DB2 Host Variables ************************/ 01240000
EXEC SQL BEGIN DECLARE SECTION; 01250000
char hvDeptNo[4]; /* ID of department to query */ 01260000
short int niDeptNo = 0; /* Indic var for dept number */ 01270000
01280000
char hvServerName[17]; /* Location name of server */ 01290000
01300000
decimal(15,2) hvDeptEarnings = 0; /* Total dept salaries & bonus*/ 01310000
short int niDeptEarnings = 0; /* Indic var for dept salary */ 01320000
01330000
long int hvDeptBonusCount= 0; /* Total no. of bonuses in dpt*/ 01340000
short int niDeptBonusCount= 0; /* Indic var for dpt bonus cnt*/ 01350000
01360000
long int hvSequence; /* Result set row sequence no.*/ 01370000
char hvEmpno[7]; /* Employee number */ 01380000
char hvFirstName[13]; /* Employee first name */ 01390000
char hvLastName[16]; /* Employee last name */ 01400000
decimal(9,2) hvSalary = 0; /* Employee salary */ 01410000
decimal(9,2) hvBonus = 0; /* Employee bonus */ 01420000
01430000
EXEC SQL END DECLARE SECTION; 01440000
01450000
01460000
/********************* DB2 Result Set Locators **********************/ 01470000
EXEC SQL BEGIN DECLARE SECTION; 01480000
static volatile SQL TYPE IS RESULT_SET_LOCATOR *DSN8ES1_rs_loc; 01490000
EXEC SQL END DECLARE SECTION; 01500000
01510000
01520000
/********************** DB2 Message Formatter ***********************/ 01530000
struct error_struct /* DSNTIAR message structure */ 01540000
{ 01550000
short int error_len; 01560000
char error_text[DATA_DIM][OUTLEN]; 01570000
} error_message = {DATA_DIM * (OUTLEN)}; 01580000
01590000
#pragma linkage( dsntiar, OS ) 01600000
01610000
extern short int dsntiar( struct sqlca *sqlca, 01620000
struct error_struct *msg, 01630000
int *len ); 01640000
01650000
01660000
/********************* DSN8ED3 Global Variables *********************/ 01670000
short int status = OK; /* DSN8ED3 run status */ 01680000
01690000
long int completion_code = 0; /* DSN8ED3 return code */ 01700000
01710000
01720000
/******************** DSN8ED3 Function Prototypes *******************/ 01730000
int main( int argc, char *argv[] ); 01740000
void processResultSet( void ); 01750000
void sql_error( char locmsg[] ); 01760000
01770000
01780000
int main( int argc, char *argv[] ) 01790000
/********************************************************************* 01800000
* Get input parms, pass them to DSN8ES1, and process the results * 01810000
*********************************************************************/ 01820000
{ 01830000
printf( "**** DSN8ED3: Sample client for DB2 PSM " 01840000
"Stored Procedure Sample (DSN8ES1)\n\n" ); 01850000
01860000
if( argc == 2 ) /* Only dept no. was passed */ 01870000
{ 01880000
strcpy( hvDeptNo,argv[1] ); 01890000
} 01900000
else if( argc == 3 ) /* Dept & server name passed */ 01910000
{ 01920000
strcpy( hvDeptNo,argv[1] ); 01930000
strcpy( hvServerName,argv[2] ); 01940000
EXEC SQL CONNECT TO :hvServerName; 01950000
if( SQLCODE != 0 ) 01960000
sql_error( " *** Connect to server" ); 01970000
} 01980000
else 01990000
{ 02000000
printf( "DSN8ED3 failed: Invalid parameter count\n" ); 02010000
status = NOT_OK; 02020000
} 02030000
02040000
if( status == OK ) 02050000
printf( "Salary and Bonus Report for Department %s\n",hvDeptNo ); 02060000
02070000
if( status == OK ) 02080000
{ 02090000
EXEC SQL CALL DSN8.DSN8ES1( :hvDeptNo :niDeptNo, 02100000
:hvDeptEarnings :niDeptEarnings, 02110000
:hvDeptBonusCount:niDeptBonusCount );02120000
if( SQLCODE != 0 && SQLCODE != 466 ) 02130000
sql_error( " *** Call DSN8ES1" ); 02140000
else 02150000
{ 02160000
printf( "Total Department Salaries and Bonuses: %D(15,2)\n", 02170000
hvDeptEarnings ); 02180000
printf( "Total Number of Bonuses in Department: %i\n", 02190000
hvDeptBonusCount ); 02200000
} 02210000
} 02220000
02230000
if( SQLCODE == 0 && status == OK ) 02240000
if( hvDeptBonusCount != 0 ) 02250000
{ 02260000
printf( "\n*** Error: Result set was expected from DSN8ES1 " 02270000
"but was not received\n" ); 02280000
status = NOT_OK; 02290000
} 02300000
02310000
if( SQLCODE == 466 && status == OK ) 02320000
processResultSet(); 02330000
02340000
if( status != OK ) 02350000
completion_code = 8; 02360000
02370000
return( completion_code ); 02380000
02390000
} /* end main */ 02400000
02410000
02420000
void processResultSet( void ) 02430000
/********************************************************************* 02440000
* If a result was returned by DSN8ES1, this function will process it * 02450000
*********************************************************************/ 02460000
{ 02470000
printf( "Bonus Earners are\n" ); 02480000
02490000
printf( "Serial First Name Last Name " 02500000
"Salary Bonus\n" ); 02510000
printf( "------ ------------ --------------- " 02520000
"--------- ---------\n" ); 02530000
02540000
EXEC SQL ASSOCIATE LOCATOR( :DSN8ES1_rs_loc ) 02550000
WITH PROCEDURE DSN8.DSN8ES1; 02560000
if( SQLCODE != 0 ) 02570000
sql_error( " *** Associate locator DSN8ES1_rs_loc" ); 02580000
02590000
if( SQLCODE == 0 && status == OK ) 02600000
{ 02610000
EXEC SQL ALLOCATE DSN8ES1_RS_CSR 02620000
CURSOR FOR 02630000
RESULT SET :DSN8ES1_rs_loc; 02640000
if( SQLCODE != 0 ) 02650000
sql_error( " *** Allocate cursor for DSN8ES1 result set" ); 02660000
} 02670000
02680000
while( SQLCODE == 0 && status == OK ) 02690000
{ 02700000
EXEC SQL FETCH DSN8ES1_RS_CSR 02710000
INTO :hvSequence, 02720000
:hvEmpno, 02730000
:hvFirstName, 02740000
:hvLastName, 02750000
:hvSalary, 02760000
:hvBonus; 02770000
if( SQLCODE == 0 ) 02780000
printf( "%s %s %s %9D(9,2) %9D(9,2)\n", 02790000
hvEmpno, hvFirstName, hvLastName, hvSalary, hvBonus ); 02800000
else if( SQLCODE != 100 ) 02810000
sql_error( " *** Fetch from DSN8ES1 result set cursor" ); 02820000
} 02830000
02840000
} /* end void processResultSet( void ) */ 02850000
02860000
02870000
/********************************************************************* 02880000
********************************************************************** 02890000
** SQL error handler ** 02900000
********************************************************************** 02910000
*********************************************************************/ 02920000
void sql_error( char locmsg[] ) /*proc*/ 02930000
{ 02940000
02950000
02960000
short int rc; /* DSNTIAR Return code */ 02970000
int j,k; /* Loop control */ 02980000
static int lrecl = OUTLEN; /* Width of message lines */ 02990000
03000000
/******************************************************************* 03010000
* set status to prevent further processing * 03020000
*******************************************************************/ 03030000
status = NOT_OK; 03040000
03050000
/******************************************************************* 03060000
* print the locator message * 03070000
*******************************************************************/ 03080000
printf( " %.80s\n", locmsg ); 03090000
03100000
/******************************************************************* 03110000
* format and print the SQL message * 03120000
*******************************************************************/ 03130000
rc = dsntiar( &sqlca, &error_message, &lrecl ); 03140000
if( rc == 0 ) 03150000
for( j=0; j<DATA_DIM; j++ ) 03160000
{ 03170000
for( k=0; k<OUTLEN; k++ ) 03180000
putchar(error_message.error_text[j][k] ); 03190000
putchar('\n'); 03200000
} 03210000
else 03220000
{ 03230000
printf( " *** ERROR: DSNTIAR could not format the message\n" ); 03240000
printf( " *** SQLCODE is %d\n",SQLCODE ); 03250000
printf( " *** SQLERRM is \n" ); 03260000
for( j=0; j<sqlca.sqlerrml; j++ ) 03270000
printf( "%c", sqlca.sqlerrmc[j] ); 03280000
printf( "\n" ); 03290000
} 03300000
03310000
} /* end of sql_error */ 03320000