DSN8DUWC
Invokes the sample UDF table function WEATHER to demon- strate how a UDF and UDF table handling using static SQL.
/*********************************************************************
* Module name = DSN8DUWC (DB2 sample program) *
* *
* DESCRIPTIVE NAME = Client for sample UDF table function WEATHER *
* *
* *
* LICENSED MATERIALS - PROPERTY OF IBM *
* 5645-DB2 *
* (C) COPYRIGHT 1998 IBM CORP. ALL RIGHTS RESERVED. *
* *
* STATUS = VERSION 6 *
* *
* Function: Invokes the sample UDF table function WEATHER to demon- *
* strate how a UDF and UDF table handling using static SQL.*
* *
* Notes: *
* Dependencies: Requires IBM C/C++ for OS/390 V1R3 or higher *
* *
* Restrictions: *
* *
* Module type: C program *
* Processor: IBM C/C++ for OS/390 V1R3 or higher *
* Module size: See linkedit output *
* Attributes: Re-entrant and re-usable *
* *
* Entry Point: DSN8DUWC *
* Purpose: See Function *
* Linkage: DB2SQL *
* Invoked via SQL UDF call *
* *
* *
* Parameters: DSN8DUWC 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 (DSN8DUWC) *
* - ARGV[1] = (input) pointer to a char[45], null- *
* terminated string having the name of the *
* source data for the weather reports. *
* *
* Normal Exit: Return Code: 0000 *
* - Message: none *
* *
* Error Exit: Return Code: 0008 *
* - Message: DSN8DUWC failed: Invalid parameter count *
* *
* - Message: <formatted SQL text from DSNTIAR> *
* *
* *
* External References: *
* - Routines/Services: DSNTIAR: DB2 msg text formatter *
* - Data areas : None *
* - Control blocks : None *
* *
* Pseudocode: *
* DSN8DUWC: *
* - Verify that 2 input parameters (program name and weather data *
* set name) were passed. *
* - if not, issue diagnostic message and end with code 0008 *
* - Open WEATHER_CURSOR, the client cursor for the WEATHER UDF *
* table function, passing the weather data set name as a host *
* variable *
* - if unsuccessful, call sql_error to issue a diagnostic mes- *
* sage, then end with code 0008. *
* - Do while not end of cursor *
* - Read the cursor *
* - If successful, print the result *
* - else if not end of cursor, call sql_error to issue a diag- *
* nostic message, then end with code 0008. *
* - Close the cursor *
* - if unsuccessful, call sql_error to issue a diagnostic mes- *
* sage, then end with code 0008. *
* End DSN8DUWC *
* *
* sql_error: *
* - call DSNTIAR to format the unexpected SQLCODE. *
* End sql_error *
* *
*********************************************************************/
/********************** C library definitions ***********************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/***************************** Equates ******************************/
#define NULLCHAR '\0' /* Null character */
#define OUTLEN 80 /* Length of output line */
#define DATA_DIM 10 /* Number of message lines */
#define NOT_OK 0 /* Run status indicator: Error*/
#define OK 1 /* Run status indicator: Good */
/******************** DB2 SQL Communication Area ********************/
EXEC SQL INCLUDE SQLCA;
/************************ DB2 Host Variables ************************/
EXEC SQL BEGIN DECLARE SECTION;
char hvWeatherDSN[44]; /* host var for weather dsn */
short int niWeatherDSN = 0; /* indic var for weather dsn */
char hvCity[31]; /* host var for name of city */
short int niCity = 0; /* indic var for city name */
long int hvTemp_in_f = 0; /* host var, fahrenheit temp */
short int niTemp_in_f = 0; /* indic var for temperature */
long int hvHumidity = 0; /* host var, percent humidity */
short int niHumidity = 0; /* indic var for humidity */
char hvWind[5]; /* host var, wind direction */
short int niWind = 0; /* indic var for wind direct */
long int hvWind_velocity = 0; /* host var, wind velocity */
short int niWind_velocity = 0; /* indic var for wind velocity*/
double hvBarometer = 0; /* host var, barometric press */
short int niBarometer = 0; /* indic var for baro pressure*/
char hvForecast[26]; /* host var, forecast */
short int niForecast = 0; /* indic var for forecast */
EXEC SQL END DECLARE SECTION;
/******************* DB2 SQL Cursor Declarations ********************/
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL DECLARE WEATHER_CURSOR
CURSOR FOR
SELECT CITY, 00008900
TEMP_IN_F, 00008900
HUMIDITY, 00008900
WIND, 00008900
WIND_VELOCITY, 00008900
BAROMETER,
FORECAST
FROM TABLE( DSN8.WEATHER(:hvWeatherDSN) ) AS W
WHERE CITY = 'Juneau, AK';
EXEC SQL END DECLARE SECTION;
/********************** DB2 Message Formatter ***********************/
struct error_struct /* DSNTIAR message structure */
{
short int error_len;
char error_text[DATA_DIM][OUTLEN];
} error_message = {DATA_DIM * (OUTLEN)};
#pragma linkage( dsntiar, OS )
extern short int dsntiar( struct sqlca *sqlca,
struct error_struct *msg,
int *len );
/******************** DSN8DUWC Global Variables *********************/
short int status = OK; /* DSN8DUWC run status */
long int completion_code = 0; /* DSN8DUWC return code */
/******************* DSN8DUWC Function Prototypes *******************/
int main( int argc, char *argv[] );
void sql_error( char locmsg[] );
int main( int argc, char *argv[] )
/*********************************************************************
* *
*********************************************************************/
{
if( argc == 2 )
strcpy( hvWeatherDSN,argv[1] );
else
{
printf( "DSN8DUWC failed: Invalid parameter count\n" );
status = NOT_OK;
}
if( status == OK )
{
EXEC SQL OPEN WEATHER_CURSOR;
if( SQLCODE != 0 )
sql_error( " *** Open weather cursor" );
}
while( SQLCODE == 0 && status == OK )
{
EXEC SQL FETCH WEATHER_CURSOR
INTO :hvCity :niCity,
:hvTemp_in_f :niTemp_in_f,
:hvHumidity :niHumidity,
:hvWind :niWind,
:hvWind_velocity:niWind_velocity,
:hvBarometer :niBarometer,
:hvForecast :niForecast;
if( SQLCODE == 0 )
{
printf( "Weather Report for %s\n", hvCity );
printf( "... Temperature : %d\n", hvTemp_in_f );
printf( "... Humidity : %d\n", hvHumidity );
printf( "... Wind direction: %s\n", hvWind );
printf( "... Wind velocity : %d\n", hvWind_velocity );
printf( "... Barometer : %.2f\n", hvBarometer );
printf( "... Forecast : %s\n", hvForecast );
}
else if( SQLCODE != 100 )
sql_error( " *** Fetch from weather cursor" );
}
if( status == OK )
{
EXEC SQL CLOSE WEATHER_CURSOR;
if( SQLCODE != 0 )
sql_error( " *** Close weather cursor" );
}
if( status != OK )
completion_code = 8;
return( completion_code );
} /* end main */
/*********************************************************************
**********************************************************************
** SQL error handler **
**********************************************************************
*********************************************************************/
void sql_error( char locmsg[] ) /*proc*/
{
short int rc; /* DSNTIAR Return code */
int j,k; /* Loop control */
static int lrecl = OUTLEN; /* Width of message lines */
/*******************************************************************
* set status to prevent further processing *
*******************************************************************/
status = NOT_OK;
/*******************************************************************
* print the locator message *
*******************************************************************/
printf( " %.80s\n", locmsg );
/*******************************************************************
* format and print the SQL message *
*******************************************************************/
rc = dsntiar( &sqlca, &error_message, &lrecl );
if( rc == 0 )
for( j=0; j<DATA_DIM; j++ )
{
for( k=0; k<OUTLEN; k++ )
putchar(error_message.error_text[j][k] );
putchar('\n');
}
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" );
}
} /* end of sql_error */