DSN8ED9
Demonstrates how to use an application program to call DSN8ES3, a sample native SQL procedure.
/*********************************************************************
* Module name = DSN8ED9 (sample program) *
* *
* DESCRIPTIVE NAME: Sample client for: *
* DSN8ES3 (DB2 sample native SQL procedure) *
* *
* *
* LICENSED MATERIALS - PROPERTY OF IBM *
* 5650-DB2 *
* (C) COPYRIGHT 2006, 2016 IBM CORP. ALL RIGHTS RESERVED. *
* *
* STATUS = VERSION 12 *
* *
* Function: Demonstrates how to use an application program to call *
* DSN8ES3, a sample native SQL procedure. DSN8ED9 *
* receives the schema and name of a stored procedure *
* and passes it to DSN8ES3 to request the CREATE PROCEDURE *
* statement. *
* *
* Notes: *
* Dependencies: Requires DSN8.DSN8ES3 *
* *
* Restrictions: *
* *
* Module type: C program *
* Processor: DB2 Precompiler *
* IBM C/C++ for z/OS *
* Module size: See linkedit output *
* Attributes: Reentrant and reusable *
* *
* Entry point: DSN8ED9 *
* Purpose: See Function *
* Linkage: Standard MVS program invocation, three parameters. *
* *
* Parameters: DSN8ED9 uses the C "main" argument convention of *
* argv (argument vector) and argc (argument count). *
* *
* - ARGV[0]: (input) pointer to a char[9], *
* null-terminated string having the name of *
* this program (DSN8ED9) *
* - ARGV[1]: (input) pointer to a char[129], *
* null-terminated string having the schema *
* of a stored procedure *
* - ARGV[2]: (input) pointer to a char[129], *
* null-terminated string having the name of *
* a stored procedure *
* - ARGV[3]: (input) pointer to a char[17], *
* null-terminated string having the name of *
* the server where DSN8ES3 is to be run. *
* This is an optional parameter; the local *
* server is used if no argument is provided. *
* *
* Inputs: None *
* *
* Outputs: Standard output (SYSPRINT) *
* *
* Normal Exit: Return Code: 0 *
* - Message: CREATE PROCEDURE statement for specified *
* stored procedure *
* *
* Normal with Warnings Exit: Return Code: 0004 *
* - Message: DSN8ES3 ran successfully but returned *
* no output *
* *
* Error Exit: Return Code: 0012 *
* - Message: DSN8ES3 has completed with return code <n> *
* - Message: The length of the argument specified for *
* the <parameter-name> does not fall within *
* the required bounds of <minimum-length> *
* and <maximum-length> *
* - Message: DSN8ED9 was invoked with <parameter-count> *
* parameters. At least 2 parameters are *
* required *
* - Message: <formatted SQL text from DSNTIAR> *
* *
* External References: *
* - Routines/Services: DSNTIAR: DB2 msg text formatter *
* - Data areas : None *
* - Control blocks : None *
* *
* *
* Pseudocode: *
* DSN8ED9: *
* - call getCallParms to receive and validate call parm arguments*
* - call connectToLocation *
* - call callDSN8ES3 to invoke the sample native SQL procedure *
* - call processDSN8ES3resultSet to output results from DSN8ES3 *
* End DSN8ED9 *
* *
* *
* Change activity = *
* 04/22/2015 Storage overlay stops output d176357 *
* *
*********************************************************************/
/********************** C library definitions ***********************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <decimal.h>
/***************************** Equates ******************************/
#define NULLCHAR '\0' /* Null character */
#define RETNRM 0 /* Normal return code @04*/
#define RETWRN 4 /* Warning return code */
#define RETERR 8 /* Error return code */
#define RETSEV 12 /* Severe error return code */
enum flag {No, Yes}; /* Settings for flags */
/******************** DB2 SQL Communication Area ********************/
EXEC SQL INCLUDE SQLCA;
/************************ DB2 Host Variables ************************/
EXEC SQL BEGIN DECLARE SECTION;
long int hvSequence; /* Result set row sequence no.*/
char hvLine[80]; /* line */
char hvSpSchema[129]; /* Stored procedure schema */
short int niSpSchema = 0; /* Indic var for schema */
char hvSpName[129]; /* Stored procedure name */
short int niSpName = 0; /* Indic var for name */
char hvLocationName[17]; /* Server location name */
EXEC SQL END DECLARE SECTION;
/********************* DB2 Result Set Locators **********************/
EXEC SQL BEGIN DECLARE SECTION;
static volatile SQL TYPE IS RESULT_SET_LOCATOR *DSN8ES3_rs_loc;
EXEC SQL END DECLARE SECTION;
/********************* DSN8ED9 Global Variables *********************/
unsigned short resultSetReturned = 0;/* DSN8ES3 result set status */
long int rc = 0; /* DSN8ED9 return code */
/******************** DSN8ED9 Function Prototypes *******************/
int main /* DSN8ED9 driver */
( int argc, /* - Input argument count */
char *argv[] /* - Input argument vector */
);
void getCallParms /* Process args to call parms */
( int argc, /* - Input argument count */
char *argv[] /* - Input argument vector */
);
void connectToLocation( void ); /* Connect to DB2 location */
void callDSN8ES3( void ); /* Call DSN8ES3 */
void processDSN8ES3resultSet( void ); /* Process DSN8ES3 result set */
void associateResultSetLocator(void); /* Assoc DSN8ES3 RS locator */
void allocateResultSetCursor( void ); /* Alloc DSN8ES3 RS cursor */
void writeDSN8ES3results( void ); /* Output DSN8ES3 results */
void fetchFromResultSetCursor( void );/* Read DSNTSPMP RS cursor */
void issueInvalidCallParmCountError /* Handler for parm count err */
( int argc /* - in: no. parms received */
);
void issueInvalidParmLengthError /* Handler for parm len error */
( char *parmName, /* - in: identify of parm */
int minLength, /* - in: min valid length */
int maxLength /* - in: max valid length */
);
void issueSqlError /* Handler for SQL error */
( char *locMsg /* - in: Call location */
);
int main /* DSN8ED9 driver */
( int argc, /* - Input argument count */
char *argv[] /* - Input argument vector */
)
/*******************************************************************
* Get input parms, pass them to DSN8ES3, and process the results *
*******************************************************************/
{ printf( "**** DSN8ED9: Sample client for DB2 PSM "
"Stored Procedure Sample (DSN8ES3)\n\n" );
/*****************************************************************
* Extract the following information from the call parms: *
* (1) The schema of the stored procedure *
* (2) The name of the stored procedure *
* (3) Optional: The name of the location where the stored proc *
* resides *
*****************************************************************/
getCallParms( argc,argv );
/*****************************************************************
* Connect to location where the stored procedure resides *
*****************************************************************/
if( rc < RETSEV && strlen(hvLocationName) > 0 )
connectToLocation();
if( rc < RETSEV )
callDSN8ES3();
if( rc < RETSEV && resultSetReturned == Yes )
processDSN8ES3resultSet();
return( rc );
} /* end main */
void getCallParms /* Process args to call parms */
( int argc, /* - Input argument count */
char *argv[] /* - Input argument vector */
)
/*******************************************************************
* Verifies that correct call parms have been passed in: *
* - Two parameters (the schema and the name of a stored procedure) *
* require an argument *
* - The third parameter (location name) is optional *
*******************************************************************/
{ if( argc < 3 || argc > 4 )
{ issueInvalidCallParmCountError( argc );
}
else if( strlen( argv[1] ) < 1 || strlen( argv[1] ) > 130 )
{ issueInvalidParmLengthError("Stored procedure schema",
1,130);
}
else if( strlen( argv[2] ) < 1 || strlen( argv[1] ) > 130 )
{ issueInvalidParmLengthError("Stored procedure name",
1,130);
}
else
{ strcpy( hvSpSchema, argv[1] );
strcpy( hvSpName, argv[2] );
}
if( argc > 3 )
if( strlen( argv[3] ) < 1 || strlen( argv[3] ) > 16 )
{ issueInvalidParmLengthError("Server Location Name",1,16);
}
else
strcpy( hvLocationName,argv[3] );
else
hvLocationName[0] = NULLCHAR;
} /* end of getCallParms */
void connectToLocation( void ) /* Connect to DB2 location */
/*******************************************************************
* Connects to the DB2 location specified in call parm number 3 *
*******************************************************************/
{ EXEC SQL
CONNECT TO :hvLocationName;
if( SQLCODE != 0 )
{ issueSqlError( "Connect to location failed" );
}
} /* end of connectToLocation */
void callDSN8ES3( void ) /* Run sample native SQL proc */
/*******************************************************************
* Calls the DSN8ES3 (sample native SQL procedure) *
*******************************************************************/
{ printf( "\n");
printf( "-> Now requesting CREATE PROCEDURE statement for %s.%s\n",
hvSpSchema, hvSpName );
EXEC SQL CALL DSN8.DSN8ES3( :hvSpSchema :niSpSchema,
:hvSpName :niSpName );
/*****************************************************************
* Analyze status codes from DSN8ES3 *
*****************************************************************/
if( SQLCODE == 466 )
{ resultSetReturned = Yes;
}
else if( SQLCODE == 0 )
{ resultSetReturned = No;
printf( "\n");
printf( "-> Call to DSN8ES3 succeeded "
"but returned no result\n" );
}
else
{ issueSqlError( "Call to DSN8ES3 failed" );
}
} /* end of callDSN8ES3 */
void processDSN8ES3resultSet( void ) /* Handle DSN8ES3 result set */
/*******************************************************************
* Outputs data from the result set returned by DSN8ES3 *
*******************************************************************/
{
/*****************************************************************
* Associate a locator with the result set from DSN8ES3 *
*****************************************************************/
associateResultSetLocator();
/*****************************************************************
* Allocate a cursor for the result set *
*****************************************************************/
if( rc < RETSEV )
allocateResultSetCursor();
/*****************************************************************
* Output data from the result set *
*****************************************************************/
if( rc < RETSEV )
writeDSN8ES3results();
} /* end of processDSN8ES3resultSet */
void associateResultSetLocator(void) /* Associate DSN8ES3 RS locator*/
/*******************************************************************
* Associates the result set from DSN8ES3 with a result set locator *
*******************************************************************/
{ EXEC SQL
ASSOCIATE
LOCATORS( :DSN8ES3_rs_loc )
WITH PROCEDURE DSN8.DSN8ES3;
if( SQLCODE != 0 )
{ issueSqlError( "Associate locator call failed" );
}
} /* end of associateResultSetLocator */
void allocateResultSetCursor( void ) /* Alloc DSN8ES3 RS cursor */
/*******************************************************************
* Allocates a cursor to the locator for the DSN8ES3 result set *
*******************************************************************/
{ EXEC SQL
ALLOCATE DSN8ES3_RS_CSR
CURSOR FOR RESULT SET :DSN8ES3_rs_loc;
if( SQLCODE != 0 )
{ issueSqlError( "Allocate result set cursor call failed" );
}
} /* end of allocateResultSetCursor */
void writeDSN8ES3results( void ) /* Print DSN8ES3 results */
/*******************************************************************
* Outputs the results returned in the result set from DSN8ES3 *
*******************************************************************/
{ /*****************************************************************
* Get the first entry in the result set *
*****************************************************************/
fetchFromResultSetCursor();
/*****************************************************************
* Process all rows in the result set *
*****************************************************************/
while( SQLCODE == 0 && rc < RETSEV )
{ printf( "%s\n",hvLine );
if( rc < RETSEV )
{ fetchFromResultSetCursor();
}
}
} /* end of writeDSN8ES3results */
void fetchFromResultSetCursor( void ) /* Read DSN8ES3 RS cursor */
/*******************************************************************
* Reads the cursor for the DSN8ES3 result set *
*******************************************************************/
{ memset( hvLine,' ',80 ); /*d176357*/
EXEC SQL
FETCH DSN8ES3_RS_CSR
INTO :hvSequence,
:hvLine;
if( SQLCODE != 0 && SQLCODE != 100 && rc < RETSEV )
{ issueSqlError( "*** Fetch from result set cursor failed" );
}
} /* end of fetchFromResultSetCursor */
void issueInvalidCallParmCountError /* Handler for parm count err */
( int argc /* - in: no. parms received */
)
/*******************************************************************
* Called when this program is invoked with an inappropriate number *
* of call parms. *
*******************************************************************/
{ printf( "ERROR: DSN8ED9 was invoked with %i parameters\n",--argc );
printf( " - The first two parms (schema and name "
"of a stored procedure) are required\n" );
printf( " - The third parm (location name) "
"is optional\n" );
printf( "-----> Processing halted\n" );
rc = RETSEV;
} /* end of issueInvalidCallParmCountError */
void issueInvalidParmLengthError /* Handler for parm len error */
( char *parmName, /* - in: identify of parm */
int minLength, /* - in: min valid length */
int maxLength /* - in: max valid length */
)
/*******************************************************************
* Called when the length of an argument specified for a DSN8ES3 *
* parameter (parmName) does not fall within the valid bounds for *
* size (minLength and maxLength) for that parameter *
*******************************************************************/
{ printf( "ERROR: The length of the argument specified for the %s "
"parameter\n",parmName );
printf( " does not fall within the required bounds of %i "
"and %i\n",minLength,maxLength );
printf( "-----> Processing halted\n" );
rc = RETSEV;
} /* end of issueInvalidParmLengthError */
#pragma linkage(dsntiar, OS)
void issueSqlError /* Handler for SQL error */
( char *locMsg /* - in: Call location */
)
/*******************************************************************
* Called when an unexpected SQLCODE is returned from a DB2 call *
*******************************************************************/
{ struct error_struct { /* DSNTIAR message structure */
short int error_len;
char error_text[10][80];
} error_message = {10 * 80};
extern short int dsntiar( struct sqlca *sqlca,
struct error_struct *msg,
int *len );
short int DSNTIARrc; /* DSNTIAR Return code */
int j; /* Loop control */
static int lrecl = 80; /* Width of message lines */
/*****************************************************************
* print the locator message *
*****************************************************************/
printf( "ERROR: %-80s\n", locMsg );
printf( "-----> Processing halted\n" );
/*****************************************************************
* format and print the SQL message *
*****************************************************************/
DSNTIARrc = dsntiar( &sqlca, &error_message, &lrecl );
if( DSNTIARrc == 0 )
for( j = 0; j <= 10; j++ )
printf( " %.80s\n", error_message.error_text[j] );
else
{
printf( " *** ERROR: DSNTIAR could not format the message\n" );
printf( " *** SQLCODE is %d\n",SQLCODE );
printf( " *** SQLERRM is \n" );
for( j=0; j<sqlca.sqlerrml; j++ )
printf( "%c", sqlca.sqlerrmc[j] );
printf( "\n" );
}
/*****************************************************************
* set severe error code *
*****************************************************************/
rc = RETSEV;
} /* end of issueSqlError */