/*************************************************************************
 * (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: XmlXQueryProcClient.cs
*
* SAMPLE: Call the stored procedure implemented in XmlXQueryProc.cs
*
* Steps to run the sample with command line window:
*             1. Compile the server source file with:
*                   nmake XML_XQuery_Proc
*                     OR
*                   bldXMLapp XmlXQueryProc
*             2. Erase the existing library/class files (if exists),
*                XmlXQueryProc.dll from the following path,
*                $(DB2PATH)\function.
*             3. Copy the class files, XmlXQueryProc.dll from the current
*                directory to the $(DB2PATH)\function.
*             4. Catalog the stored procedures in the database with:
*                  XML_spcat_xquery
*             5. Compile XmlXQueryProcClient with:
*                   nmake XmlXQueryProcClient
*                     OR
*                   bldXMLapp XmlXQueryProcClient
*             6. Run XmlXQueryProcClient with:
*                  XmlXQueryProcClient
*
* XmlXQueryProcClient
*      Calls a stored procedure that accepts an XML document with extended
*      promodate for the products and returns another XML document
*      with Customer Information and excess amount paid by them.
*        Parameter types used: IN  XML AS CLOB(5000)
*                              OUT XML AS CLOB(5000)
*                              OUT INTEGER 
* SQL Statements USED:
*         CALL
*
****************************************************************************
*
* Building and Running the sample program 
*
* 1. Compile the XmlXQueryProcClient.cs file with bldapp.bat by entering 
*    the following at the command prompt:
*
*      bldXMLapp XmlXQueryProcClient
*
*    or compile XmlXQueryProcClient.cs with the makefile by entering the
*    following at the command prompt:
*
*      nmake XmlXQueryProcClient
*
* 2. Run the XmlXQueryProcClient program by entering the program name
*     at the command prompt:
*
*      XmlXQueryProcClient
*
*****************************************************************************
*
* For more information on the sample programs, see the README file.
*
* For information on developing applications, see the Application
* Development Guide.
*
* 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/
*
****************************************************************************/

using System.Collections;
using System.Xml;
using System.Text.RegularExpressions;
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
using IBM.Data.DB2Types;

class Xquery_XmlProc_Client : XML_Util
{

  public static void Main(String[] args)
  {
    // Declare a DB2Connection
    Xquery_XmlProc_Client Run_Sample = new Xquery_XmlProc_Client();
    
    try
    {
      Console.WriteLine();
      Console.WriteLine("\nTHIS SAMPLE Call the stored procedure implemented in XML_XQuery_Proc.cs\n");

      // Connect to a database
      Console.WriteLine("  Connecting to a database ...");
      if (Run_Sample.ConnectDb(args))
      {
        // Do Some Sample Stuff
        //Different ways to create an index on XML columns
        Run_Sample.Call_XML_Proc();
      }
      else
        return;
    }
    catch (Exception e)
    {
      Console.WriteLine(e.Message);
    }

    // Disconnect from the database
    try
    {
      Console.WriteLine("\n  Disconnect from the database.");
      Run_Sample.Close();
    }
    catch (Exception e)
    {
      Console.WriteLine(e.Message);
    }
  } // Main

  // callSupp_XML_Proc_Java  procedure to call the stored procedure
  public void Call_XML_Proc()
  {
    string inXml = "";
    string query = "";

    DB2Xml outXML;
    string returnValue = "";

    DB2Command cmd;
    DB2Parameter parm;

    cmd = dbconn.CreateCommand();

    try
    {
      // prepare the CALL statement 
      query = "Xquery_Proc";

      cmd.CommandText = query;
      cmd.CommandType = CommandType.StoredProcedure;

      // input data
      inXml = @"
<Suppliers> 
  <Supplier id=""100""> 
    <Products>
      <Product id=""100-100-01"">
        <ExtendedDate>
          2007-01-02
        </ExtendedDate>
      </Product> 
      <Product id= ""100-101-01""> 
        <ExtendedDate>
          2007-02-02
        </ExtendedDate>
      </Product>
    </Products>
  </Supplier>
  <Supplier id=""101"">
    <Products>
      <Product id=""100-103-01"">
        <ExtendedDate>
          2007-03-22
        </ExtendedDate>
      </Product>
    </Products>
  </Supplier>
</Suppliers>
";
      inXml = Regex.Replace(inXml, "\\s+", " ");

      parm = cmd.Parameters.Add("@inXml", DB2Type.Clob);
      parm.Value = inXml;
      parm.Direction = ParameterDirection.Input;

      // register the output parameter
      parm = cmd.Parameters.Add("@outXml", DB2Type.Xml, 16384);
      parm.Direction = ParameterDirection.Output;

      parm = cmd.Parameters.Add("@retcode", DB2Type.Clob);
      parm.Direction = ParameterDirection.Output;


      // call the stored procedure
      Console.WriteLine("\nCalling stored procedure Xquery_Proc\n");

      cmd.ExecuteReader();

      Console.WriteLine("Xquery_Proc called successfully");

      returnValue = (string)cmd.Parameters["@retcode"].Value;
      Console.WriteLine("\n\n  Return code : " + returnValue);

     if (!cmd.Parameters["@outXml"].Value.Equals(DBNull.Value))
     {
        outXML = (DB2Xml)cmd.Parameters["@outXml"].Value;
       if (!outXML.IsNull)
       {
          Console.WriteLine("\n\n  Customers Inforrmation :\n" + display_xml_parsed_struct(outXML.GetXmlReader()));
       }
       else
         Console.WriteLine("\n\n Customers Inforrmation : NULL \n");
     }

      

    }
    catch (Exception e)
    {
      Console.WriteLine(e);
      Console.WriteLine("--FAILED----");
    }
  }
}