/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
** 
** The following sample of source code ("Sample") is owned by International 
** Business Machines Corporation or one of its subsidiaries ("IBM") and is 
** copyrighted and licensed, not sold. You may use, copy, modify, and 
** distribute the Sample in any form without payment to IBM, for the purpose of 
** assisting you in the development of your applications.
** 
** The Sample code is provided to you on an "AS IS" basis, without warranty of 
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
** not allow for the exclusion or limitation of implied warranties, so the above 
** limitations or exclusions may not apply to you. IBM shall not be liable for 
** any damages you suffer as a result of using, copying, modifying or 
** distributing the Sample, even if IBM has been advised of the possibility of 
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: udfemcli.sqC
**
** SAMPLE: Call a variety of types of embedded SQL user-defined functions.
**
**         This file is a client for the UDFs from udfemsrv.sqC.
**
** SQL STATEMENTS USED:
**         CLOSE
**         COMMIT
**         CREATE FUNCTION
**         CREATE TABLE
**         DECLARE CURSOR
**         DROP FUNCTION
**         DROP TABLE
**         EXECUTE IMMEDIATE
**         FETCH
**         PREPARE
**
** STRUCTURES USED:
**         sqlca
**
**                           
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running DB2
** applications, visit the DB2 Information Center at
**      http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/

#include <string.h>
#include <sqlenv.h>
#include "utilemb.h"
#if ((__cplusplus >= 199711L) && !defined DB2HP && !defined DB2AIX) || \
    (DB2LINUX && (__LP64__ || (__GNUC__ >= 3)) )
  #include <iomanip>
  #include <iostream>
  using namespace std;
#else
  #include <iomanip.h>
  #include <iostream.h>
#endif


EXEC SQL BEGIN DECLARE SECTION;
  char name[15];
  char job[15];
  double salary;
  short salaryInd;
  double salaryInUS;
  char empno[7];
  char resume_format[7];
  sqlint32 numWords;
  char deptNo[4];
  short deptNoInd;
  double amount;
  short amountInd;
  char strStmt[256];
EXEC SQL END DECLARE SECTION;

class udfemcli {
        public:
        //scalar UDFs
        int ExternalScalarUDFWithSQLUse();
        int ExternalScalarUDFWithNestingUse();
        int ExternalClobLocatorScalarUDFUse();

        //table UDFs
        int ExternalTableUDFWithSQLUse();

        private:
};


int main(int argc, char *argv[])
{
  int rc = 0;
  CmdLineArgs check;
  udfemcli emcli;
  DbEmb db;


  //check the command line arguments
  rc = check.CmdLineArgsCheck1(argc, argv, db);
  if (rc != 0)
  {
    return rc;
  }

  cout.setf(ios::fixed, ios::floatfield);
  cout << setprecision(2);
  cout << "\nTHIS SAMPLE SHOWS HOW TO WORK WITH EMBEDDED SQL UDFs.";

  //connect to database
  rc = db.Connect();
  if (rc != 0)
  {
    return rc;
  }

  //scalar UDFs
  rc = emcli.ExternalScalarUDFWithSQLUse();
  rc = emcli.ExternalScalarUDFWithNestingUse();
  rc = emcli.ExternalClobLocatorScalarUDFUse();

  //table UDFs
  rc = emcli.ExternalTableUDFWithSQLUse();

  //disconnect from database
  rc = db.Disconnect();
  if (rc != 0)
  {
    return rc;
  }

  return 0;
} //main

