/******************************************************************************
**
** Source File Name = calludf.c
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
**    PURPOSE :
**  Sample code to perform the following operations using the Call Level
**  Interface:
**
**       - Register a UDF function.
**       - Call the registered UDF function in a SELECT statement.
**
** 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 "samputil.h"          /* Header file for CLI sample code */

/*
 * Global Variables for user id and password.
 * To keep samples simple, not a recommended practice.
 */
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;

/*******************************************************************
** main
** - initialize
** - register UDF function: wordcount
** - call the registered UDF function in a SELECT statement
** - terminate
*******************************************************************/
int main( int argc, char * argv[] ) {
   SQLHANDLE henv, hdbc, hstmt ;
   SQLRETURN rc ;

   SQLCHAR  szDrop[] =
   "DROP FUNCTION wordcount";

   SQLCHAR  szRegister[] =
   "  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  ";

   SQLCHAR  szSelect[] =
   "SELECT empno, resume_format, wordcount(resume)"
   "  FROM emp_resume "
   "  ORDER BY empno, resume_format";


   /* macro to initalize server, uid and pwd */
   INIT_UID_PWD ;

   /* allocate an environment handle */
   rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
   if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;

   /* allocate a connect handle, and connect */
   rc = DBconnect( henv, &hdbc ) ;
   if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;

   /* allocate a statement handle */
   rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
   CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;



   /* drop any function registered with the name: wordcount */
   rc = SQLExecDirect( hstmt, szDrop, SQL_NTS ) ;

   /* register function: wordcount*/
   rc = SQLExecDirect( hstmt, szRegister, SQL_NTS ) ;
   CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

   /* prepare and execute SELECT statement that */
   /* calls UDF function: wordcount */
   rc = SQLPrepare(hstmt, szSelect, SQL_NTS);
   CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

   rc = SQLExecute(hstmt);
   CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

   /* print results */
   rc = print_results (hstmt);
   if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;



   /*  Commit the changes.  */
   rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
   CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

   /*  Free statement resources allocated in: print_results  */
   rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
   CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

   rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
   CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

   /*  Disconnect and free up CLI resources.  */

   rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
   CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

   printf( "\n>Disconnecting .....\n" ) ;
   rc = SQLDisconnect( hdbc ) ;
   CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

   rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
   CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

   rc = SQLFreeHandle( SQL_HANDLE_ENV,  henv ) ;
   if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;

   return( SQL_SUCCESS ) ;

} /* end of main */