/*************************************************************************
* (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: XmlXQueryProc.ca
*
* SAMPLE: Code implementation of stored procedure Xquery_Proc
* The stored procedures defined in this program are called by the
* client application XmlXQueryProc.ca. Before building and
* running XmlXQueryProc.ca, build the shared library by
* completing the following steps:
*
* Steps to run the sample with command line window:
* 1. Compile the server source file with:
* nmake XmlXQueryProc
* OR
* bldXMLapp XmlXQueryProc
* 2. Erase the existing library/class files (if exists),
* XML_XQuery_Proc.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
*
* Class Xquery_XmlProc contains one method which solves the following scenario:
* Some of the suppliers have extended the promotional price date for
* their products. Getting all the customer's Information who purchased
* these products in the extended period will help the financial department
* to return the excess amount paid by those customers. The supplier
* information along with extended date's for the products is provided
* in an XML document and the client wants to have the information
* of all the customers who has paid the excess amount by purchasing those
* products in the extended period.
*
* This procedure will return an XML document containing customer info
* along with the the excess amount paid by them.
*
* SQL Statements USED:
* CREATE
* SELECT
* INSERT
*
****************************************************************************
*
* Building and Running the sample program
*
* 1. Compile the XML_Simple_Proc.cs file with bldapp.bat by entering
* the following at the command prompt:
*
* bldXMLapp XmlXQueryProc
*
* or compile XML_Simple_Proc.cs with the makefile by entering the
* following at the command prompt:
*
* nmake XmlXQueryProc
*
* 2. Copy the XmlXQueryProc.dll stored procedure to your \sqllib\function
*
*****************************************************************************
*
* 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.Text.RegularExpressions;
using System.Xml;
using System;
using IBM.Data.DB2;
using IBM.Data.DB2Types;
public class XML_XQuery_Proc
{
public static void Xquery_Proc(string inXML, out DB2Xml outXML, out string retcode)
{
DB2Command cmd;
DB2DataReader reader;
string query = "";
outXML = DB2Xml.Null;
retcode = "Start";
// get caller's connection to the database
cmd = DB2Context.GetCommand();
try
{
query = @"
CREATE TABLE TmpExtendedPromo(
ProductID VARCHAR(30),
PromoEnd DATE,
ExtendedDate DATE,
PromoPrice NUM(12,2)
)
";
query = Regex.Replace(query, "\\s+", " ");
cmd.CommandText = query;
cmd.Prepare();
cmd.ExecuteNonQuery();
query = @"
INSERT INTO TmpExtendedPromo
SELECT
Pid,
PromoEnd,
cast(extendedDate as DATE),
PromoPrice
FROM
product
INNER JOIN
XMLTABLE(
'$po/Suppliers/Supplier/Products/Product' PASSING cast(? as XML) as ""po""
COLUMNS
prod VARCHAR(30) PATH '@id',
extendedDate DATE PATH './ExtendedDate'
) as promotionExtend
ON product.pid = promotionExtend.prod
";
query = Regex.Replace(query, "\\s+", " ");
cmd.CommandText = query;
cmd.Prepare();
cmd.Parameters.Add("@ipdate", DB2Type.Clob).Value = inXML;
cmd.ExecuteNonQuery();
// finding out the toatal quantity of the product purchased by a customer
// if that order is made in between oldpromodate and extended promodate.
// this query will return the custid, product id and total quantity of
// that product purchased in all his orders.
// and
// the total excess amount to be paid to a customer for all the products
// store those results in another temporary table
// format the results into an XML document of the following form
// <Customers>
// <Customer>
// <Custid>XXXX</Custid>
// <Total>XXXX.XXXX</Total>
// <customerinfo Cid="xxxx">
// <name>xxxx xxx</name>
// <addr country="xxx>........
// </addr>
// <phone type="xxxx">.........
// </phone>
// </customerinfo>
// </Customer>............
// </Customers>
query = @"
XQUERY let $res:=db2-fn:sqlquery(
""
SELECT
XMLELEMENT(
NAME """"Customer"""",
(
XMLCONCAT(
XMLELEMENT(
NAME """"Custid"""",
CUSTID
),
XMLELEMENT(
NAME """"Total"""",
TOTAL
),
INFO
)
)
)
FROM
TABLE(
SELECT
CUSTID,
SUM( ( Price - PromoPrice ) * quantity) as TOTAL
FROM
TABLE(
SELECT
X.OrderDate,
P.CUSTID,
X.partid,
X.quantity,
X.Price
FROM
TABLE(
SELECT
CUSTID,
PORDER
FROM
PURCHASEORDER
WHERE
ORDERDATE > (SELECT PromoEnd FROM TmpExtendedPromo ORDER BY PromoEnd ASC FETCH FIRST 1 ROW ONLY)
AND
ORDERDATE < (SELECT ExtendedDate FROM TmpExtendedPromo ORDER BY ExtendedDate DESC FETCH FIRST 1 ROW ONLY)
) as P,
XMLTABLE ('$po/PurchaseOrder/item/partid' PASSING P.PORDER as """"po""""
COLUMNS
OrderDate VARCHAR(30) PATH '../../@OrderDate',
partid VARCHAR(30) PATH '.',
quantity INT PATH '../quantity',
price DECIMAL(12,2) PATH '../price'
) as X
) AS T1
INNER JOIN
TmpExtendedPromo
ON T1.PARTID = TmpExtendedPromo.ProductID
AND
T1.OrderDate < TmpExtendedPromo.extendedDate
AND
T1.OrderDate > TmpExtendedPromo.PromoEnd
AND
T1.Price > TmpExtendedPromo.PromoPrice
GROUP BY
CUSTID
) AS T
INNER JOIN
CUSTOMER AS C
ON C.CID = T.CUSTID
""
)
return
<Customers>
{
$res
}
</Customers>
";
query = Regex.Replace(query, "\\s+", " ");
cmd.CommandText = query;
cmd.Prepare();
cmd.Parameters.Clear();
reader = cmd.ExecuteReader();
/***********************************
***********************************
*
* This next line is important
* with out this you will not
* successfully assign the
* DB2Xml value to your
* out variable.
*
***********************************
***********************************/
reader.CacheData = true;
if (reader.Read())
{
outXML = reader.GetDB2Xml(0);
}
else
{
outXML = DB2Xml.Null;
}
reader.Close();
retcode = "Good";
}
catch (Exception e)
{
retcode = e.ToString();
}
finally
{
try
{
query = @"
DROP TABLE TmpExtendedPromo
";
query = Regex.Replace(query, "\\s+", " ");
cmd.CommandText = query;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
catch (Exception)
{
retcode += "\n\nDrop Table Failed!\n\n";
}
}
}
}