int udfemcli::ExternalScalarUDFWithSQLUse()
{
  struct sqlca sqlca;
  int rc = 0;

  cout << "\n-----------------------------------------------------------";
  cout << "\nUSE THE SQL STATEMENTS\n";
  cout << "  CREATE FUNCTION\n";
  cout << "  COMMIT\n";
  cout << "  SELECT\n";
  cout << "  DROP FUNCTION\n";
  cout << "TO WORK WITH SCALAR UDF THAT CONTAINS EMBEDDED SQL:\n";

  cout << "\n  DROP FUNCTION Convert, if exists.\n";
  EXEC SQL DROP FUNCTION Convert;

  EXEC SQL DROP TABLE exchangeRate;

  //register the Scalar UDF 'Convert'
  cout << "\n  Register the Scalar UDF 'Convert'.\n";

  EXEC SQL CREATE FUNCTION Convert(CHAR(2), DOUBLE, CHAR(2))
    RETURNS DOUBLE
    EXTERNAL NAME 'udfemsrv!Convert'
    FENCED
    CALLED ON NULL INPUT
    NOT VARIANT
    READS SQL DATA
    PARAMETER STYLE SQL
    LANGUAGE C
    NO EXTERNAL ACTION;
  EMB_SQL_CHECK("scalar UDF 'Convert' -- register");

  // Create the exchangeRate table
  cout << "\n  Create a table called 'exchangeRate'.\n";

  EXEC SQL CREATE TABLE exchangeRate (sourceCurrency char(2),
                                      resultCurrency char(2),
                                      exchangeRate double);
  EMB_SQL_CHECK("scalar UDF 'Convert' -- create table");

  // Insert into the exchangeRate table
  cout << "\n  Populate 'exchangeRate' table.\n";

  strcpy(strStmt, "INSERT INTO exchangeRate values "
         "(char('US'), char('CA'), 1.5), (char('CA'), char('US'), 0.67)");

  EXEC SQL EXECUTE IMMEDIATE :strStmt;
  EMB_SQL_CHECK("scalar UDF 'Convert' -- populate table");

  cout << "\n  COMMIT.\n";

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  //use scalar UDF
  cout << "\n  Use the scalar UDF 'Convert':\n";
  cout << "    SELECT name, job, salary, Convert(char('CA'), ";
  cout << "salary, char('US'))\n";
  cout << "      FROM staff\n";
  cout << "      WHERE name LIKE 'S%%'\n";

  strcpy(strStmt, "SELECT name, job, salary, Convert(char('CA'), salary,"
                  " char('US')) FROM staff WHERE name LIKE 'S%' ");

  EXEC SQL PREPARE stmt10 FROM :strStmt;
  EMB_SQL_CHECK("statement -- prepare");

  //declare cursor
  EXEC SQL DECLARE c10 CURSOR FOR stmt10;

  //open cursor
  EXEC SQL OPEN c10;
  EMB_SQL_CHECK("cursor -- open");

  //fetch cursor
  cout << "\n  Fetch each row and display.\n";
  cout << "    NAME       JOB     SALARY   SALARY IN US\n";
  cout << "    ---------- ------- -------- ------------\n";

  EXEC SQL FETCH c10 INTO :name, :job, :salary, :salaryInUS;
  EMB_SQL_CHECK("cursor -- fetch");

  while (sqlca.sqlcode != 100)
  {
    cout.setf(ios::left, ios::adjustfield);
    cout << "    " << setw(10) << name << " " << setw(7) << job;
    cout.setf(ios::right, ios::adjustfield);
    cout << " " << setw(7) << salary << "  " << setw(8) << salaryInUS << endl;

    EXEC SQL FETCH c10 INTO :name, :job, :salary, :salaryInUS;
    EMB_SQL_CHECK("cursor -- fetch");
  }

  //close cursor
  EXEC SQL CLOSE c10;
  EMB_SQL_CHECK("cursor -- close");

  //drop scalar UDF
  cout << "\n  DROP the scalar UDF 'Convert'.\n";

  EXEC SQL DROP FUNCTION Convert;
  EMB_SQL_CHECK("scalar UDF -- drop");

  //drop exchangeRate table
  cout << "\n  DROP TABLE exchangeRate.\n";

  EXEC SQL DROP TABLE exchangeRate;
  EMB_SQL_CHECK("scalar UDF -- drop table");

  cout << "\n  COMMIT.\n";

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  return 0;
} //ExternalScalarUDFWithSQLUse


