/****************************************************************************
**
** Source File Name = tblsrv.c
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
**
** This Table Function reads an input file which contains
** the Weather data and returns the weather information.
**
** Input File contains City Name followed by Weather info. This
** pattern is repeated for other cities.
** This would be an associated DDL for the following Table Function.
** Note that it does not take any input and returns 7 output columns.
**
** create function tfweather_u()
** returns table ( CITY VARCHAR(30),
** TEMP_IN_F INTEGER,
** HUMIDITY INTEGER,
** WIND VARCHAR(5),
** WIND_VELOCITY INTEGER,
** BAROMETER FLOAT,
** FORECAST VARCHAR(25) )
** specific tfweather_u
** disallow parallel
** not fenced not variant no sql
** no external action scratchpad final call
** language c parameter style sql
** EXTERNAL NAME 'tblsrv!weather';
**
** To make the library:
** Drop any instances of the function that previously existed.
** Compile without linking.
** Link with the DB2 libraries.
** Copy the compiled library to the "function" directory of
** the DB2INSTANCE that the library will used with.
**
** There is an example program called "tblcli.sqc" which creates and runs
** the user-defined function tfweather_u from the "tblsrv" library.
**
** EXTERNAL DEPENDENCIES :
** - Ensure existence of database for precompile purposes.
** - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
** or the Microsoft Visual C++ compiler (Windows)
** or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
****************************************************************************/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sql.h>
#include <sqludf.h> /* for use in compiling User Defined Function */
#define SQL_NOTNULL 0 /* Nulls Allowed - Value is not Null */
#define SQL_ISNULL -1 /* Nulls Allowed - Value is Null */
/* Short and long city name structure */
typedef struct {
char * city_short ;
char * city_long ;
} city_area ;
/* Scratchpad data */
/* Preserve information from one function call to the next call */
typedef struct {
/* FILE * file_ptr; if you use weather data text file */
int file_pos ; /* if you use a weather data buffer */
} scratch_area ;
/* Field descriptor structure */
typedef struct {
char fld_field[31] ; /* Field data */
int fld_ind ; /* Field null indicator data */
int fld_type ; /* Field type */
int fld_length ; /* Field length in the weather data */
int fld_offset ; /* Field offset in the weather data */
} fld_desc ;
/* Short and long city name data */
city_area cities[] = {
{ "alb", "Albany, NY" },
{ "atl", "Atlanta, GA" },
{ "aus", "Austin, TX" },
{ "bgm", "Binghamton, NY" },
{ "btv", "Burlington, VT" },
{ "chi", "Chicago, IL" },
{ "clt", "Charlotte, NC" },
{ "den", "Denver, CO" },
{ "ftw", "Dallas - Fort Worth, TX" },
{ "hou", "Houston, TX" },
{ "lax", "Los Angeles, CA" },
{ "lex", "Lexington, KY" },
{ "mci", "Kansas City, MO" },
{ "mem", "Memphis, TN" },
{ "mia", "Miami - Fort Lauderdale, FL" },
{ "mke", "Milwaukee, WI" },
{ "msp", "Twin Cities, MN" },
{ "nyc", "New York City, NY" },
{ "pbi", "Palm Beach, FL" },
{ "pit", "Pittsburgh, PA" },
{ "rdu", "Raleigh - Durham, NC" },
{ "ric", "Richmond, VA" },
{ "rst", "Rochester, MN" },
{ "sea", "Seattle, WA" },
{ "sfo", "San Francisco, CA" },
{ "sjc", "San Jose, CA" },
{ "slc", "Salt Lake City, UT" },
{ "stl", "St. Louis, MO" },
{ "tus", "Tuscon, AZ" },
{ "wbc", "Washington DC, DC" },
/* You may want to add more cities here */
/* Do not forget a null termination */
{ ( char * ) 0, ( char * ) 0 }
} ;
/* Field descriptor data */
fld_desc fields[] = {
{ "", SQL_ISNULL, SQL_TYP_VARCHAR, 30, 0 }, /* city */
{ "", SQL_ISNULL, SQL_TYP_INTEGER, 3, 2 }, /* temp_in_f */
{ "", SQL_ISNULL, SQL_TYP_INTEGER, 3, 7 }, /* humidity */
{ "", SQL_ISNULL, SQL_TYP_VARCHAR, 5, 13 }, /* wind */
{ "", SQL_ISNULL, SQL_TYP_INTEGER, 3, 19 }, /* wind_velocity */
{ "", SQL_ISNULL, SQL_TYP_FLOAT, 5, 24 }, /* barometer */
{ "", SQL_ISNULL, SQL_TYP_VARCHAR, 25, 30 }, /* forecast */
/* You may want to add more fields here */
/* Do not forget a null termination */
{ ( char ) 0, 0, 0, 0, 0 }
} ;
/* Following is the weather data buffer for this example. You */
/* may want to keep the weather data in a separate text file. */
/* Uncomment the following fopen() statement. Note that you */
/* have to specify the full path name for this file. */
char * weather_data[] = {
"alb.forecast",
" 34 28% wnw 3 30.53 clear",
"atl.forecast",
" 46 89% east 11 30.03 fog",
"aus.forecast",
" 59 57% south 5 30.05 clear",
"bgm.forecast",
" 36 32% south 10 30.34 overcast",
"btv.forecast",
" 38 30% south 5 30.36 clear",
"chi.forecast",
" 41 55% nne 12 30.15 partly cloudy",
"clt.forecast",
" 37 100% nne 10 30.18 light rain",
"den.forecast",
" 58 24% nne 8 29.67 clear",
"ftw.forecast",
" 49 73% nne 4 30.05 overcast",
"hou.forecast",
" 65 46% west 10 30.03 clear",
"lax.forecast",
" 60 86% east 6 29.96 light drizzle",
"lex.forecast",
" 46 92% east 8 29.99 fog",
"mci.forecast",
" 46 51% ne 9 30.06 overcast",
"mem.forecast",
" 53 96% wnw 8 29.97 moderate rain",
"mia.forecast",
" 82 64% se 17 30.08 mostly cloudy",
"mke.forecast",
" 37 59% ne 12 30.18 clear",
"msp.forecast",
" 41 39% se 6 30.19 n/a",
"nyc.forecast",
" 37 56% sse 6 30.44 overcast",
"pbi.forecast",
" 81 69% south 17 30.09 partly cloudy",
"pit.forecast",
" 39 82% east 11 30.21 light drizzle",
"rdu.forecast",
" 39 96% north 8 30.10 light rain",
"ric.forecast",
" 39 75% ene 12 30.20 light rain",
"rst.forecast",
" 40 50% se 10 30.17 n/a",
"sea.forecast",
" 51 37% ssw 8 29.93 clear",
"sfo.forecast",
" 53 54% west 19 29.98 partly cloudy",
"sjc.forecast",
" 62 44% wnw 17 n/a partly cloudy",
"slc.forecast",
" 63 26% south 25 29.45 mostly cloudy",
"stl.forecast",
" 43 55% north 9 30.06 overcast",
"tus.forecast",
" 73 18% south 8 29.80 clear",
"wbc.forecast",
" 38 96% ene 16 30.31 light rain",
/* You may want to add more weather data here */
/* Do not forget a null termination */
( char * ) 0
} ;
#ifdef __cplusplus
extern "C"
#endif
/* Find a full city name using a short name */
int get_name( char * short_name, char * long_name ) {
int name_pos = 0 ;
while ( cities[name_pos].city_short != ( char * ) 0 ) {
if (strcmp(short_name, cities[name_pos].city_short) == 0) {
strcpy( long_name, cities[name_pos].city_long ) ;
/* A full city name found */
return( 0 ) ;
}
name_pos++ ;
}
/* Could not find such city in the city data */
strcpy( long_name, "Unknown City" ) ;
return( -1 ) ;
}
#ifdef __cplusplus
extern "C"
#endif
/* Clean all field data and field null indicator data */
int clean_fields( int field_pos ) {
while ( fields[field_pos].fld_length != 0 ) {
memset( fields[field_pos].fld_field, '\0', 31 ) ;
fields[field_pos].fld_ind = SQL_ISNULL ;
field_pos++ ;
}
return( 0 ) ;
}
#ifdef __cplusplus
extern "C"
#endif
/* Fills all field data and field null indicator data ... */
/* ... from text weather data */
int get_value( char * value, int field_pos ) {
fld_desc * field ;
char field_buf[31] ;
double * double_ptr ;
int * int_ptr, buf_pos ;
while ( fields[field_pos].fld_length != 0 ) {
field = &fields[field_pos] ;
memset( field_buf, '\0', 31 ) ;
memcpy( field_buf,
( value + field->fld_offset ),
field->fld_length ) ;
buf_pos = field->fld_length ;
while ( ( buf_pos > 0 ) &&
( field_buf[buf_pos] == ' ' ) )
field_buf[buf_pos--] = '\0' ;
buf_pos = 0 ;
while ( ( buf_pos < field->fld_length ) &&
( field_buf[buf_pos] == ' ' ) )
buf_pos++ ;
if ( strlen( ( char * ) ( field_buf + buf_pos ) ) > 0 ||
strcmp( ( char * ) ( field_buf + buf_pos ), "n/a") != 0 ) {
field->fld_ind = SQL_NOTNULL ;
/* Text to SQL type conversion */
switch( field->fld_type ) {
case SQL_TYP_VARCHAR:
strcpy( field->fld_field,
( char * ) ( field_buf + buf_pos ) ) ;
break ;
case SQL_TYP_INTEGER:
int_ptr = ( int * ) field->fld_field ;
*int_ptr = atoi( ( char * ) ( field_buf + buf_pos ) ) ;
break ;
case SQL_TYP_FLOAT:
double_ptr = ( double * ) field->fld_field ;
*double_ptr = atof( ( char * ) ( field_buf + buf_pos ) ) ;
break ;
/* You may want to add more text to SQL type conversion here */
}
}
field_pos++ ;
}
return( 0 ) ;
}
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN weather( /* Return row fields */
SQLUDF_VARCHAR * city,
SQLUDF_INTEGER * temp_in_f,
SQLUDF_INTEGER * humidity,
SQLUDF_VARCHAR * wind,
SQLUDF_INTEGER * wind_velocity,
SQLUDF_DOUBLE * barometer,
SQLUDF_VARCHAR * forecast,
/* You may want to add more fields here */
/* Return row field null indicators */
SQLUDF_NULLIND * city_ind,
SQLUDF_NULLIND * temp_in_f_ind,
SQLUDF_NULLIND * humidity_ind,
SQLUDF_NULLIND * wind_ind,
SQLUDF_NULLIND * wind_velocity_ind,
SQLUDF_NULLIND * barometer_ind,
SQLUDF_NULLIND * forecast_ind,
/* You may want to add more field indicators here */
/* UDF always-present (trailing) input arguments */
SQLUDF_TRAIL_ARGS_ALL
) {
scratch_area * save_area ;
char line_buf[81] ;
int line_buf_pos ;
/* SQLUDF_SCRAT is part of SQLUDF_TRAIL_ARGS_ALL */
/* Preserve information from one function call to the next call */
save_area = ( scratch_area * ) ( SQLUDF_SCRAT->data ) ;
/* SQLUDF_CALLT is part of SQLUDF_TRAIL_ARGS_ALL */
switch( SQLUDF_CALLT ) {
/* First call UDF: Open table and fetch first row */
case SQL_TF_OPEN:
/* If you use a weather data text file specify full path */
/* save_area->file_ptr = fopen("/sqllib/samples/c/tblsrv.dat",
"r"); */
save_area->file_pos = 0 ;
break ;
/* Normal call UDF: Fetch next row */
case SQL_TF_FETCH:
/* If you use a weather data text file */
/* memset(line_buf, '\0', 81); */
/* if (fgets(line_buf, 80, save_area->file_ptr) == NULL) { */
if ( weather_data[save_area->file_pos] == ( char * ) 0 ) {
/* SQLUDF_STATE is part of SQLUDF_TRAIL_ARGS_ALL */
strcpy( SQLUDF_STATE, "02000" ) ;
break ;
}
memset( line_buf, '\0', 81 ) ;
strcpy( line_buf, weather_data[save_area->file_pos] ) ;
line_buf[3] = '\0' ;
/* Clean all field data and field null indicator data */
clean_fields( 0 ) ;
/* Fills city field null indicator data */
fields[0].fld_ind = SQL_NOTNULL ;
/* Find a full city name using a short name */
/* Fills city field data */
if ( get_name( line_buf, fields[0].fld_field ) == 0 ) {
save_area->file_pos++ ;
/* If you use a weather data text file */
/* memset(line_buf, '\0', 81); */
/* if (fgets(line_buf, 80, save_area->file_ptr) == NULL) { */
if ( weather_data[save_area->file_pos] == ( char * ) 0 ) {
/* SQLUDF_STATE is part of SQLUDF_TRAIL_ARGS_ALL */
strcpy( SQLUDF_STATE, "02000" ) ;
break ;
}
memset( line_buf, '\0', 81 ) ;
strcpy( line_buf, weather_data[save_area->file_pos] ) ;
line_buf_pos = strlen( line_buf ) ;
while ( line_buf_pos > 0 ) {
if ( line_buf[line_buf_pos] >= ' ' )
line_buf_pos = 0 ;
else {
line_buf[line_buf_pos] = '\0' ;
line_buf_pos-- ;
}
}
}
/* Fills field data and field null indicator data ... */
/* ... for selected city from text weather data */
get_value( line_buf, 1 ) ; /* Skips city field */
/* Builds return row fields */
strcpy( city, fields[0].fld_field ) ;
memcpy( (void *) temp_in_f,
fields[1].fld_field,
sizeof( SQLUDF_INTEGER ) ) ;
memcpy( (void *) humidity,
fields[2].fld_field,
sizeof( SQLUDF_INTEGER ) ) ;
strcpy( wind, fields[3].fld_field ) ;
memcpy( (void *) wind_velocity,
fields[4].fld_field,
sizeof( SQLUDF_INTEGER ) ) ;
memcpy( (void *) barometer,
fields[5].fld_field,
sizeof( SQLUDF_DOUBLE ) ) ;
strcpy( forecast, fields[6].fld_field ) ;
/* Builds return row field null indicators */
memcpy( (void *) city_ind,
&(fields[0].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) temp_in_f_ind,
&(fields[1].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) humidity_ind,
&(fields[2].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) wind_ind,
&(fields[3].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) wind_velocity_ind,
&(fields[4].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) barometer_ind,
&(fields[5].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) forecast_ind,
&(fields[6].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
/* Next city weather data */
save_area->file_pos++ ;
break ;
/* Special last call UDF for cleanup (no real args!): Close table */
case SQL_TF_CLOSE:
/* If you use a weather data text file */
/* fclose(save_area->file_ptr); */
/* save_area->file_ptr = NULL; */
save_area->file_pos = 0 ;
break ;
}
}