//
// Source File Name = calludf.sqx
//
// Licensed Materials - Property of IBM
//
// (C) COPYRIGHT International Business Machines Corp. 1995, 1997
// All Rights Reserved.
//
// US Government Users Restricted Rights - Use, duplication or
// disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
//
//
// PURPOSE: This sample program demonstrates how to call
// UDF functions in C++.
//
//
// EXTERNAL DEPENDENCIES :
// - Existing database for precompile purposes.
// - Precompile with the SQL precompiler (PREP in DB2)
// - Binding to a database (BIND in DB2)
// - Compiling and linking 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 <iostream.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <sqlutil.h>
#include "util.h"
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
EXEC SQL INCLUDE SQLCA;
#define CHECKERR(CE_STR) check_error (CE_STR, &sqlca)
class Server {
public:
void Connect();
void Connect(char *, char *);
void RegisterUDFs();
void CallUDFs();
void Disconnect();
private:
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
char statement[1000];
EXEC SQL END DECLARE SECTION;
private: // private member functions
void ExecAndPrint(char *);
};
void Server::Connect () {
printf( "Connecting to database SAMPLE with default userid and password...\n");
EXEC SQL CONNECT TO sample;
CHECKERR ("CONNECT TO SAMPLE") ;
printf( "Connected to database SAMPLE \n");
}
void Server::Connect (char *uid, char *pswd) {
strcpy (userid, uid);
strcpy (passwd, pswd);
printf( "Connecting to database SAMPLE... \n");
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
CHECKERR ("CONNECT TO SAMPLE") ;
printf( "Connected to database SAMPLE \n");
}
void Server::Disconnect () {
// Disconnect from Remote Database
printf( "Disconnecting from database SAMPLE... \n");
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
printf( "Disconnected from database SAMPLE \n");
}
void Server::RegisterUDFs () {
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
NOT FENCED
EXTERNAL NAME 'udf!increase'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
CHECKERR ("CREATE FUNCTION increase");
EXEC SQL CREATE FUNCTION raisesal (float, float) RETURNS float
NOT FENCED
EXTERNAL NAME 'udf!raisesal'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
CHECKERR ("CREATE FUNCTION raisesal");
EXEC SQL CREATE FUNCTION wordcount (CLOB(5K)) RETURNS INT
NOT FENCED
EXTERNAL NAME 'udf!wordcount'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
CHECKERR ("CREATE FUNCTION wordcount");
EXEC SQL CREATE FUNCTION findv (VARCHAR(500)) RETURNS SMALLINT
NOT FENCED
EXTERNAL NAME 'udf!findvwl'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
CHECKERR ("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;
CHECKERR ("CREATE FUNCTION fold");
EXEC SQL CREATE FUNCTION ctr() RETURNS INT
SCRATCHPAD NOT FENCED VARIANT
EXTERNAL NAME 'udf!ctr'
NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
CHECKERR ("CREATE FUNCTION ctr");
EXEC SQL CREATE FUNCTION twice (INT) RETURNS INT
SCRATCHPAD NOT FENCED VARIANT
EXTERNAL NAME 'udf!leni'
NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
CHECKERR ("CREATE FUNCTION twice");
EXEC SQL CREATE FUNCTION pos (CLOB(33k), VARCHAR(50)) RETURNS INT
SCRATCHPAD NOT FENCED VARIANT
EXTERNAL NAME 'udf!ilob'
NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION;
CHECKERR ("CREATE FUNCTION pos");
} // RegisterUDFs
void Server::CallUDFs () {
/* calling UDF function: wordcount */
strcpy (statement, "select empno, resume_format, wordcount(resume) from "
"emp_resume order by empno, resume_format");
ExecAndPrint (statement);
CHECKERR ("PROCESS STATEMENT #1");
/* calling UDF function: findv */
strcpy (statement, "select deptname, findv(deptname) from org order by deptname");
ExecAndPrint (statement);
CHECKERR ("PROCESS STATEMENT #2");
/* calling UDF function: ctr */
strcpy (statement, "select name, id, ctr() from staff order by name, id");
ExecAndPrint (statement);
CHECKERR ("PROCESS STATEMENT #3");
} // CallUDFs
void Server::ExecAndPrint (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;
CHECKERR ("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;
CHECKERR ("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;
CHECKERR ("OPEN");
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer;
CHECKERR ("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;
CHECKERR ("CLOSE CURSOR");
printf ("\n %d record(s) selected\n\n", counter);
free_da(sqldaPointer);
} // ExecAndPrint
int main(int argc, char *argv[]) {
Server server;
#ifdef DB268K
/* Before making any API calls for 68K environment,
need to initial the Library Manager */
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
if (argc == 3) {
server.Connect(argv[1], argv[2]);
} else if (argc == 1) {
server.Connect();
} else {
cout << "\nUSAGE: calludf [userid passwd]\n\n";
return 1;
} // end if
server.RegisterUDFs();
printf( "\nCalling UDF functions...\n");
server.CallUDFs();
server.Disconnect();
return 0;
}
// end of program : calludf.sqx