/****************************************************************************
** (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: tbloadcursor.sqC
**    
** SAMPLE: How to load data into a table using either the CURSOR or
**         REMOTEFETCH media types.
**         This sample program uses the following argument list:
**
**           TbLoadCursor [dbAlias1 [dbAlias2] [user1 pswd1 [user2 pswd2]]]
**
**        - if dbAlias1 is not specified, a Load from CURSOR will be
**          performed against database SAMPLE.
**        - if dbAlias1 is specified but dbAlias2 is not, a Load from
**          CURSOR will be performed against database dbAlias1.
**        - if dbAlias1 and dbAlias2 are specified, a Load from
**          REMOTEFETCH media will be performed against database dbAlias1
**          by fetching from dbAlias2.
**
**          NOTE: You must explicitly bind TbLoadCursor.bnd against both 
**                databases (dbAlias1 and dbAlias2) when Load from 
**                REMOTEFETCH media is performed.
**           
** DB2 API USED:
**         db2Load -- Loads data into a DB2 table
**
** 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, building, and running DB2 
** applications, visit the DB2 Information Center: 
**     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/

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

#include "utilemb.h"
#include "utilapi.h"

#ifndef TRUE
  #define TRUE 1
  #define FALSE 0
#endif

EXEC SQL BEGIN DECLARE SECTION;
  char strStmt[256];
  short c1;
  char dbAlias_source[9];
  char user_source[31];
  char pswd_source[129];
  char dbAlias_target[9];
  char user_target[31];
  char pswd_target[129];
EXEC SQL END DECLARE SECTION;

class TbLoadCursor
{
  public:
    int CreateSourceTable(void);
    int InsertInitialValuesInSourceTable(void);
    int CreateTargetTable(void);
    int LoadData(int, char *, char *, char *);
    int DropSourceTable(void);
    int DropTargetTable(void);
    int DisplayTableContents(void);
};

//�This function creates a table SOURCE1
int TbLoadCursor::CreateSourceTable(void)
{

  struct sqlca sqlca;

  // create a new table 'SOURCE1'
  cout<<"\n  CREATE A NEW TABLE 'MYSCHEMA1.SOURCE1' BY INVOKING"
        " THE STATEMENT:";
  cout<<"\n  --------------------------------------------------------"
        "---------";
  cout<<"\n     CREATE TABLE MYSCHEMA1.SOURCE1 (c1 integer not null)\n\n";

  EXEC SQL CREATE TABLE MYSCHEMA1.SOURCE1 (
             C1 INTEGER NOT NULL);
  EMB_SQL_CHECK("new table -- create");

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

  return 0;
} // TbLoadCursor::CreateSourceTable

// This function creates a table TARGET1
int TbLoadCursor::CreateTargetTable(void)
{
  struct sqlca sqlca;

  // create a new table 'MYSCHEMA1.TARGET1'
  cout<<"\n  CREATE A NEW TABLE 'MYSCHEMA1.TARGET1' BY INVOKING"
        " THE STATEMENT:";
  cout<<"\n  ----------------------------------------------------"
        "-------------";
  cout<<"\n       CREATE TABLE MYSCHEMA1.TARGET1 (c1 integer not null)\n";

  EXEC SQL CREATE TABLE MYSCHEMA1.TARGET1 (
             C1 INTEGER NOT NULL);
  EMB_SQL_CHECK("new table -- create");

  EXEC SQL COMMIT;
  EMB_SQL_CHECK("transaction -- commit");
  return 0;
} // TbLoadCursor::CreateTargetTable

// This function drops table SOURCE1
int TbLoadCursor::DropSourceTable(void)
{
  int rc = 0;
  struct sqlca sqlca;

  // drop the table 'MYSCHEMA1.SOURCE1'
  cout<<"\n  DROP TABLE MYSCHEMA1.SOURCE1\n";

  EXEC SQL DROP TABLE MYSCHEMA1.SOURCE1;
  EMB_SQL_CHECK("table MYSCHEMA1.SOURCE1 -- drop");

  return 0;
} // TbLoadCursor::DropSourceTable

// This function drops table TARGET1
int TbLoadCursor::DropTargetTable(void)
{
  int rc = 0;
  struct sqlca sqlca;

  // drop the table 'MYSCHEMA1.TARGET1'
  cout<<"\n  DROP TABLE MYSCHEMA1.TARGET1\n";

  EXEC SQL DROP TABLE MYSCHEMA1.TARGET1;
  EMB_SQL_CHECK("table MYSCHEMA1.TARGET1 -- drop");

  return 0;
} // TbLoadCursor::DropTargetTable

// This function inserts some values directly into table MYSCHEMA.SOURCE1
int TbLoadCursor::InsertInitialValuesInSourceTable(void)
{
  struct sqlca sqlca;

  cout<< "  INSERT INITIAL VALUES INTO TABLE SOURCE1 BY INVOKING\n"
         "  THE STATEMENT:\n";
         "  --------------------------------------------------------"
         "--------\n";
         "      INSERT INTO SOURCE1 VALUES (1), (2), (3), (4), (5)\n";

  // Insert initial values into table SOURCE1
  strcpy(strStmt,
         "INSERT INTO MYSCHEMA1.SOURCE1 VALUES (1), (2), (3), (4), (5)");
  EXEC SQL EXECUTE IMMEDIATE :strStmt;
  EMB_SQL_CHECK("insert -- SOURCE1");
 
  EXEC SQL COMMIT;
  EMB_SQL_CHECK("commit");

  return 0;
} // TbLoadCursor::InsertInitialValuesInSourceTable

// This function demonstrates how to load data using the remotefetch media
// type using the db2Load API
int TbLoadCursor::LoadData(int bDoRemoteFetch,
                           char *pDBName_source,
                           char *pUserID_source,
                           char *pPasswd_source)
{
  struct sqlca sqlca;

  // Statement�for CURSOR or REMOTEFETCH media
  char *pSourceStatement = "select * from MYSCHEMA1.SOURCE1";
  char actionString[256];

  // Declare the input and output structures needed to call the
  // db2Load API
  struct db2LoadStruct paramStruct;
  struct db2LoadIn inputInfoStruct;
  struct db2LoadOut outputInfoStruct;
  struct sqlu_media_list mediaList;
  union sqlu_media_list_targets mediaListTargets;
  struct sqlu_statement_entry statementEntry;
  struct sqlu_remotefetch_entry remotefetchEntry;

  struct sqlchar *pAction;
  char localMsgFileName[128];

  // Initialize the members of the structures to NULL or zero
  memset(&paramStruct, 0, sizeof(paramStruct));
  memset(&mediaList, 0, sizeof(mediaList));
  memset(&outputInfoStruct, 0, sizeof(outputInfoStruct));
  memset(&inputInfoStruct, 0, sizeof(inputInfoStruct));
  memset(&mediaListTargets, 0, sizeof(mediaListTargets));
  memset(&statementEntry, 0, sizeof(statementEntry));
  memset(&remotefetchEntry, 0, sizeof(remotefetchEntry));

  cout<<
    "\n  INITIALIZE LOAD API STRUCTURES AND INVOKE THE LOAD API";
    "\n  ------------------------------------------------------";
    "\n  CREATE db2LoadStruct, db2LoadIn, db2LoadOut, ";
    "\n  paramStruct, inputInfoStruct, and outputInfoStruct RESPECTIVELY";
    "\n  AND INITIALIZE THEIR MEMBERS TO ZERO OR NULL.\n";

  cout<<
    "\n  CREATE sqlu_media_list, mediaList, remotefetchEntry or";
    "\n   statementEntry, sqlu_media_list_targets UNION, and ";
    "\n   mediaListTargets. INITIALIZE THEIR MEMBERS TO ZERO OR NULL.\n";

  // Setup the input information structure
  cout<<"\n  SET UP THE db2LoadIn STRUCTURE 'inputInfoStruct'.\n";

  inputInfoStruct.iRestartphase =  ' ';
  inputInfoStruct.iStatsOpt = SQLU_STATS_NONE;
  inputInfoStruct.iIndexingMode = SQLU_INX_AUTOSELECT;
  inputInfoStruct.iNonrecoverable = SQLU_NON_RECOVERABLE_LOAD;
  inputInfoStruct.iAccessLevel = SQLU_ALLOW_NO_ACCESS;
  inputInfoStruct.iLockWithForce = SQLU_NO_FORCE;
  inputInfoStruct.iCheckPending = SQLU_CHECK_PENDING_CASCADE_DEFERRED;

  if (bDoRemoteFetch)
  {
    // Setup the REMOTEFETCH media
    cout<<"\n  SET UP remotefetchEntry, mediaListTargets, mediaList AND";
    cout<<"\n  paramStruct.\n";

    remotefetchEntry.pDatabaseName = pDBName_source;
    remotefetchEntry.iDatabaseNameLen = strlen(pDBName_source);
    if (strlen(pUserID_source) != 0)
    {
      remotefetchEntry.pUserID = pUserID_source;
      remotefetchEntry.iUserIDLen = strlen(pUserID_source);
    }
    if (strlen(pPasswd_source) != 0)
    {
      remotefetchEntry.pPassword = pPasswd_source;
      remotefetchEntry.iPasswordLen = strlen(pPasswd_source);
    }
    remotefetchEntry.pStatement = pSourceStatement;
    remotefetchEntry.iStatementLen = strlen(pSourceStatement);
    mediaListTargets.pRemoteFetch = &remotefetchEntry;
    mediaList.media_type = SQLU_REMOTEFETCH;
  }
  else
  {
    // Setup the CURSOR media
    cout<<"\n  SET UP statementEntry, mediaListTargets, mediaList AND";
    cout<<"\n  paramStruct.\n";

    statementEntry.pEntry = pSourceStatement;
    statementEntry.length = strlen(pSourceStatement);
    mediaListTargets.pStatement = &statementEntry;
    mediaList.media_type = SQLU_SQL_STMT;
  }
  mediaList.sessions = 1;
  mediaList.target = mediaListTargets;

  // Setup action string
  strcpy(actionString, "REPLACE INTO MYSCHEMA1.TARGET1");
  pAction = (struct sqlchar *)malloc(sizeof(short) +
                                     sizeof(actionString) + 1);
  pAction->length = strlen(actionString);
  strcpy(pAction->data, actionString);

  // Setup the parameter structure
  paramStruct.piSourceList = &mediaList;
  paramStruct.piActionString = pAction;  
  paramStruct.piFileType = SQL_CURSOR;
   
  strcpy(localMsgFileName, "tbloadcursor.MSG");
  paramStruct.piLocalMsgFileName = localMsgFileName;
  paramStruct.piLoadInfoIn = &inputInfoStruct;
  paramStruct.poLoadInfoOut    = &outputInfoStruct;
  paramStruct.iCallerAction = SQLU_INITIAL;

  // load data into the 'MYSCHEMA1.TARGET1' table by calling the db2Load API
  cout<<"\n  CALL THE DB2 API db2Load()\n";
  db2Load (db2Version970, &paramStruct, &sqlca);     

  DB2_API_CHECK("table -- load");

  // free memory allocated
  free(pAction);

  // Display the contents of table 'MYSCHEMA1.TARGET1' after
  // loading data into it
  cout<<
    "\n  DISPLAY THE CONTENTS OF TABLE 'MYSCHEMA1.TARGET1' AFTER DATA"
    " IS LOADED INTO IT";
    "\n  -------------------------------------------------------------"
    "-----------------\n";
  DisplayTableContents();

  return 0;
} // TbLoadCursor::LoadData

// This function displays the contents of the TARGET1 table
int TbLoadCursor::DisplayTableContents(void)
{
  struct sqlca sqlca;
  char *selectstmt = "SELECT * FROM MYSCHEMA1.TARGET1";
  
  cout<<"\n    "<< selectstmt;
  cout<<"\n\n    C1\n";
  cout<<"    ---\n";

  // Declare a CURSOR to store the results of the query specified by
  // 'selectstmt'
  strcpy(strStmt, selectstmt);
  EXEC SQL PREPARE S1 FROM :strStmt;
  EMB_SQL_CHECK("after prepare");

  EXEC SQL DECLARE c1 CURSOR FOR s1;
  EMB_SQL_CHECK("declare cursor");

  EXEC SQL OPEN c1;
  EMB_SQL_CHECK("cursor -- open");

  // Retrieve and display the results of the query
  EXEC SQL FETCH c1 INTO :c1;
  EMB_SQL_CHECK("cursor -- fetch");

  while (sqlca.sqlcode != 100)
  {
    cout<<"\n    " << c1;
    EXEC SQL FETCH c1 INTO :c1;
    EMB_SQL_CHECK("cursor -- fetch");
  }

  EXEC SQL CLOSE c1;
  EMB_SQL_CHECK("cursor -- close");

  return 0;
} // TbLoadCursor::DisplayTableContents

int main(int argc, char *argv[])
{
  int rc = 0;
  struct sqlca sqlca;
  CmdLineArgs check;
  TbLoadCursor tbloadcursor;
  int bDoRemoteFetch = FALSE;
  DbEmb db1;
  DbEmb db2;

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

  strcpy(dbAlias_source, db1.getAlias());
  strcpy(user_source, db1.getUser());
  strcpy(pswd_source, db1.getPswd());

  strcpy(dbAlias_target, db2.getAlias());
  strcpy(user_target, db2.getUser());
  strcpy(pswd_target, db2.getPswd());

  // Deciding upon which method to be used : CURSOR or REMOTEFETCH media
  // (if both dbAlias1 and db2Alias2 are specified, use REMOTEFETCH)
  bDoRemoteFetch = (argc == 3 || argc >= 5) ? TRUE : FALSE;

  if (bDoRemoteFetch)
    cout<<"\n  THIS SAMPLE SHOWS HOW TO LOAD USING REMOTEFETCH MEDIA"
          " TYPE \n";
  else
    cout<<"\n  THIS SAMPLE SHOWS HOW TO LOAD USING THE CURSOR MEDIA TYPE \n";

  // User/Password must be specified for REMOTEFETCH
  if (bDoRemoteFetch &&
       (strlen(user_target)==0 ||
         strlen(pswd_target)==0))
  {
    cout<<"\n (error: userid & password must be provided for"
          " REMOTEFETCH media type)";
    return -1;
  }

  // Create MYSCHEMA1.SOURCE table
  if (bDoRemoteFetch)
  {
  cout << "\n  Connecting to '";
  cout << dbAlias_source << "' database..." << endl;
  if (strlen(user_source) == 0)
  {
    EXEC SQL CONNECT TO :dbAlias_source;
    EMB_SQL_CHECK("CONNECT");
  }
  else
  {
    EXEC SQL CONNECT TO :dbAlias_source USER :user_source USING :pswd_source;
    EMB_SQL_CHECK("CONNECT");
  }
  cout << "  Connected to '" << dbAlias_source << "' database." << endl;
  }
  else
  {
    cout << "\n  Connecting to '";
    cout << dbAlias_target << "' database..." << endl;
    if (strlen(user_target)==0)
    {
      EXEC SQL CONNECT TO :dbAlias_target;
      EMB_SQL_CHECK("CONNECT");
    }
    else
    {
      EXEC SQL CONNECT TO :dbAlias_target USER :user_target USING :pswd_target;
      EMB_SQL_CHECK("CONNECT");
    }
    cout << "  Connected to '" << dbAlias_target << "' database." << endl;
  }

  rc = tbloadcursor.CreateSourceTable();
  rc = tbloadcursor.InsertInitialValuesInSourceTable();

  // Create MYSCHEMA1.TARGET table
  if (bDoRemoteFetch)
  {
  cout << "\n  Disconnecting from '";
  cout << dbAlias_source << "' database..." << endl;

  EXEC SQL CONNECT RESET;
  EMB_SQL_CHECK("CONNECT RESET");

  cout << "  Disconnected from '";
  cout << dbAlias_source << "' database." << endl;

  if (strlen(user_target) == 0)
  {
    EXEC SQL CONNECT TO :dbAlias_target;
    EMB_SQL_CHECK("CONNECT");
  }
  else
  {
    EXEC SQL CONNECT TO :dbAlias_target USER :user_target USING :pswd_target;
    EMB_SQL_CHECK("CONNECT");
  }
  cout << "  Connected to '" << dbAlias_target << "' database." << endl;
  }

  rc = tbloadcursor.CreateTargetTable();

  // Perform the Load on TARGET DATABASE
  rc = tbloadcursor.LoadData(bDoRemoteFetch,
                             dbAlias_source,
                             user_source,
                             pswd_source);

  // Drop table on TARGET DATABASE
  rc = tbloadcursor.DropTargetTable();

  // Drop table on SOURCE DATABASE
  if (bDoRemoteFetch)
  {
  cout << "\n  Disconnecting from '";
  cout << dbAlias_source << "' database..." << endl;

  EXEC SQL CONNECT RESET;
  EMB_SQL_CHECK("CONNECT RESET");

  cout << "  Disconnected from '";
  cout << dbAlias_source << "' database." << endl;

  cout << "\n  Connecting to '";
  cout << dbAlias_source << "' database..." << endl;
  if (strlen(user_source) == 0)
  {
    EXEC SQL CONNECT TO :dbAlias_source;
    EMB_SQL_CHECK("CONNECT");
  }
  else
  {
    EXEC SQL CONNECT TO :dbAlias_source USER :user_source USING :pswd_source;
    EMB_SQL_CHECK("CONNECT");
  }
  cout << "  Connected to '" << dbAlias_source << "' database." << endl;
  }

  rc = tbloadcursor.DropSourceTable();

  // Disconnect from the database
  cout << "\n  Disconnecting from '";
  cout << dbAlias_source << "' database..." << endl;

  EXEC SQL CONNECT RESET;
  EMB_SQL_CHECK("CONNECT RESET");

  cout << "  Disconnected from '";
  cout << dbAlias_source << "' database." << endl;

  return 0;
} // main