/******************************************************************************
**
** Source File Name = calludf.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** This program is an example of how UDFs are implemented in
** order to modify selection queries.
**
** STRUCTURES USED :
** sqlca
**
** APIs USED :
**
** FUNCTIONS DECLARED :
** 'C' COMPILER LIBRARY :
** stdio.h - printf
** string.h - fgets, strncpy
**
** DBMS LIBRARY :
** sqlenv.h
**
** Other Function(s) used:
** external : [in the file UTIL.C]
** check_error : Checks for SQLCODE error, and prints out any
** related information available.
** init_da: Initialiaze the memory space for an SQLDA.
** alloc_host_vars: Allocate memory to be filled with SQLDA
** data.
** free_da: Frees up memory which has been allocated
** as an SQLDA structure.
** display_col_titles: Display column titles.
** display_da: Display row information.
**
** EXTERNAL DEPENDENCIES :
** - Ensure existence of database for precompile purposes.
** - Precompile with the SQL precompiler (PREP in DB2)
** - Bind to a database (BIND in DB2)
** - 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 <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlda.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int output_statement (char *);
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
char statement[1000];
EXEC SQL END DECLARE SECTION;
printf( "\nSample C program: CALLUDF\n");
if (argc == 1) {
EXEC SQL CONNECT TO sample; /* :rk.5:erk. */
EMB_SQL_CHECK("CONNECT TO SAMPLE"); /* :rk.6:erk. */
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd; /* :rk.5:erk. */
EMB_SQL_CHECK("CONNECT TO SAMPLE"); /* :rk.6:erk. */
}
else {
printf ("\nUSAGE: calludf [userid passwd]\n\n");
return 1;
} /* endif */
EXEC SQL DROP FUNCTION increase;
EXEC SQL DROP FUNCTION raisesal;
EXEC SQL DROP FUNCTION wordcount;
EXEC SQL DROP FUNCTION findv;
EXEC SQL DROP FUNCTION fold;
EXEC SQL DROP FUNCTION ctr;
EXEC SQL DROP FUNCTION pos;
EXEC SQL DROP FUNCTION twice;
EXEC SQL CREATE FUNCTION increase (float, float) RETURNS float
FENCED
EXTERNAL NAME 'udf!increase'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CREATE FUNCTION increase");
EXEC SQL CREATE FUNCTION raisesal (float, float) RETURNS float
FENCED
EXTERNAL NAME 'udf!raisesal'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CREATE FUNCTION raisesal");
EXEC SQL CREATE FUNCTION wordcount (CLOB(5K)) RETURNS INT
FENCED
EXTERNAL NAME 'udf!wordcount'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CREATE FUNCTION wordcount");
EXEC SQL CREATE FUNCTION findv (VARCHAR(500)) RETURNS SMALLINT
FENCED
EXTERNAL NAME 'udf!findvwl'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CREATE FUNCTION findv");
EXEC SQL CREATE FUNCTION fold (CLOB(100K), INT) RETURNS CLOB(100K)
FENCED
EXTERNAL NAME 'udf!fold'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CREATE FUNCTION fold");
EXEC SQL CREATE FUNCTION ctr() RETURNS INT
SCRATCHPAD FENCED VARIANT
EXTERNAL NAME 'udf!ctr'
NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CREATE FUNCTION ctr");
EXEC SQL CREATE FUNCTION twice (INT) RETURNS INT
SCRATCHPAD FENCED VARIANT
EXTERNAL NAME 'udf!leni'
NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CREATE FUNCTION twice");
EXEC SQL CREATE FUNCTION pos (CLOB(33k), VARCHAR(50)) RETURNS INT
SCRATCHPAD FENCED VARIANT
EXTERNAL NAME 'udf!ilob'
NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CREATE FUNCTION pos");
strcpy (statement, "select empno, resume_format, wordcount(resume) from "
"emp_resume order by empno, resume_format");
output_statement (statement);
EMB_SQL_CHECK("PROCESS STATEMENT #1");
strcpy (statement, "select deptname, findv(deptname) from org order by deptname");
output_statement (statement);
EMB_SQL_CHECK("PROCESS STATEMENT #2");
strcpy (statement, "select name, id, ctr() from staff order by name, id");
output_statement (statement);
EMB_SQL_CHECK("PROCESS STATEMENT #3");
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
int output_statement (char *sqlInput) {
int counter = 0;
struct sqlda *sqldaPointer;
EXEC SQL BEGIN DECLARE SECTION;
char sqlStatement[256];
EXEC SQL END DECLARE SECTION;
strcpy (sqlStatement, sqlInput);
init_da (&sqldaPointer, 1);
EXEC SQL PREPARE statement1 from :sqlStatement;
EMB_SQL_CHECK("PREPARE");
/* obtain the initial SQLDA, to get the number of columns */
EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer;
/* need to reassign the SQLDA with the correct number of columns to
the SQL statement */
init_da (&sqldaPointer, sqldaPointer->sqld);
EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer;
EMB_SQL_CHECK("DESCRIBE");
/* allocating the proper amount of memory space needed for the
variables */
alloc_host_vars (sqldaPointer);
EXEC SQL DECLARE pcurs CURSOR FOR statement1;
EXEC SQL OPEN pcurs;
EMB_SQL_CHECK("OPEN");
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer;
EMB_SQL_CHECK("FETCH");
/* if the FETCH is successful, obtain data from SQLDA */
/* display the column titles */
display_col_titles (sqldaPointer);
/* display the rows that are fetched */
while (SQLCODE == 0) {
counter++;
display_da (sqldaPointer);
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer;
} /* endwhile */
EXEC SQL CLOSE pcurs;
EMB_SQL_CHECK("CLOSE CURSOR");
printf ("\n %d record(s) selected\n\n", counter);
free_da(sqldaPointer);
return (SQLCODE);
}
/* end of program : calludf.sqc */