/*******************************************************************************
**
** Source File Name = udfcli.c
**
** 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 :
** Client for the UDFs from udfsrv.c .
**
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "utilcli.h" /* Header file for CLI sample code */
int ScalarUDFCall( SQLHANDLE) ;
int SourcedUDFUse( SQLHANDLE) ;
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] )
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handles */
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
if ( rc != 0 ) return( rc ) ;
printf("\n\nUDFs: HOW TO CALL UDFs.\n");
/* initialize the CLI application */
rc = CLIAppInit( dbAlias, user, pswd, &henv, &hdbc,
(SQLPOINTER)SQL_AUTOCOMMIT_ON);
if ( rc != 0 ) return( rc ) ;
rc = ScalarUDFCall( hdbc) ;
rc = SourcedUDFUse( hdbc) ;
/* terminate the CLI application */
rc = CLIAppTerm( &henv, &hdbc, dbAlias);
return( rc ) ;
} /* end main */
/******************************************************************************
** ScalarUDFCall
******************************************************************************/
int ScalarUDFCall( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmtDrop = ( SQLCHAR *)
"DROP FUNCTION ScalarUDF";
SQLCHAR * stmtRegister = ( SQLCHAR *)
" 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 ";
SQLCHAR * stmtSelect = ( SQLCHAR *)
"SELECT name, job, salary, ScalarUDF( job, salary)"
" FROM staff WHERE name LIKE 'S%' " ;
struct
{ SQLINTEGER ind ;
SQLCHAR val[15] ;
} name, job;
struct
{ SQLINTEGER ind ;
SQLDOUBLE val ;
} salary, newSalary;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLBindCol\n");
printf("-SQLFetch\n-SQLFreeHandle\n");
printf("TO CALL UDFs:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* execute directly the drop statement */
sqlrc = SQLExecDirect( hstmt, stmtDrop, SQL_NTS ) ;
/* execute directly the register statement */
printf("\n Register the scalar UDF.\n");
sqlrc = SQLExecDirect( hstmt, stmtRegister, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute directly the select statement */
printf(" Use the scalar UDF performing this statement:\n");
printf(" SELECT name, job, salary, ScalarUDF( job, salary)\n");
printf(" FROM staff WHERE name LIKE 'S%%'\n");
sqlrc = SQLExecDirect( hstmt, stmtSelect, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_CHAR, name.val, 15,
&name.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR, job.val, 15,
&job.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 3, SQL_C_DOUBLE, &salary.val,
sizeof( salary.val), &salary.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 4, SQL_C_DOUBLE, &newSalary.val,
sizeof( newSalary.val), &newSalary.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch each row, and display */
printf(" Fetch each row and dispaly.\n");
printf(" NAME JOB SALARY NEW_SALARY\n");
printf(" ---------- ------ ---------- ----------\n");
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
while (sqlrc != SQL_NO_DATA_FOUND)
{ printf(" %-10s %-7s %-7.2f %-7.2f\n",
name.val, job.val, salary.val,newSalary.val);
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
/* close the cursor */
sqlrc = SQLCloseCursor( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute directly the drop statement */
sqlrc = SQLExecDirect( hstmt, stmtDrop, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}
/******************************************************************************
** SourcedUDFUse
******************************************************************************/
int SourcedUDFUse( SQLHANDLE hdbc)
{ SQLRETURN sqlrc = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt ; /* statement handle */
SQLCHAR * stmt1 = ( SQLCHAR *)
"CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS";
SQLCHAR * stmt2 = ( SQLCHAR *)
"CREATE FUNCTION MAX(CNUM) RETURNS CNUM source sysibm.max(integer)";
SQLCHAR * stmt3 = ( SQLCHAR *)
"CREATE TABLE CUSTOMER ( "
"CustNum CNUM NOT NULL, "
"CustName CHAR(30) NOT NULL)" ;
SQLCHAR * stmt4 = ( SQLCHAR *)
"INSERT INTO CUSTOMER VALUES "
" ( CAST(1 AS CNUM), 'JOHN WALKER'), "
" ( CAST(2 AS CNUM), 'BRUCE ADAMSON'), "
" ( CAST(3 AS CNUM), 'SALLY KWAN')" ;
SQLCHAR * stmt5 = ( SQLCHAR *)
"SELECT CAST(MAX(CustNum) AS INTEGER ) FROM CUSTOMER " ;
SQLCHAR * stmt6 = ( SQLCHAR *)
"DROP TABLE CUSTOMER" ;
SQLCHAR * stmt7 = ( SQLCHAR *)
"DROP FUNCTION MAX(CNUM)" ;
SQLCHAR * stmt8 = ( SQLCHAR *)
"DROP DISTINCT TYPE CNUM" ;
struct
{ SQLINTEGER ind ;
SQLINTEGER val ;
} maxCustNum;
printf("\nUSE THE CLI FUNCTIONS\n");
printf("-SQLSetConnectAttr\n-SQLAllocHandle\n");
printf("-SQLExecDirect\n-SQLBindCol\n");
printf("-SQLFetch\n-SQLFreeHandle\n");
printf("TO USE A SOURCED UDF:\n");
/* set AUTOCOMMIT on */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* allocate a statement handle */
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
DBC_HANDLE_CHECK( hdbc, sqlrc);
/* create a distinct type */
printf("\n Execute directly\n");
printf(" CREATE DISTINCT TYPE CNUM AS INTEGER\n");
printf(" WITH COMPARISONS\n");
sqlrc = SQLExecDirect( hstmt, stmt1, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* create a sourced UDF */
printf(" Execute directly\n");
printf(" CREATE FUNCTION MAX(CNUM) RETURNS CNUM\n");
printf(" source sysibm.max(integer)\n");
sqlrc = SQLExecDirect( hstmt, stmt2, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* create a table that uses the distinct type */
printf(" Execute directly\n");
printf(" CREATE TABLE CUSTOMER ( \n");
printf(" CustNum CNUM NOT NULL,\n");
printf(" CustName CHAR(30) NOT NULL)\n");
sqlrc = SQLExecDirect( hstmt, stmt3, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* insert values in table */
printf(" Execute directly\n");
printf(" INSERT INTO CUSTOMER VALUES\n");
printf(" ( CAST(1 AS CNUM), 'JOHN WALKER'),\n");
printf(" ( CAST(2 AS CNUM), 'BRUCE ADAMSON'),\n");
printf(" ( CAST(3 AS CNUM), 'SALLY KWAN')\n");
sqlrc = SQLExecDirect( hstmt, stmt4, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* execute directly the select statement */
printf(" Use the sourced UDF performing this statement:\n");
printf(" SELECT FROM CUSTOMER CAST(MAX(CUSTNUM) AS INTEGER)\n");
sqlrc = SQLExecDirect( hstmt, stmt5, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_LONG, &maxCustNum.val,
sizeof( maxCustNum.val), &maxCustNum.ind ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* fetch the result */
printf(" Fetch the result.\n");
sqlrc = SQLFetch( hstmt );
STMT_HANDLE_CHECK( hstmt, sqlrc);
if (sqlrc == SQL_NO_DATA_FOUND)
{ printf("\n Data not found.\n");
}
else
{ printf( " Max(CustNum) is : %-8d \n", maxCustNum.val ) ;
}
/* close the cursor */
sqlrc = SQLCloseCursor( hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* drop the table */
printf(" Execute directly\n");
printf(" %s\n", stmt6);
sqlrc = SQLExecDirect( hstmt, stmt6, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* drop the sourced UDF */
printf(" Execute directly\n");
printf(" %s\n", stmt7);
sqlrc = SQLExecDirect( hstmt, stmt7, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* drop the distinct type */
printf(" Execute directly\n");
printf(" %s\n", stmt8);
sqlrc = SQLExecDirect( hstmt, stmt8, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* free the statement handle */
sqlrc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
return(rc);
}