int udfemcli::ExternalScalarUDFWithNestingUse()
{
  struct sqlca sqlca;
  int rc = 0;

  cout << "\n-----------------------------------------------------------";
  cout << "\nUSE THE SQL STATEMENTS\n";
  cout << "  CREATE FUNCTION\n";
  cout << "  COMMIT\n";
  cout << "  SELECT\n";
  cout << "  DROP FUNCTION\n";
  cout << "TO WORK WITH NESTED SCALAR UDF THAT CONTAINS EMBEDDED SQL:\n";

  //drop scalar UDF, if exists
  cout << "\n  DROP the scalar UDF, if exists.\n";

  EXEC SQL DROP FUNCTION Convert;

  EXEC SQL DROP FUNCTION SumSalary;

  EXEC SQL DROP TABLE exchangeRate;

  //register scalar UDF
  cout << "\n  Register the scalar UDFs 'Convert' and 'SumSalary'.";

  EXEC SQL CREATE FUNCTION Convert(CHAR(2), DOUBLE, CHAR(2))
    RETURNS DOUBLE
    EXTERNAL NAME 'udfemsrv!Convert'
    FENCED
    CALLED ON NULL INPUT
    NOT VARIANT
    READS SQL DATA
    PARAMETER STYLE SQL
    LANGUAGE C
    NO EXTERNAL ACTION;
  EMB_SQL_CHECK("scalar UDF -- register");

  EXEC SQL CREATE FUNCTION SumSalary(CHAR(3))
    RETURNS DOUBLE
    EXTERNAL NAME 'udfemsrv!SumSalary'
    FENCED
    CALLED ON NULL INPUT
    NOT VARIANT
    READS SQL DATA
    PARAMETER STYLE SQL
    LANGUAGE C
    NO EXTERNAL ACTION;
  EMB_SQL_CHECK("scalar UDF -- register");

  //Create the exchangeRate table
  cout << "\n  Create the table 'exchangeRate'.";

  EXEC SQL CREATE TABLE exchangeRate (sourceCurrency char(2),
                                      resultCurrency char(2),
                                      exchangeRate double);
  EMB_SQL_CHECK("scalar UDF -- create table");

  //Insert into the exchangeRate table
  cout << "\n  Populate the table 'exchangeRate'.";

  strcpy(strStmt, "INSERT INTO exchangeRate values (char('US'), "
                  "char('CA'), 1.5), (char('CA'), char('US'), 0.67)");

  EXEC SQL EXECUTE IMMEDIATE :strStmt;

  EMB_SQL_CHECK("scalar UDF -- populate table");

  cout << "\n  COMMIT.\n";

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  //use scalar UDF
  cout << "\n  Use the scalar UDF 'SumSalary':\n";
  cout << "    SELECT DeptNo, SumSalary(DeptNo)\n";
  cout << "      FROM department\n";

  strcpy(strStmt, "SELECT DeptNo, SumSalary(DeptNo) FROM department");

  EXEC SQL PREPARE stmt30 FROM :strStmt;
  EMB_SQL_CHECK("statement -- prepare");

  //declare cursor
  EXEC SQL DECLARE c30 CURSOR FOR stmt30;

  //open cursor
  EXEC SQL OPEN c30;
  EMB_SQL_CHECK("cursor -- open");

  //fetch cursor
  cout << "\n  Fetch each row and display.\n";
  cout << "    DeptNo  Sum of Salary in US currency\n";
  cout << "    ------  ----------------------------\n";

  EXEC SQL FETCH c30 INTO :deptNo :deptNoInd, :amount :amountInd;
  EMB_SQL_CHECK("cursor -- fetch");

  while (sqlca.sqlcode != 100)
  {
    if (deptNoInd >= 0)
    {
      cout.setf(ios::left, ios::adjustfield);
      cout << "       " << setw(3) << deptNo;
    }
    else
    {
      cout << "    -     ";
    }

    if (amountInd >= 0)
    {
      cout.setf(ios::right, ios::adjustfield);
      cout << "  " << setw(9) << amount;
    }
    else
    {
      cout << "  -";
    }
    cout << "\n";

    EXEC SQL FETCH c30 INTO :deptNo :deptNoInd, :amount :amountInd;
    EMB_SQL_CHECK("cursor -- fetch");
  }

  //close cursor
  EXEC SQL CLOSE c30;
  EMB_SQL_CHECK("cursor -- close");

  //drop scalar UDF 'SumSalary'
  cout << "\n  DROP the scalar UDF 'SumSalary'.\n";

  EXEC SQL DROP FUNCTION SumSalary;
  EMB_SQL_CHECK("scalar UDF -- drop");

  //drop scalar UDF 'Convert'
  cout << "\n  DROP the scalar UDF 'Convert'.\n";

  EXEC SQL DROP FUNCTION Convert;
  EMB_SQL_CHECK("scalar UDF -- drop");

  //drop exchangeRate table
  cout << "\n  DROP TABLE exchangeRate.\n";

  EXEC SQL DROP TABLE exchangeRate;
  EMB_SQL_CHECK("scalar UDF -- drop table");

  cout << "\n  COMMIT.\n";

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  return 0;
} //ExternalScalarUDFWithNestingUse


