/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: udfcli.sqc
**
** SAMPLE: Call a variety of types of user-defined functions
**
** This is the client program for udfsrv.c.
**
** SQL STATEMENTS USED:
** CLOSE
** COMMIT
** CREATE DISTINCT TYPE
** CREATE FUNCTION
** CREATE TABLE
** DECLARE CURSOR
** DROP DISTINCT TYPE
** DROP FUNCTION
** DROP TABLE
** EXECUTE IMMEDIATE
** FETCH
** PREPARE
**
** STRUCTURES USED:
** sqlca
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlenv.h>
#include "utilemb.h"
/* Example directory path used in TblUDFClobFromFile UDF */
#if(defined(DB2NT))
#define TESTDIRPATH "c:\\My Download Files\\"
#else
#define TESTDIRPATH "/usr/bin/"
#endif
/* scalar UDFs */
int ExternalScalarUDFUse(void);
int ExternalScratchpadScalarUDFUse(void);
int ExternalClobScalarUDFUse(void);
int ExternalScalarUDFReturningErrorUse(void);
/* column UDFs */
int SourcedColumnUDFUse(void);
/* table UDFs */
int ExternalTableUDFUse(void);
int ExternalClobTableUDFuse(void);
EXEC SQL BEGIN DECLARE SECTION;
char name[15];
char job[15];
double salary;
double newSalary;
sqlint32 counter;
char empno[7];
char resume_format[7];
sqlint32 numWords;
double comm;
short salaryInd;
sqlint32 maxCustNum;
char strStmt[256];
char fname[200];
SQL TYPE IS CLOB (200000) clobFile;
short clobInd;
EXEC SQL END DECLARE SECTION;
int main(int argc, char *argv[])
{
int rc = 0;
char dbAlias[SQL_ALIAS_SZ + 1];
char user[USERID_SZ + 1];
char pswd[PSWD_SZ + 1];
/* check the command line arguments */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
printf("\nTHIS SAMPLE SHOWS HOW TO WORK WITH UDFs.\n");
/* connect to database */
rc = DbConn(dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
/* scalar UDFs */
rc = ExternalScalarUDFUse();
rc = ExternalScratchpadScalarUDFUse();
rc = ExternalClobScalarUDFUse();
rc = ExternalScalarUDFReturningErrorUse();
/* column UDFs */
rc = SourcedColumnUDFUse();
/* table UDFs */
rc = ExternalTableUDFUse();
rc = ExternalClobTableUDFUse();
/* disconnect from database */
rc = DbDisconn(dbAlias);
return 0;
} /* main */
int ExternalScalarUDFUse()
{
struct sqlca sqlca;
int rc = 0;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS\n");
printf(" CREATE FUNCTION\n");
printf(" COMMIT\n");
printf(" SELECT\n");
printf(" DROP FUNCTION\n");
printf("TO WORK WITH SCALAR UDF:\n");
/* drop scalar UDF, if exists */
printf("\n DROP the scalar UDF, if exists.\n");
EXEC SQL DROP FUNCTION ScalarUDF;
/* register scalar UDF */
printf("\n Register the scalar UDF.\n");
EXEC SQL CREATE FUNCTION ScalarUDF(CHAR(5), DOUBLE)
RETURNS DOUBLE
EXTERNAL NAME 'udfsrv!ScalarUDF'
FENCED
CALLED ON NULL INPUT
NOT VARIANT
NO SQL
PARAMETER STYLE SQL
LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("scalar UDF -- register");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* use scalar UDF */
printf("\n Use the scalar UDF:\n");
printf(" SELECT name, job, salary, ScalarUDF(job, salary)\n");
printf(" FROM staff\n");
printf(" WHERE name LIKE 'S%%'\n");
strcpy(strStmt, "SELECT name, job, salary, ScalarUDF(job, salary) "
" FROM staff "
" WHERE name LIKE 'S%' ");
EXEC SQL PREPARE stmt1 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
/* declare cursor */
EXEC SQL DECLARE c1 CURSOR FOR stmt1;
/* open cursor */
EXEC SQL OPEN c1;
EMB_SQL_CHECK("cursor -- open");
/* fetch cursor */
printf("\n Fetch each row and display.\n");
printf(" NAME JOB SALARY NEW_SALARY\n");
printf(" ---------- ------- -------- ----------\n");
EXEC SQL FETCH c1 INTO :name, :job, :salary, :newSalary;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
printf(" %-10s %-7s %-7.2f %-7.2f", name, job, salary, newSalary);
printf("\n");
EXEC SQL FETCH c1 INTO :name, :job, :salary, :newSalary;
EMB_SQL_CHECK("cursor -- fetch");
}
/* close cursor */
EXEC SQL CLOSE c1;
EMB_SQL_CHECK("cursor -- close");
/* drop scalar UDF */
printf("\n DROP the scalar UDF.\n");
EXEC SQL DROP FUNCTION ScalarUDF;
EMB_SQL_CHECK("scalar UDF -- drop");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* ExternalScalarUDFUse */
int ExternalScratchpadScalarUDFUse()
{
struct sqlca sqlca;
int rc = 0;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS\n");
printf(" CREATE FUNCTION\n");
printf(" COMMIT\n");
printf(" SELECT\n");
printf(" DROP FUNCTION\n");
printf("TO WORK WITH SCRATCHPAD SCALAR UDF:\n");
/* drop SCRATCHPAD scalar UDF, if exists */
printf("\n DROP the SCRATCHPAD scalar UDF, if exists.\n");
EXEC SQL DROP FUNCTION ScratchpadScUDF;
/* register SCRATCHPAD scalar UDF */
printf("\n Register the SCRATCHPAD scalar UDF.\n");
EXEC SQL CREATE FUNCTION ScratchpadScUDF()
RETURNS INTEGER
EXTERNAL NAME 'udfsrv!ScratchpadScUDF'
FENCED
SCRATCHPAD 10
FINAL CALL
VARIANT
NO SQL
PARAMETER STYLE SQL
LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("SCRATCHPAD scalar UDF -- register");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* use SCRATCHPAD scalar UDF */
printf("\n Use the SCRATCHPAD scalar UDF:\n");
printf(" SELECT ScratchpadScUDF(), name, job\n");
printf(" FROM staff\n");
printf(" WHERE name LIKE 'S%%'\n");
strcpy(strStmt, "SELECT ScratchpadScUDF(), name, job "
" FROM staff "
" WHERE name LIKE 'S%' ");
EXEC SQL PREPARE stmt2 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
/* declare cursor */
EXEC SQL DECLARE c2 CURSOR FOR stmt2;
/* open cursor */
EXEC SQL OPEN c2;
EMB_SQL_CHECK("cursor -- open");
/* fetch cursor */
printf("\n Fetch each row and display.\n");
printf(" COUNTER NAME JOB \n");
printf(" ------- ---------- -------\n");
EXEC SQL FETCH c2 INTO :counter, :name, :job;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
printf(" %7d %-10s %-7s", counter, name, job);
printf("\n");
EXEC SQL FETCH c2 INTO :counter, :name, :job;
EMB_SQL_CHECK("cursor -- fetch");
}
/* close cursor */
EXEC SQL CLOSE c2;
EMB_SQL_CHECK("cursor -- close");
/* drop SCRATCHPAD scalar UDF */
printf("\n DROP the SCRATCHPAD scalar UDF.\n");
EXEC SQL DROP FUNCTION ScratchpadScUDF;
EMB_SQL_CHECK("SCRATCHPAD scalar UDF -- drop");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* ScratchpadScUDFUse */
int ExternalClobScalarUDFUse()
{
struct sqlca sqlca;
int rc = 0;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS\n");
printf(" CREATE FUNCTION\n");
printf(" COMMIT\n");
printf(" SELECT\n");
printf("TO WORK WITH CLOB SCALAR UDF:\n");
/* drop CLOB scalar UDF, if exists */
printf("\n DROP the CLOB scalar UDF, if exists.\n");
EXEC SQL DROP FUNCTION ClobScalarUDF;
/* register CLOB scalar UDF */
printf("\n Register the CLOB scalar UDF.\n");
EXEC SQL CREATE FUNCTION ClobScalarUDF(CLOB(5 K))
RETURNS INTEGER
EXTERNAL NAME 'udfsrv!ClobScalarUDF'
FENCED
NOT VARIANT
NO SQL
PARAMETER STYLE SQL
LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("CLOB scalar UDF -- register");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* use CLOB scalar UDF */
printf("\n Use the CLOB scalar UDF:\n");
printf(" SELECT empno, resume_format, ClobScalarUDF(resume)\n");
printf(" FROM emp_resume\n");
printf(" WHERE resume_format = 'ascii'\n");
strcpy(strStmt, "SELECT empno, resume_format, ClobScalarUDF(resume) "
" FROM emp_resume "
" WHERE resume_format = 'ascii' ");
EXEC SQL PREPARE stmt3 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
/* declare cursor */
EXEC SQL DECLARE c3 CURSOR FOR stmt3;
/* open cursor */
EXEC SQL OPEN c3;
EMB_SQL_CHECK("cursor -- open");
/* fetch cursor */
printf("\n Fetch each row and display.\n");
printf(" EMPNO RESUME_FORMAT NUM.WORDS\n");
printf(" ------- ------------- ---------\n");
EXEC SQL FETCH c3 INTO :empno, :resume_format, :numWords;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
printf(" %-7s %-13s %ld", empno, resume_format, numWords);
printf("\n");
EXEC SQL FETCH c3 INTO :empno, :resume_format, :numWords;
EMB_SQL_CHECK("cursor -- fetch");
}
/* close cursor */
EXEC SQL CLOSE c3;
EMB_SQL_CHECK("cursor -- close");
/* drop CLOB scalar UDF */
printf("\n DROP the CLOB scalar UDF.\n");
EXEC SQL DROP FUNCTION ClobScalarUDF;
EMB_SQL_CHECK("CLOB scalar UDF -- drop");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* ExternalClobScalarUDFUse */
int ExternalScalarUDFReturningErrorUse()
{
struct sqlca sqlca;
int rc = 0;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS\n");
printf(" CREATE FUNCTION\n");
printf(" COMMIT\n");
printf(" SELECT\n");
printf(" DROP FUNCTION\n");
printf("TO WORK WITH SCALAR UDF THAT RETURNS ERROR:\n");
/* drop scalar UDF that returns errors, if exists */
printf("\n DROP the scalar UDF that returns error, if exists.\n");
EXEC SQL DROP FUNCTION ScUDFReturningErr;
/* register scalar UDF that returns errors */
printf("\n Register the scalar UDF that returns error.\n");
EXEC SQL CREATE FUNCTION ScUDFReturningErr(DOUBLE, DOUBLE)
RETURNS DOUBLE
EXTERNAL NAME 'udfsrv!ScUDFReturningErr'
FENCED
NOT VARIANT
NO SQL
PARAMETER STYLE SQL
LANGUAGE C
NO EXTERNAL ACTION;
EMB_SQL_CHECK("scalar UDF -- register");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* use scalar UDF that returns errors */
printf("\n Use the scalar UDF that returns error:\n");
printf(" SELECT name, job, ScUDFReturningErr(salary, 0.00)\n");
printf(" FROM staff\n");
printf(" WHERE name LIKE 'S%%'\n");
strcpy(strStmt, "SELECT name, job, ScUDFReturningErr(salary, 0.00) "
" FROM staff "
" WHERE name LIKE 'S%' ");
EXEC SQL PREPARE stmt4 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
/* declare cursor */
EXEC SQL DECLARE c4 CURSOR FOR stmt4;
/* open cursor */
EXEC SQL OPEN c4;
EMB_SQL_CHECK("cursor -- open");
/* fetch cursor */
printf("\n Fetch each row and display.\n");
printf(" NAME JOB COMM \n");
printf(" ---------- ------- --------\n");
EXEC SQL FETCH c4 INTO :name, :job, :comm;
EXPECTED_ERR_CHECK("cursor -- fetch");
if (sqlca.sqlcode < 0)
{
TransRollback();
return 1;
}
while (sqlca.sqlcode != 100)
{
printf(" %-10s %-7s %-7.2f", name, job, comm);
printf("\n");
EXEC SQL FETCH c4 INTO :name, :job, :comm;
EMB_SQL_CHECK("cursor -- fetch");
}
/* close cursor */
EXEC SQL CLOSE c4;
EMB_SQL_CHECK("cursor -- close");
/* drop scalar UDF that returns error */
printf("\n DROP the scalar UDF that returns error.\n");
EXEC SQL DROP FUNCTION ScUDFReturningErr;
EMB_SQL_CHECK("scalar UDF -- drop");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* ExternalScalarUDFReturningErrorUse */
int SourcedColumnUDFUse()
{
struct sqlca sqlca;
int rc = 0;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS\n");
printf(" CREATE DISTINCT TYPE\n");
printf(" CREATE FUNCTION\n");
printf(" CREATE TABLE\n");
printf(" COMMIT\n");
printf(" SELECT INTO\n");
printf(" DROP TABLE\n");
printf(" DROP FUNCTION\n");
printf(" DROP DISTINCT TYPE\n");
printf("TO WORK WITH SOURCED COLUMN UDF:\n");
printf("\n DROP the table 'customer', if exists.\n");
EXEC SQL DROP TABLE customer;
strcpy(strStmt, "DROP FUNCTION MAX(cnum)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
printf("\n DROP DISTINCT TYPE cnum, if exists.\n");
EXEC SQL DROP DISTINCT TYPE cnum;
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* create distinct type */
printf("\n CREATE DISTINCT TYPE cnum AS INTEGER WITH COMPARISONS\n");
EXEC SQL CREATE DISTINCT TYPE cnum AS INTEGER WITH COMPARISONS;
EMB_SQL_CHECK("distinct type -- create");
/* create sourced column UDF */
printf("\n CREATE FUNCTION MAX(cnum) RETURNS cnum"
" SOURCE SYSIBM.MAX(INTEGER)\n");
EXEC SQL CREATE FUNCTION MAX(cnum) RETURNS cnum SOURCE SYSIBM.MAX(INTEGER);
EMB_SQL_CHECK("sourced column UDF -- create");
/* create table that uses the distinct type */
printf("\n CREATE TABLE customer(custNum CNUM NOT NULL,"
"\n custName CHAR(30) NOT NULL)\n");
EXEC SQL CREATE TABLE customer(custNum CNUM NOT NULL,
custName CHAR(30) NOT NULL);
EMB_SQL_CHECK("table that uses distinct type -- create");
/* populate customer table */
printf("\n INSERT INTO CUSTOMER VALUES(CAST(1 AS CNUM), 'JOHN WALKER'),"
"\n (CAST(2 AS CNUM), 'BRUCE ADAMSON'),"
"\n (CAST(3 AS CNUM), 'SALLY KWAN')\n");
strcpy(strStmt,
"INSERT INTO CUSTOMER VALUES(CAST(1 AS CNUM), 'JOHN WALKER'), "
" (CAST(2 AS CNUM), 'BRUCE ADAMSON'), "
" (CAST(3 AS CNUM), 'SALLY KWAN') ");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("customer table -- populate");
/* commit transaction */
printf("\n COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* use sourced column UDF */
printf("\n Use the sourced column UDF:");
printf("\n SELECT CAST(MAX(custNum) AS INTEGER) FROM customer\n");
strcpy(strStmt, "SELECT CAST(MAX(custNum) AS INTEGER) FROM customer ");
EXEC SQL PREPARE stmt7 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
EXEC SQL DECLARE c7 CURSOR FOR stmt7;
EXEC SQL OPEN c7;
EMB_SQL_CHECK("cursor -- open");
EXEC SQL FETCH c7 INTO :maxCustNum;
EMB_SQL_CHECK("cursor -- fetch");
printf("\n MAX(custNum) is: %d\n", maxCustNum);
EXEC SQL CLOSE c7;
EMB_SQL_CHECK("cursor -- close");
/* drop customer table */
printf("\n DROP TABLE customer.\n");
EXEC SQL DROP TABLE customer;
EMB_SQL_CHECK("customer table -- drop");
/* drop sourced column UDF */
printf("\n DROP FUNCTION MAX(cnum)\n");
strcpy(strStmt, "DROP FUNCTION MAX(cnum)");
EXEC SQL EXECUTE IMMEDIATE :strStmt;
EMB_SQL_CHECK("sourced column UDF -- drop");
/* drop cnum distinct type */
printf("\n DROP DISTINCT TYPE cnum\n");
EXEC SQL DROP DISTINCT TYPE cnum;
EMB_SQL_CHECK("cnum distinct type -- drop");
/* commit transaction */
printf("\n COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* SourcedColumnUDFUse */
int ExternalTableUDFUse()
{
struct sqlca sqlca;
int rc = 0;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS\n");
printf(" CREATE FUNCTION\n");
printf(" COMMIT\n");
printf(" SELECT\n");
printf(" DROP FUNCTION\n");
printf("TO WORK WITH TABLE UDF:\n");
/* drop table UDF, if exists */
printf("\n DROP FUNCTION TableUDF, if exists.\n");
EXEC SQL DROP FUNCTION TableUDF;
/* register table UDF */
printf("\n Register the table UDF.\n");
EXEC SQL CREATE FUNCTION TableUDF(DOUBLE)
RETURNS TABLE(name VARCHAR(20),
job VARCHAR(20),
salary DOUBLE)
EXTERNAL NAME 'udfsrv!TableUDF'
LANGUAGE C
PARAMETER STYLE SQL
NOT DETERMINISTIC
FENCED
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD 10
FINAL CALL DISALLOW
PARALLEL NO DBINFO;
EMB_SQL_CHECK("table UDF -- register");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* use table UDF */
printf("\n Use the table UDF:");
printf("\n SELECT udfTable.name, udfTable.job, udfTable.salary");
printf("\n FROM TABLE(TableUDF(1.5))");
printf("\n AS udfTable\n");
strcpy(strStmt, "SELECT udfTable.name, udfTable.job, udfTable.salary "
" FROM TABLE(TableUDF(1.5)) "
" AS udfTable ");
EXEC SQL PREPARE stmt5 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
/* delcare cursor */
EXEC SQL DECLARE c5 CURSOR FOR stmt5;
/* open cursor */
EXEC SQL OPEN c5;
EMB_SQL_CHECK("cursor -- open");
/* fetch cursor */
printf("\n Fetch each row and display.\n");
printf(" NAME JOB SALARY \n");
printf(" ---------- ------- ---------\n");
EXEC SQL FETCH c5 INTO :name, :job, :salary:salaryInd;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
printf(" %-10s %-7s", name, job);
if (salaryInd >= 0)
{
printf(" %7.2f", salary);
}
else
{
printf(" %8s", "-");
}
printf("\n");
EXEC SQL FETCH c5 INTO :name, :job, :salary:salaryInd;
EMB_SQL_CHECK("cursor -- fetch");
}
/* close cursor */
EXEC SQL CLOSE c5;
EMB_SQL_CHECK("cursor -- close");
/* drop table UDF */
printf("\n DROP FUNCTION TableUDF.\n");
EXEC SQL DROP FUNCTION TableUDF;
EMB_SQL_CHECK("table UDF -- drop");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* TableUDFUse */
int ExternalClobTableUDFUse()
{
struct sqlca sqlca;
int charNmb = 0;
int rc = 0;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS\n");
printf(" CREATE FUNCTION\n");
printf(" COMMIT\n");
printf(" SELECT\n");
printf("TO WORK WITH CLOB TABLE UDF:\n");
/* drop CLOB scalar UDF, if exists */
printf("\n DROP the CLOB table UDF, if exists.\n");
EXEC SQL DROP FUNCTION TblUDFClobFromFile;
/* register CLOB table UDF */
printf("\n Register the CLOB table UDF.\n");
EXEC SQL CREATE FUNCTION TblUDFClobFromFile (dir varchar(40))
RETURNS TABLE (fname varchar(200),
file clob(200000))
EXTERNAL NAME 'udfsrv!TblUDFClobFromFile'
SPECIFIC TBLUDFCLOBFROMFILE
LANGUAGE C PARAMETER STYLE db2sql
SCRATCHPAD FINAL CALL
FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL;
EMB_SQL_CHECK("CLOB table UDF -- register");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
/* use CLOB table UDF */
printf("\n Notes - regarding use of the CLOB table UDF: \n");
printf(" ----- \n");
printf(" 1) USAGE \n");
printf(" If no data is returned, verify that the input \n");
printf(" parameter directory name is valid for your system.\n");
printf(" If not, edit the #define TESTDIRPATH to one that \n");
printf(" exists for your system or edit the input parameter\n");
printf(" directly in the SQL statement that follows below. \n");
printf(" \n");
printf(" 2) SECURITY: \n");
printf(" Because this table function reads files \n");
printf(" residing on the database server, caution should \n");
printf(" be taken when granting execute priviliges of this \n");
printf(" function to database users. \n");
printf("\n Use the CLOB table UDF:\n");
printf(" SELECT substr(fname, 1, 20), substr(file, 1, 10)\n");
printf(" FROM table (TblUDFClobFromFile('%s')) as T;\n", TESTDIRPATH);
/* Select sub-strings of the columns fname (varchar) and file (clob) */
/* from the table returned by calling TblUDFClobFromFile. One row is */
/* returned per directory entry found in the directory specified by */
/* the input parameter. */
sprintf(strStmt, "SELECT substr(fname, 1, 20), substr(file, 1, 10)"
"FROM table (TblUDFClobFromFile('%s')) as T", TESTDIRPATH);
EXEC SQL PREPARE stmt6 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
/* declare cursor */
EXEC SQL DECLARE c6 CURSOR FOR stmt6;
/* open cursor */
EXEC SQL OPEN c6;
EMB_SQL_CHECK("cursor -- open");
/* fetch cursor */
printf("\n Fetch each row and display.\n");
printf(" FNAME FILE(first 10 chars)\n");
printf(" ------- --------------------\n");
EXEC SQL FETCH c6 INTO :fname, :clobFile:clobInd;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
/* If CLOB is NULL, output a "NULL" string
Otherwise, output data from the clob*/
if (clobInd < 0)
{
printf(" %s %s", fname, "NULL");
printf("\n");
}
else
{
/* print out fname and max 10 chars of clobFile */
printf(" %s ", fname);
for (charNmb = 0;
((charNmb < 10) && (charNmb < clobFile.length));
charNmb++)
{
printf("%c", clobFile.data[charNmb]);
}
printf ("\n");
}
EXEC SQL FETCH c6 INTO :fname, :clobFile:clobInd;
EMB_SQL_CHECK("cursor -- fetch");
}
/* close cursor */
EXEC SQL CLOSE c6;
EMB_SQL_CHECK("cursor -- close");
/* drop CLOB scalar UDF */
printf("\n DROP the CLOB table UDF.\n");
EXEC SQL DROP FUNCTION TblUDFClobFromFile;
EMB_SQL_CHECK("CLOB table UDF -- drop");
printf("\n COMMIT.\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* ExternalClobTableUDFUse */