/*******************************************************************************
**
** 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.
** This is a client for the UDFs from CLI udfsrv.c .
**
** For more information about these samples see the README file.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlenv.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int ScalarUDFCall( ) ;
int SourcedUDFUse( ) ;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
int rc;
printf( "\nSample C program: udfcli \n");
if (argc == 1) {
EXEC SQL CONNECT TO sample;
EMB_SQL_CHECK ("CONNECT TO SAMPLE");
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd;
EMB_SQL_CHECK ("CONNECT TO SAMPLE");
}
else {
printf ("\nUSAGE: %s [userid passwd]\n\n",argv[0]);
return 1;
}
rc = ScalarUDFCall( ) ;
rc = SourcedUDFUse( ) ;
EXEC SQL ROLLBACK;
EMB_SQL_CHECK ("ROLLBACK");
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK ("CONNECT RESET");
return 0;
}
/******************************************************************************
** SourcedUDFUse
******************************************************************************/
int 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;
printf("%sCREATE DISTINCT TYPE CNUM AS INTEGER\n",sp5);
printf("%sWITH COMPARISONS\n",sp5);
EXEC SQL CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS;
EMB_SQL_CHECK ("Create DISTINCT");
/* create a sourced UDF */
printf(" CREATE FUNCTION MAX(CNUM) RETURNS CNUM\n");
printf(" 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 */
printf("%sCREATE TABLE CUSTOMER ( \n",sp5);
printf("%sCustNum CNUM NOT NULL,\n",sp8);
printf("%sCustName CHAR(30) NOT NULL)\n",sp8);
EXEC SQL CREATE TABLE customer (CustNum CNUM NOT NULL,
CustName CHAR(30) NOT NULL);
EMB_SQL_CHECK ("Create TABLE");
/* insert values in table */
printf("%sINSERT INTO CUSTOMER VALUES\n",sp5);
printf("%s( CAST(1 AS CNUM), 'JOHN WALKER'),\n",sp8);
printf("%s( CAST(2 AS CNUM), 'BRUCE ADAMSON'),\n",sp8);
printf("%s( CAST(3 AS CNUM), 'SALLY KWAN')\n",sp8);
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 */
printf("%sUse the sourced UDF performing this statement:\n",sp5);
printf("%sSELECT FROM CUSTOMER CAST(MAX(CUSTNUM) AS INTEGER)\n",sp8);
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 ){
printf( "%sMax(CustNum) is : %-8d \n", sp8,maxCustNum_val ) ;
}
EXEC SQL CLOSE c1;
/* drop the table */
printf("%s%s\n",sp5,stmt6);
EXEC SQL DROP TABLE customer ;
EMB_SQL_CHECK ("DROP TABLE");
/* drop the sourced UDF */
printf("%s%s\n", sp5,stmt7);
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 */
printf("%s%s\n",sp5,stmt8);
EXEC SQL DROP DISTINCT TYPE CNUM ;
EMB_SQL_CHECK ("DROP TYPE");
return(rc);
error:
EXEC SQL WHENEVER SQLERROR continue;
printf("Error occured SQLCODE =%d \n SQLERRMES=%s\n",
SQLCODE,sqlca.sqlerrmc);
return(rc);
}
/******************************************************************************
** ScalarUDFCall
******************************************************************************/
int 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;
printf("\nUSE THE EMBEDDED SQL \n");
printf("-Scalar UDF\n");
printf("TO CALL UDFs:\n");
EXEC SQL DROP FUNCTION ScalarUDF;
if (SQLCODE !=0) {
printf("%sNo function dropped\n",sp5);
}
printf("%sRegister the scalar UDF.\n",sp5);
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");
printf("%sUse the scalar UDF performing this statement:\n",sp5);
printf("%sSELECT name, job, salary, ScalarUDF( job, salary)\n",sp8);
printf("%sFROM staff WHERE name LIKE 'S%%'\n",sp8);
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 */
printf("%sFetch each row and display.\n",sp5);
printf("%sNAME JOB SALARY NEW_SALARY\n",sp8);
printf("%s---------- ------ ---------- ----------\n",sp8);
EXEC SQL FETCH c2 INTO :name_val,:job_val,:salary_val,
:newsalary_val;
EMB_SQL_CHECK ("Fetch");
if (SQLCODE == 100)
{ printf("%sData not found.\n",sp5);
}
while (SQLCODE == 0 ) {
printf("%s%-10s %-7s %-7.2f %-7.2f\n",
sp8,name_val, job_val, salary_val,newsalary_val);
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;
printf("Error occured SQLCODE =%d \n SQLERRMES=%s\n",
SQLCODE,sqlca.sqlerrmc);
return(rc);
}