int udfemcli::ExternalClobLocatorScalarUDFUse()
{
  struct sqlca sqlca;
  int rc = 0;

  cout << "\n-----------------------------------------------------------";
  cout << "\nUSE THE SQL STATEMENTS\n";
  cout << "  CREATE FUNCTION\n";
  cout << "  COMMIT\n";
  cout << "  SELECT\n";
  cout << "  DROP FUNCTION\n";
  cout << "TO WORK WITH CLOB LOCATOR SCALAR UDF THAT CONTAINS ";
  cout << "EMBEDDED SQL:\n";

  //drop CLOB locator scalar UDF, if exists
  cout << "\n  DROP the CLOB locator scalar UDF, if exists.\n";

  EXEC SQL DROP FUNCTION CountWords;

  //register the CLOB Locator scalar UDF
  cout << "\n  Register the CLOB locator scalar UDF 'CountWords'.\n";

  EXEC SQL CREATE FUNCTION CountWords(CLOB(5k) AS LOCATOR)
    RETURNS INTEGER
    EXTERNAL NAME 'udfemsrv!ClobLocatorScalarUDF'
    FENCED
    RETURNS NULL ON NULL INPUT
    NOT VARIANT
    READS SQL DATA
    PARAMETER STYLE SQL
    LANGUAGE C
    NO EXTERNAL ACTION;
  EMB_SQL_CHECK("scalar UDF -- register");

  cout << "\n  COMMIT.\n";

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  //use CLOB locator scalar UDF
  cout << "\n  Use the CLOB locator scalar UDF:\n";
  cout << "    SELECT empno, resume_format, CountWords(resume)\n";
  cout << "      FROM emp_resume\n";
  cout << "      WHERE resume_format = 'ascii'\n";

  strcpy(strStmt, "SELECT empno, resume_format, CountWords(resume) "
                  "  FROM emp_resume "
                  "  WHERE resume_format = 'ascii' ");


  EXEC SQL PREPARE stmt40 FROM :strStmt;
  EMB_SQL_CHECK("statement -- prepare");

  //declare cursor
  EXEC SQL DECLARE c40 CURSOR FOR stmt40;

  //open cursor
  EXEC SQL OPEN c40;
  EMB_SQL_CHECK("cursor -- open");

  //fetch cursor
  cout << "\n  Fetch each row and display.\n";
  cout << "    EMPNO   RESUME_FORMAT NUM.WORDS\n";
  cout << "    ------- ------------- ---------\n";

  EXEC SQL FETCH c40 INTO :empno, :resume_format, :numWords;
  EMB_SQL_CHECK("cursor -- fetch");

  while (sqlca.sqlcode != 100)
  {
    cout.setf(ios::left, ios::adjustfield);
    cout << "    " << setw(7) << empno << " " << setw(13) << resume_format;
    cout << " " << setw(9) << numWords << endl;

    EXEC SQL FETCH c40 INTO :empno, :resume_format, :numWords;
    EMB_SQL_CHECK("cursor -- fetch");
  }

  //close cursor
  EXEC SQL CLOSE c40;
  EMB_SQL_CHECK("cursor -- close");

  //drop the CLOB locator scalar UDF
  cout << "\n  DROP the CLOB locator scalar UDF 'CountWords'.\n";

  EXEC SQL DROP FUNCTION CountWords;
  EMB_SQL_CHECK("scalar UDF -- drop");

  cout << "\n  COMMIT.\n";

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  return 0;
} //ExternalClobLocatorScalarUDFUse



