/***************************************************************************
* (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: XmlSQLXQuery.cs
*
* SAMPLE: How to run SQL/XML Queries
*
* SQL Statements USED:
* SELECT
*
* SQL/XML STATEMENTS USED:
* XMLQUERY
* XMLEXISTS
*
****************************************************************************
*
* Building and Running the sample program
*
* 1. Compile the XmlSQLXQuery.cs file with bldapp.bat by entering the following
* at the command prompt:
*
* bldXMLapp XmlSQLXQuery
*
* or compile XmlSQLXQuery.cs with the makefile by entering the following at
* the command prompt:
*
* nmake XmlSQLXQuery
*
* 2. Run the XmlSQLXQuery program by entering the program name at the command
* prompt:
*
* XmlSQLXQuery
*
*****************************************************************************
*
* 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;
class SqlXQuery : XML_Util
{
public static void Main(String[] args)
{
// Declare a DB2Connection
SqlXQuery Run_Sample = new SqlXQuery();
string custName = "Robert Shoemaker";
string partID = "100-101-01";
try
{
Console.WriteLine();
Console.WriteLine("This sample will demonstrate how to run SQL/XML Queries\n");
// Connect to a database
Console.WriteLine(" Connecting to a database ...");
if (Run_Sample.ConnectDb(args))
{
// Do Some Sample Stuff
Run_Sample.firstPO1(custName);
Run_Sample.firstPO2(custName);
Run_Sample.sortCust_PO();
Run_Sample.numPO(custName, partID);
}
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
// The firstPO1 void returns the first item in the purchase order for customer custName passed as an argument
public void firstPO1(string custName)
{
string query;
DB2Command cmd;
DB2DataReader reader;
Console.WriteLine(@"
---------------------------------------------------------------------------
RETURN THE FIRST ITEM IN THE PURCHASEORDER FOR THE CUSTOMER " + custName + @"......
");
cmd = dbconn.CreateCommand();
try
{
query = @"
SELECT
XMLQUERY(
'
$p/PurchaseOrder/item[1]
'
passing p.porder AS ""p""
)
FROM
PURCHASEORDER AS p,
CUSTOMER AS c
WHERE
XMLEXISTS(
'
$custinfo/customerinfo[name=$c and @Cid = $cid]
'
passing c.info AS ""custinfo"",
p.custid AS ""cid"",
cast(? as varchar(20)) as ""c""
)
";
Console.WriteLine(query);
query = Regex.Replace(query, "\\s+", " ");
cmd.CommandText = query;
cmd.Prepare();
// Set the value for the parameter marker
Console.WriteLine("\nSet the value of the parameter: $custName");
cmd.Parameters.Add("@custName", DB2Type.VarChar).Value = custName;
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
// Prints a formatted version of the xml tree that is returned
Console.WriteLine("|___________________________________________________");
Console.Write(display_xml_parsed_struct(reader.GetXmlReader(0), "| "));
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // firstPO1
// The firstPO2 void returns the first item in the purchaseorder when
// Name is from the sequence (X,Y,Z)
// or the customer id is from the sequence (1000,1002,1003)
public void firstPO2(string custName)
{
string query;
DB2Command cmd;
DB2DataReader reader;
Console.WriteLine(@"
---------------------------------------------------------------------------
RETURN THE FIRST ITEM IN THE PURCHASEORDER WHEN THE CUSTOMER IS IN SEQUENCE
(X,Y,Z) AND CUSTOMER ID IN THE SEQUENCE (1001,1002,1003)........
");
cmd = dbconn.CreateCommand();
try
{
query = @"
SELECT
CID,
XMLQUERY(
'
$custinfo/customerinfo/name
' passing c.info AS ""custinfo""
) AS NAME,
XMLQUERY(
'
$p/PurchaseOrder/item[1]
' passing p.porder AS ""p""
) as PURCHASEORDER,
XMLQUERY(
'
$x/history
' passing c.history AS ""x""
) AS HISTORY
FROM
PURCHASEORDER AS p,
CUSTOMER AS c
WHERE
XMLEXISTS(
'
$custinfo/customerinfo[name=(X,Y,Z)
or @Cid=(1000,1002,1003) and @Cid=$cid ]
' passing c.info AS ""custinfo"", p.custid AS ""cid""
)
";
Console.WriteLine(query);
query = Regex.Replace(query, "\\s+", " ");
cmd.CommandText = query;
cmd.Prepare();
// Set the value for the parameter marker
Console.WriteLine("\nSet the value of the parameter: " + custName);
cmd.Parameters.Add("@custName", DB2Type.VarChar).Value = custName;
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
// Prints a formatted version of the xml tree that is returned
Console.WriteLine("|___________________________________________________");
// Print the customer id
Console.WriteLine("\nCid: \n" + reader.GetString(0));
// Print the name as DB2 String
Console.WriteLine("Name: \n" + display_xml_parsed_struct(reader.GetXmlReader(1)));
// Print the first item in the purchaseorder as DB2 XML String
Console.WriteLine("First Item in purchaseorder : \n" + display_xml_parsed_struct(reader.GetXmlReader(2)));
// Retrieve the history for the customer
// Print the history of the customer as DB2 XML String
Console.WriteLine("History: " + display_xml_parsed_struct(reader.GetXmlReader(3)));
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // firstPO2
// The sortCust_PO void sort the customers according to the number of purchaseorders
public void sortCust_PO()
{
string query;
DB2Command cmd;
DB2DataReader reader;
Console.WriteLine(@"
--------------------------------------------------------------------------
SORT THE CUSTOMERS ACCORDING TO THE NUMBER OF PURCHASEORDERS...........
");
cmd = dbconn.CreateCommand();
try
{
query = @"
WITH count_table AS
(
SELECT
count(poid) as COUNT_POID,
custid
FROM
PURCHASEORDER,
CUSTOMER
WHERE
cid=custid
GROUP BY
custid
)
SELECT
COUNT_POID,
XMLQUERY(
'
$s/customerinfo[@Cid=$id]/name
' passing
CUSTOMER.INFO AS ""s"",
count_table.custid as ""id""
) AS CUSTOMER
FROM
CUSTOMER,
count_table
WHERE
custid=cid
ORDER BY
COUNT_POID
";
Console.WriteLine(query);
query = Regex.Replace(query, "\\s+", " ");
cmd.CommandText = query;
cmd.Prepare();
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
// Prints a formatted version of the xml tree that is returned
Console.WriteLine("|___________________________________________________");
Console.Write("|COUNT : " + reader.GetString(0) + "\n CUSTOMER : \n" + display_xml_parsed_struct(reader.GetXmlReader(1), "| "));
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // sortCust_PO
// The numPO void returns the number of purchaseorder having specific partid
// for the specific customer passed as an argument to the void
public void numPO(string name, string partId)
{
string query;
DB2Command cmd;
DB2DataReader reader;
Console.WriteLine(@"
---------------------------------------------------------------------------
RETURN THE NUMBER OF PURCHASEORDER FOR THE CUSTOMER " + name + @" HAVING THE
PARTID " + partId + @"......
");
cmd = dbconn.CreateCommand();
try
{
query = @"
WITH cid_table AS
(
SELECT
Cid
FROM
CUSTOMER
WHERE
XMLEXISTS(
'
$custinfo/customerinfo[name=$name]
' passing
CUSTOMER.INFO AS ""custinfo"",
cast(? as varchar(20)) as ""name""
)
)
SELECT
count(poid) as COUNT_POID
FROM
PURCHASEORDER,
cid_table
WHERE
XMLEXISTS(
'
$po/PurchaseOrder/item[partid=$id]
' passing
PURCHASEORDER.PORDER AS ""po"",
cast(? as varchar(20)) as ""id""
)
AND
PURCHASEORDER.CUSTID = cid_table.cid
";
Console.WriteLine(query);
query = Regex.Replace(query, "\\s+", " ");
cmd.CommandText = query;
cmd.Prepare();
// Set the value for the parameter marker
Console.WriteLine("\nSet the paramter value: " + name);
cmd.Parameters.Add("@name", DB2Type.VarChar).Value = name;
Console.WriteLine("\nSet the paramter value: " + partId);
cmd.Parameters.Add("@partId", DB2Type.VarChar).Value = partId;
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
// Prints a formatted version of the xml tree that is returned
Console.WriteLine("|___________________________________________________");
Console.Write("Number of purchase order with partid \"" + partId + "\" for customer \"" + name + "\" : " + reader.GetString(0));
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // numPO
} // SqlXQuery