/******************************************************************************
**
** Source File Name = calludf.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 :
**       This program is an example of how UDFs are implemented in
**       order to modify selection queries.
**
**    STRUCTURES USED :
**       sqlca
**
**    APIs USED :
**
**    FUNCTIONS DECLARED :
**       'C' COMPILER LIBRARY :
**          stdio.h  -  printf
**          string.h -  fgets, strncpy
**
**       DBMS LIBRARY :
**         sqlenv.h
**
**    Other Function(s) used:
**          external : [in the file UTIL.C]
**             check_error :     Checks for SQLCODE error, and prints out any
**                               related information available.
**             init_da:            Initialiaze the memory space for an SQLDA.
**             alloc_host_vars:    Allocate memory to be filled with SQLDA
**                                 data.
**             free_da:            Frees up memory which has been allocated
**                                 as an SQLDA structure.
**             display_col_titles: Display column titles.
**             display_da:         Display row information.
**
**    EXTERNAL DEPENDENCIES :
**       - Ensure existence of database for precompile purposes.
**       - Precompile with the SQL precompiler (PREP in DB2)
**       - Bind to a database (BIND in DB2)
**       - Compile and link 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 <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlda.h>
#include "utilemb.h"


EXEC SQL INCLUDE SQLCA;


int output_statement (char *);

int main(int argc, char *argv[]) {

   EXEC SQL BEGIN DECLARE SECTION;
      char  userid[9];
      char  passwd[19];
      char statement[1000];
   EXEC SQL END DECLARE SECTION;


   printf( "\nSample C program:  CALLUDF\n");

   if (argc == 1) {
      EXEC SQL CONNECT TO sample;     /* :rk.5:erk. */
      EMB_SQL_CHECK("CONNECT TO SAMPLE"); /* :rk.6:erk. */
   }
   else if (argc == 3) { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;  /* :rk.5:erk. */
      EMB_SQL_CHECK("CONNECT TO SAMPLE");  /* :rk.6:erk. */
   }
   else {
      printf ("\nUSAGE: calludf [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   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
      FENCED
      EXTERNAL NAME 'udf!increase'
      NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
      NO EXTERNAL ACTION;
   EMB_SQL_CHECK("CREATE FUNCTION increase");

   EXEC SQL CREATE FUNCTION raisesal (float, float) RETURNS float
      FENCED
      EXTERNAL NAME 'udf!raisesal'
      NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
      NO EXTERNAL ACTION;
   EMB_SQL_CHECK("CREATE FUNCTION raisesal");

   EXEC SQL CREATE FUNCTION wordcount (CLOB(5K)) RETURNS INT
      FENCED
      EXTERNAL NAME 'udf!wordcount'
      NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
      NO EXTERNAL ACTION;
   EMB_SQL_CHECK("CREATE FUNCTION wordcount");

   EXEC SQL CREATE FUNCTION findv (VARCHAR(500)) RETURNS SMALLINT
      FENCED
      EXTERNAL NAME 'udf!findvwl'
      NOT VARIANT NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
      NO EXTERNAL ACTION;
   EMB_SQL_CHECK("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;
   EMB_SQL_CHECK("CREATE FUNCTION fold");

   EXEC SQL CREATE FUNCTION ctr() RETURNS INT
      SCRATCHPAD FENCED VARIANT
      EXTERNAL NAME 'udf!ctr'
      NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
      NO EXTERNAL ACTION;
   EMB_SQL_CHECK("CREATE FUNCTION ctr");

   EXEC SQL CREATE FUNCTION twice (INT) RETURNS INT
      SCRATCHPAD FENCED VARIANT
      EXTERNAL NAME 'udf!leni'
      NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
      NO EXTERNAL ACTION;
   EMB_SQL_CHECK("CREATE FUNCTION twice");

   EXEC SQL CREATE FUNCTION pos (CLOB(33k), VARCHAR(50)) RETURNS INT
      SCRATCHPAD FENCED VARIANT
      EXTERNAL NAME 'udf!ilob'
      NO SQL PARAMETER STYLE DB2SQL LANGUAGE C
      NO EXTERNAL ACTION;
   EMB_SQL_CHECK("CREATE FUNCTION pos");

   strcpy (statement, "select empno, resume_format, wordcount(resume) from "
      "emp_resume order by empno, resume_format");
   output_statement (statement);
   EMB_SQL_CHECK("PROCESS STATEMENT #1");

   strcpy (statement, "select deptname, findv(deptname) from org order by deptname");
   output_statement (statement);
   EMB_SQL_CHECK("PROCESS STATEMENT #2");

   strcpy (statement, "select name, id, ctr() from staff order by name, id");
   output_statement (statement);
   EMB_SQL_CHECK("PROCESS STATEMENT #3");

   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}


int output_statement (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;
   EMB_SQL_CHECK("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;
   EMB_SQL_CHECK("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;
   EMB_SQL_CHECK("OPEN");

   EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer;
   EMB_SQL_CHECK("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;
   EMB_SQL_CHECK("CLOSE CURSOR");
   printf ("\n  %d record(s) selected\n\n", counter);

   free_da(sqldaPointer);
   return (SQLCODE);
}
/* end of program : calludf.sqc */