Example: Weather table function

 

Suppose that you write a table function that returns weather information for various cities in the United States.

By using the code examples, you agree to the terms of the Code license and disclaimer information.

The weather date for these cities is read in from an external file, as indicated in the comments contained in the example program. The data includes the name of a city followed by its weather information. This pattern is repeated for the other cities.

#include <stdlib.h>
#include <string.h>
#include <stdio.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 */
#define   SQL_TYP_VARCHAR 448 
#define   SQL_TYP_INTEGER 496 
#define   SQL_TYP_FLOAT 480 
 
/* Short and long city name structure */
typedef struct {
  char * city_short ;
  char * city_long ;
} city_area ;
 
/* Scratchpad data */ (See note 1)
/* 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"                 },
  .
  .
  .
  { "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  */
/* need 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",
   .
   .
   .
   "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 /* This is a subroutine. */
/* 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++ ;
    }
    /* can not find such city in the city data */
    strcpy( long_name, "Unknown City" ) ;
    return( -1 ) ;
 
}
 
#ifdef __cplusplus extern "C"
#endif /* This is a subroutine. */
/* 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 /* This is a subroutine. */
/* 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("tblsrv.dat","r"); */
         save_area->file_pos = 0 ;
         break ;
 
    /* Normal call UDF: Fetch next row */ (See note 2)
    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 clean up (no real args!): Close table */ (See note 3)
    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 ;
 
  }
 
}

Referring to the embedded notes in this UDF code, observe that:

1. The scratchpad is defined. The row variable is initialized on the OPEN call, and the iptr array and nbr_rows variable are filled in by the mystery function at open time.

2. FETCH traverses the iptr array, using row as an index, and moves the values of interest from the current element of iptr to the location pointed to by out_c1, out_c2, and out_c3 result value pointers.

3. Finally, CLOSE frees the storage acquired by OPEN and anchored in the scratchpad.

Following is the CREATE FUNCTION statement for this UDF:

     CREATE FUNCTION tfweather_u()
       RETURNS TABLE (CITY VARCHAR(25),
                      TEMP_IN_F INTEGER,
                      HUMIDITY INTEGER,
                      WIND VARCHAR(5),
                      WIND_VELOCITY INTEGER,
                      BAROMETER FLOAT,
                      FORECAST VARCHAR(25))
       SPECIFIC tfweather_u        DISALLOW PARALLEL        NOT FENCED 
       DETERMINISTIC        NO SQL        NO EXTERNAL ACTION 
       SCRATCHPAD 
       NO FINAL CALL        LANGUAGE C 
       PARAMETER STYLE DB2SQL        EXTERNAL NAME 'LIB1/WEATHER(weather)';

Referring to the embedded numbered notes, observe that:

• It does not take any input, and returns 7 output columns.

• SCRATCHPAD is specified, so DB2^® allocates, properly initializes and passes the scratchpad argument.

• NO FINAL CALL is specified.

• The function is specified as NOT DETERMINISTIC, because it depends on more than the SQL input arguments. That is, it depends on the mystery function and we assume that the content can vary from execution to execution.

• DISALLOW PARALLEL is required for table functions.

• CARDINALITY 100 is an estimate of the expected number of rows returned, provided to the DB2 optimizer.

• DBINFO is not used, and the optimization to only return the columns needed by the particular statement referencing the function is not implemented.

• NOT NULL CALL is specified, so the UDF will not be called if any of its input SQL arguments are NULL, and does not need to check for this condition.

To select all of the rows generated by this table function, use the following query:

SELECT * 
    FROM TABLE (tfweather_u())x

 

Parent topic:

Examples: UDF code