int udfemcli::ExternalTableUDFWithSQLUse()
{
  struct sqlca sqlca;
  int rc = 0;

  cout << "\n-----------------------------------------------------------";
  cout << "\nUSE THE SQL STATEMENTS\n";
  cout << "  CREATE FUNCTION\n";
  cout << "  COMMIT\n";
  cout << "  SELECT\n";
  cout << "  DROP FUNCTION\n";
  cout << "TO WORK WITH TABLE UDF THAT CONTAINS EMBEDDED SQL:\n";

  //drop table UDF, if exists
  cout << "\n  DROP FUNCTION TableUDFWithSQL, if exists.\n";

  EXEC SQL DROP FUNCTION TableUDFWithSQL;

  //register table UDF
  cout << "\n  Register the table UDF 'TableUDFWithSQL'.\n";

  EXEC SQL CREATE FUNCTION TableUDFWithSQL(DOUBLE)
    RETURNS TABLE(name VARCHAR(20),
                  job VARCHAR(20),
                  salary DOUBLE)
    EXTERNAL NAME 'udfemsrv!TableUDFWithSQL'
    LANGUAGE C
    PARAMETER STYLE SQL
    NOT DETERMINISTIC
    FENCED
    READS SQL DATA
    NO EXTERNAL ACTION
    SCRATCHPAD 10
    FINAL CALL DISALLOW
    PARALLEL NO DBINFO;
  EMB_SQL_CHECK("table UDF with SQL -- register");

  cout << "\n  COMMIT.\n";

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  //use table UDF
  cout << "\n  Use the table UDF 'TableUDFWithSQL':";
  cout << "\n    SELECT udfTable.name, udfTable.job, udfTable.salary";
  cout << "\n      FROM TABLE(TableUDFWithSQL(1.5))";
  cout << "\n      AS udfTable\n";

  strcpy(strStmt, "SELECT udfTable.name, udfTable.job, udfTable.salary "
                  "  FROM TABLE(TableUDFWithSQL(1.5)) "
                  "  AS udfTable ");

  EXEC SQL PREPARE stmt50 FROM :strStmt;
  EMB_SQL_CHECK("statement -- prepare");

  //delcare cursor
  EXEC SQL DECLARE c50 CURSOR FOR stmt50;

  //open cursor
  EXEC SQL OPEN c50;
  EMB_SQL_CHECK("cursor -- open");

  //fetch cursor
  cout << "\n  Fetch each row and display.\n";
  cout << "    NAME       JOB     SALARY   \n";
  cout << "    ---------- ------- ---------\n";

  EXEC SQL FETCH c50 INTO :name, :job, :salary:salaryInd;
  EMB_SQL_CHECK("cursor -- fetch");

  while (sqlca.sqlcode != 100)
  {
    cout.setf(ios::left, ios::adjustfield);
    cout << "    " << setw(10) << name << " " << setw(7) << job;
    if (salaryInd >= 0)
    {
      cout.setf(ios::right, ios::adjustfield);
      cout << " " << setw(9) << salary;
    }
    else
    {
      cout <<"-";
    }
    cout << "\n";

    EXEC SQL FETCH c50 INTO :name, :job, :salary:salaryInd;
    EMB_SQL_CHECK("cursor -- fetch");
  }

  //close cursor
  EXEC SQL CLOSE c50;
  EMB_SQL_CHECK("cursor -- close");

  //drop table UDF
  cout << "\n  DROP FUNCTION TableUDFWithSQL.\n";

  EXEC SQL DROP FUNCTION TableUDFWithSQL;
  EMB_SQL_CHECK("table UDF -- drop");

  cout << "\n  COMMIT.\n";

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");

  return 0;
} //TableUDFWithSQLUse