friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.sqlp_5.4.0.1/rbafyudfweather.htm

436 lines
17 KiB
HTML
Raw Permalink Blame History

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-us" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow" />
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="DC.Type" content="reference" />
<meta name="DC.Title" content="Example: Weather table function" />
<meta name="abstract" content="This is an example table function that returns weather information for various cities in the United States." />
<meta name="description" content="This is an example table function that returns weather information for various cities in the United States." />
<meta name="DC.subject" content="examples, weather table UDF, CREATE FUNCTION statement" />
<meta name="keywords" content="examples, weather table UDF, CREATE FUNCTION statement" />
<meta name="DC.Relation" scheme="URI" content="rbafyuwexam.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rbafyudfweather" />
<meta name="DC.Language" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
<link rel="stylesheet" type="text/css" href="./ic.css" />
<title>Example: Weather table function</title>
</head>
<body id="rbafyudfweather"><a name="rbafyudfweather"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">Example: Weather table function</h1>
<div><p>This is an example table function that returns weather information
for various cities in the United States. </p>
<div class="section"> <div class="note"><span class="notetitle">Note:</span> By using the code examples, you agree to the terms of the <a href="codedisclaimer.htm">Code license and disclaimer information</a>.</div>
</div>
<div class="section"><p>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.</p>
<pre>#include &lt;stdlib.h&gt;
#include &lt;string.h&gt;
#include &lt;stdio.h&gt;
#include &lt;sqludf.h&gt; /* 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 */ <strong><em>(See note 1)</em></strong>
/* 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 = &amp;fields[field_pos] ;
memset( field_buf, '\0', 31 ) ;
memcpy( field_buf,
( value + field-&gt;fld_offset ),
field-&gt;fld_length ) ;
buf_pos = field-&gt;fld_length ;
while ( ( buf_pos &gt; 0 ) &amp;&amp;
( field_buf[buf_pos] == ' ' ) )
field_buf[buf_pos--] = '\0' ;
buf_pos = 0 ;
while ( ( buf_pos &lt; field-&gt;fld_length ) &amp;&amp;
( field_buf[buf_pos] == ' ' ) )
buf_pos++ ;
if ( strlen( ( char * ) ( field_buf + buf_pos ) ) &gt; 0 ||
strcmp( ( char * ) ( field_buf + buf_pos ), "n/a") != 0 ) {
field-&gt;fld_ind = SQL_NOTNULL ;
/* Text to SQL type conversion */
switch( field-&gt;fld_type ) {
case SQL_TYP_VARCHAR:
strcpy( field-&gt;fld_field,
( char * ) ( field_buf + buf_pos ) ) ;
break ;
case SQL_TYP_INTEGER:
int_ptr = ( int * ) field-&gt;fld_field ;
*int_ptr = atoi( ( char * ) ( field_buf + buf_pos ) ) ;
break ;
case SQL_TYP_FLOAT:
double_ptr = ( double * ) field-&gt;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-&gt;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-&gt;file_ptr = fopen("tblsrv.dat","r"); */
save_area-&gt;file_pos = 0 ;
break ;
/* Normal call UDF: Fetch next row */ <strong><em>(See note 2)</em></strong>
case SQL_TF_FETCH:
/* If you use a weather data text file */
/* memset(line_buf, '\0', 81); */
/* if (fgets(line_buf, 80, save_area-&gt;file_ptr) == NULL) { */
if ( weather_data[save_area-&gt;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-&gt;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-&gt;file_pos++ ;
/* If you use a weather data text file */
/* memset(line_buf, '\0', 81); */
/* if (fgets(line_buf, 80, save_area-&gt;file_ptr) == NULL) { */
if ( weather_data[save_area-&gt;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-&gt;file_pos] ) ;
line_buf_pos = strlen( line_buf ) ;
while ( line_buf_pos &gt; 0 ) {
if ( line_buf[line_buf_pos] &gt;= ' ' )
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,
&amp;(fields[0].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) temp_in_f_ind,
&amp;(fields[1].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) humidity_ind,
&amp;(fields[2].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) wind_ind,
&amp;(fields[3].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) wind_velocity_ind,
&amp;(fields[4].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) barometer_ind,
&amp;(fields[5].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) forecast_ind,
&amp;(fields[6].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
/* Next city weather data */
save_area-&gt;file_pos++ ;
break ;
/* Special last call UDF for clean up (no real args!): Close table */ <strong><em>(See note 3)</em></strong>
case SQL_TF_CLOSE:
/* If you use a weather data text file */
/* fclose(save_area-&gt;file_ptr); */
/* save_area-&gt;file_ptr = NULL; */
save_area-&gt;file_pos = 0 ;
break ;
}
}
</pre>
</div>
<div class="section"><p>Referring to the embedded numbers in this UDF code, observe that:</p>
</div>
<div class="section"> <ol><li>The scratchpad is defined. The <samp class="codeph">row</samp> variable is initialized
on the OPEN call, and the <samp class="codeph">iptr</samp> array and <samp class="codeph">nbr_rows</samp> variable
are filled in by the <em>mystery</em> function at open time.</li>
<li>FETCH traverses the <samp class="codeph">iptr</samp> array, using row as an index,
and moves the values of interest from the current element of <samp class="codeph">iptr</samp> to
the location pointed to by <samp class="codeph">out_c1, out_c2</samp>, and <samp class="codeph">out_c3</samp> result
value pointers.</li>
<li>Finally, CLOSE frees the storage acquired by OPEN and anchored in the
scratchpad.</li>
</ol>
</div>
<div class="section"><p>Following is the CREATE FUNCTION statement for this UDF:</p>
<pre> <strong>CREATE FUNCTION</strong> tfweather_u()
<strong>RETURNS TABLE</strong> (CITY <strong>VARCHAR</strong>(25),
TEMP_IN_F <strong>INTEGER</strong>,
HUMIDITY <strong>INTEGER</strong>,
WIND <strong>VARCHAR</strong>(5),
WIND_VELOCITY <strong>INTEGER</strong>,
BAROMETER <strong>FLOAT</strong>,
FORECAST <strong>VARCHAR</strong>(25))
<strong>SPECIFIC</strong> tfweather_u
<strong> DISALLOW PARALLEL
NOT FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD
NO FINAL CALL
LANGUAGE C
PARAMETER STYLE DB2SQL
EXTERNAL NAME</strong> 'LIB1/WEATHER(weather)';</pre>
</div>
<div class="section"><p>Referring to the embedded numbered notes, observe
that:</p>
<ul><li>It does not take any input, and returns 7 output columns.</li>
<li>SCRATCHPAD is specified, so DB2<sup>®</sup> allocates, properly initializes and
passes the scratchpad argument.</li>
<li>NO FINAL CALL is specified.</li>
<li>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.</li>
<li>DISALLOW PARALLEL is required for table functions.</li>
<li>CARDINALITY 100 is an estimate of the expected number of rows returned,
provided to the DB2 optimizer.</li>
<li>DBINFO is not used, and the optimization to only return the columns needed
by the particular statement referencing the function is not implemented.</li>
<li>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.</li>
</ul>
</div>
<div class="section"><p>To select all of the rows generated by this table function, use
the following query:</p>
<pre><strong>SELECT</strong> *
<strong>FROM TABLE</strong> (tfweather_u())x</pre>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafyuwexam.htm" title="These examples show how to implement UDF code by using SQL functions and external functions.">Examples: UDF code</a></div>
</div>
</div>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink