//
// Source File Name = udfcli.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 :
// To demonstrate how to use User-Defined Funtions, It includes scalar UDF
// and sourced UDF in embedded SQL of C++.
// This is a client for the UDFs from CLI udfsrv.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 <sqlca.h>
#include <sqlutil.h>
#include "utilemb.h"
extern "C" {
int printf( const char *,...);
}
EXEC SQL INCLUDE SQLCA;
class CNT {
public:
void Connect();
void Connect(char *, char *);
void Disconnect();
private:
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
};
void CNT::Connect () {
cout << "Connecting to database SAMPLE... \n";
EXEC SQL CONNECT TO sample;
EMB_SQL_CHECK("CONNECT TO SAMPLE") ;
cout << "Connected to database SAMPLE \n";
}
void CNT::Connect (char *uid, char *pswd) {
strcpy (userid, uid);
strcpy (passwd, pswd);
cout << "Connecting to database SAMPLE... \n";
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
EMB_SQL_CHECK("CONNECT TO SAMPLE") ;
cout << "Connected to database SAMPLE \n";
}
void CNT::Disconnect () {
// Disconnect from Remote Database
cout << "Disconnecting from database SAMPLE... \n";
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
cout << "Disconnected from database SAMPLE \n";
}
class udfcli {
public:
int ScalarUDFCall( ) ;
int SourcedUDFUse( ) ;
private:
};
int main(int argc, char *argv[]) {
CNT cnt;
udfcli udfcli;
if (argc == 1) {
cnt.Connect();
} else if (argc == 3) {
cnt.Connect(argv[1], argv[2]);
} else {
cout << "\nUSAGE: udfcli [userid passwd]\n\n";
return 1;
} // end if
// call scalar UDF
udfcli.ScalarUDFCall( ) ;
// Use Sourced UDF function
udfcli.SourcedUDFUse( ) ;
EXEC SQL ROLLBACK;
EMB_SQL_CHECK ("ROLLBACK");
cnt.Disconnect();
return 0;
}
// end of main
/******************************************************************************
** SourcedUDFUse
******************************************************************************/
int udfcli::SourcedUDFUse()
{
int rc = 0;
char * stmt1 =
"CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS";
char * stmt2 =
"CREATE FUNCTION MAX(CNUM) RETURNS CNUM source sysibm.max(integer)";
char * stmt3 = "CREATE TABLE CUSTOMER ( "
"CustNum CNUM NOT NULL, "
"CustName CHAR(30) NOT NULL)" ;
char * stmt4 = "INSERT INTO CUSTOMER VALUES "
" ( CAST(1 AS CNUM), 'JOHN WALKER'), "
" ( CAST(2 AS CNUM), 'BRUCE ADAMSON'), "
" ( CAST(3 AS CNUM), 'SALLY KWAN')" ;
char * stmt5 =
"SELECT CAST(MAX(CustNum) AS INTEGER ) FROM customer " ;
char * stmt6 = "DROP TABLE CUSTOMER" ;
char * stmt7 = "DROP FUNCTION MAX(CNUM)" ;
char * stmt8 = "DROP DISTINCT TYPE CNUM" ;
EXEC SQL BEGIN DECLARE SECTION;
char prep_str[200];
sqlint32 maxCustNum_val;
EXEC SQL END DECLARE SECTION;
cout << sp5 <<"CREATE DISTINCT TYPE CNUM AS INTEGER\n";
cout << sp5 <<"WITH COMPARISONS\n";
EXEC SQL CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS;
EMB_SQL_CHECK ("Create DISTINCT");
/* create a sourced UDF */
cout << " CREATE FUNCTION MAX(CNUM) RETURNS CNUM\n";
cout << " source sysibm.max(integer)\n";
EXEC SQL CREATE FUNCTION MAX(CNUM) RETURNS CNUM source sysibm.max(integer);
EMB_SQL_CHECK ("Create FUNCTION");
/* create a table that uses the distinct type */
cout << sp5 <<"CREATE TABLE CUSTOMER ( \n";
cout << sp8 <<"CustNum CNUM NOT NULL,\n";
cout << sp8 <<"CustName CHAR(30) NOT NULL)\n";
EXEC SQL CREATE TABLE customer (CustNum CNUM NOT NULL,
CustName CHAR(30) NOT NULL);
EMB_SQL_CHECK ("Create TABLE");
/* insert values in table */
cout << sp5 << "INSERT INTO CUSTOMER VALUES\n";
cout << sp8 <<"( CAST(1 AS CNUM), 'JOHN WALKER'),\n";
cout << sp8 <<"( CAST(2 AS CNUM), 'BRUCE ADAMSON'),\n";
cout << sp8 <<"( CAST(3 AS CNUM), 'SALLY KWAN')\n";
strcpy(prep_str,stmt4);
EXEC SQL PREPARE statement1 from :prep_str;
EMB_SQL_CHECK ("PREPARE");
EXEC SQL EXECUTE statement1;
EMB_SQL_CHECK ("Insert TABLE");
/* execute directly the select statement */
cout << sp5 <<"Use the sourced UDF performing this statement:\n";
cout << sp8 <<"SELECT FROM CUSTOMER CAST(MAX(CUSTNUM) AS INTEGER)\n";
strcpy(prep_str,stmt5);
EXEC SQL PREPARE statement1 from :prep_str;
EMB_SQL_CHECK ("PREPARE");
EXEC SQL DECLARE c1 CURSOR FOR statement1;
EXEC SQL OPEN c1 ;
EMB_SQL_CHECK ("OPEN");
EXEC SQL FETCH c1 INTO :maxCustNum_val ;
EMB_SQL_CHECK ("SELECT TABLE");
if (SQLCODE ==0 ){
cout << sp8 << "Max(CustNum) is : " << maxCustNum_val <<endl ;
}
EXEC SQL CLOSE c1;
/* drop the table */
cout << sp5 <<stmt6 <<endl;
EXEC SQL DROP TABLE customer ;
EMB_SQL_CHECK ("DROP TABLE");
/* drop the sourced UDF */
cout << sp5 <<stmt7 <<endl;
strcpy(prep_str,stmt7);
EXEC SQL PREPARE statement1 from :prep_str;
EMB_SQL_CHECK ("PREPARE");
EXEC SQL EXECUTE statement1;
EMB_SQL_CHECK ("DROP FUNCTION");
/* drop the distinct type */
cout << sp5 <<stmt8 <<endl;
EXEC SQL DROP DISTINCT TYPE CNUM ;
EMB_SQL_CHECK ("DROP TYPE");
return(rc);
error:
EXEC SQL WHENEVER SQLERROR continue;
cout << "Error occured SQLCODE = " << SQLCODE << "\n SQLERRMES ="
<<sqlca.sqlerrmc <<endl;
return(rc);
}
/******************************************************************************
** ScalarUDFCall
******************************************************************************/
int udfcli::ScalarUDFCall( )
{
int rc = 0;
char * stmtDrop = "DROP FUNCTION ScalarUDF";
char * stmtRegister =
" CREATE FUNCTION ScalarUDF (CHAR(5), float) RETURNS float "
" FENCED "
" EXTERNAL NAME 'udfsrv!ScalarUDF' "
" NOT VARIANT NO SQL PARAMETER STYLE DB2SQL"
" LANGUAGE C NO EXTERNAL ACTION ";
EXEC SQL BEGIN DECLARE SECTION;
char * stmtSelect =
"SELECT name, job, salary, ScalarUDF( job, salary)"
" FROM staff WHERE name LIKE 'S%' " ;
char name_val[10];
char job_val[6];
double salary_val,newsalary_val;
EXEC SQL END DECLARE SECTION;
/*
EXEC SQL WHENEVER SQLERROR GOTO error;
*/
cout <<"\nUSE THE EMBEDDED SQL \n";
cout <<"-Scalar UDF\n";
cout <<"TO CALL UDFs:\n";
EXEC SQL DROP FUNCTION ScalarUDF;
if (SQLCODE !=0) {
cout << sp5 <<"No function dropped\n";
}
cout << sp5 <<"Register the scalar UDF.\n";
EXEC SQL
CREATE FUNCTION ScalarUDF (CHAR(5), float) RETURNS float
FENCED
EXTERNAL NAME 'udfsrv!ScalarUDF'
NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
NO EXTERNAL ACTION ;
EMB_SQL_CHECK ("Create Function");
cout << sp5 <<"Use the scalar UDF performing this statement:\n";
cout << sp8 <<"SELECT name, job, salary, ScalarUDF( job, salary)\n";
cout << sp8 <<"FROM staff WHERE name LIKE 'S%%'\n";
EXEC SQL PREPARE statement2 from :stmtSelect;
EMB_SQL_CHECK ("PREPARE");
EXEC SQL DECLARE c2 CURSOR FOR statement2;
EXEC SQL OPEN c2 ;
EMB_SQL_CHECK ("OPEN");
/* fetch each row, and display */
cout << sp5 <<"Fetch each row and dispaly.\n";
cout << sp8 <<"NAME JOB SALARY NEW_SALARY\n";
cout << sp8 <<"---------- ------ ---------- ----------\n";
EXEC SQL FETCH c2 INTO :name_val,:job_val,:salary_val,
:newsalary_val;
EMB_SQL_CHECK ("First fetch");
if (SQLCODE == 100)
{ cout << sp5 <<"Data not found.\n";
}
while (SQLCODE == 0 ) {
/*
printf("%s%-10s %-7s %-7.2f %-7.2f\n",
sp8,name_val, job_val, salary_val,newsalary_val);
*/
cout << sp8 <<name_val <<", " << job_val<<", " << salary_val
<<","<<newsalary_val <<endl;
EXEC SQL FETCH c2 INTO :name_val,:job_val,:salary_val,
:newsalary_val;
}
EMB_SQL_CHECK ("Fetch");
/* close the cursor */
EXEC SQL CLOSE c2;
EXEC SQL DROP FUNCTION ScalarUDF;
return(rc);
error:
EXEC SQL WHENEVER SQLERROR continue;
cout << "Error occured SQLCODE = " << SQLCODE << "\n SQLERRMES= "
<<sqlca.sqlerrmc <<endl;
return(rc);
}