/*************************************************************************
* (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: XmlSimpleProcClient.cs
*
* SAMPLE: Call the stored procedure implemented in Simple_XmlProc.cs
*
* Steps to run the sample with command line window:
* 1. Compile the server source file with:
* nmake XmlSimpleProcClient
* OR
* bldXMLapp XmlSimpleProcClient
* 2. Erase the existing library/class files (if exists),
* XmlSimpleProcClient.dll from the following path,
* $(DB2PATH)\function.
* 3. Copy the class files, XML_Simple_Proc.dll from the
* current directory to the $(DB2PATH)\function.
* 4. Catalog the stored procedures in the database with the script:
* spcat_xml
* 5. Compile XmlSimpleProcClientClient with:
* nmake XmlSimpleProcClientClient
* OR
* bldXMLapp XmlSimpleProcClientClient
* 6. XmlSimpleProcClientClient with:
* XmlSimpleProcClientClient
*
* XML_Simple_Proc_Client calls XML_Simple_Proc method that calls the stored
* This method will take Customer Information ( of type XML) as input,
* procedure: finds whether the customer with Cid in Customer
* Information exists in the customer table or not, if not this will
* insert the customer information into the customer table with same
* Customer id, and returns all the customers from the same city of
* the input customer information in XML format to the caller along
* with location as an output parameter in XML format.
*
* 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 XmlSimpleProcClient.cs file with bldapp.bat by entering
* the following at the command prompt:
*
* bldXMLapp XmlSimpleProcClient
*
* or compile XmlSimpleProcClient.cs with the makefile by entering
* the following at the command prompt:
*
* nmake XmlSimpleProcClient
*
* 2. Run the XmlSimpleProcClient program by entering the program
* name at the command prompt:
*
* XmlSimpleProcClient
*
*****************************************************************************
*
* 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;
public class XML_Simple_Proc_Client : XML_Util
{
public static void Main(String[] args)
{
// Declare a DB2Connection
XML_Simple_Proc_Client Run_Sample = new XML_Simple_Proc_Client();
try
{
Console.WriteLine();
Console.WriteLine("\nThis sample calls the stored procedure implemented in XML_Simple_Proc.cs\n");
// Connect to a database
Console.WriteLine(" Connecting to a database ...");
if (Run_Sample.ConnectDb(args))
{
// Do Some Sample Stuff
Run_Sample.callSimple_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
// callSimple_Proc procedure to call the stored procedure
public void callSimple_Proc()
{
string query = "";
DB2Xml outXML = DB2Xml.Null;
string returnValue = "";
DB2Command cmd = null;
DB2Parameter parm = null;
DB2DataReader reader = null;
cmd = dbconn.CreateCommand();
try
{
// prepare the CALL statement
query = "XML_Simple_Proc_NET";
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
// input data
String inXml = @"
<customerinfo Cid=""5002"">
<name>
Kathy Smith
</name>
<addr country=""Canada"">
<street>
25 EastCreek
</street>
<city>
Markham
</city>
<prov-state>
Ontario
</prov-state>
<pcode-zip>
N9C-3T6
</pcode-zip>
</addr>
<phone type=""work"">
905-566-7258
</phone>
</customerinfo>
";
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);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@retcode", DB2Type.Clob);
parm.Direction = ParameterDirection.Output;
// call the stored procedure
Console.WriteLine("\nCalling stored procedure XML_Simple_Proc\n");
reader = cmd.ExecuteReader();
Console.WriteLine("XML_Simple_Proc called successfully");
fetchAll(reader);
// retrieve output parameters
if (!cmd.Parameters["@outXml"].Value.Equals(DBNull.Value))
{
outXML = (DB2Xml)cmd.Parameters["@outXml"].Value;
if (!outXML.IsNull)
Console.WriteLine("\n \n Location is :\n" + display_xml_parsed_struct(outXML.GetXmlReader()) );
else
Console.WriteLine("\n \n Location is : NULL \n");
}
returnValue = (string)cmd.Parameters["@retcode"].Value;
Console.WriteLine("\n\n Return code : " + returnValue);
}
catch (Exception e)
{
Console.WriteLine(e);
Console.WriteLine("--FAILED----");
}
return;
}
public void fetchAll(DB2DataReader rs)
{
int numOfColumns;
int r = 0;
int i = 0;
try
{
Console.WriteLine("=============================================================");
// retrieve the number, types and properties of the
// resultset's columns
numOfColumns = rs.FieldCount;
Console.WriteLine("\n\n Procedure returned :");
while (rs.Read())
{
r++;
Console.Write("\n" + r + ":\n" + display_xml_parsed_struct(rs.GetXmlReader(0)));
for (i = 1; i < numOfColumns; i++)
{
Console.Write(",\n ");
Console.Write(rs.GetString(i));
}
Console.WriteLine();
}
}
catch (Exception e)
{
Console.WriteLine("Error: fetchALL: exception\n" + e);
}
} // fetchAll
}