'*************************************************************************
' (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.vb
'
' SAMPLE: Code implementation of stored procedure XQuery_Proc
' The stored procedures defined in this program are called by the
' client application XmlXQueryProc.vb. Before building and
' running XmlXQueryProc.vb, 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 XmlXQueryProc.cs file with bldapp.bat by entering
' the following at the command prompt:
'
' bldXMLapp XmlXQueryProc
'
' or compile XmlXQueryProc.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/
'
'***************************************************************************/
Imports System.Text.RegularExpressions
Imports System.Xml
Imports System
Imports IBM.Data.DB2
Imports IBM.Data.DB2Types
Imports Microsoft.VisualBasic
Public Class XML_XQuery_Proc
Public Shared Sub Xquery_Proc(ByVal inXML As String, ByRef outXML As DB2Xml, ByRef retcode As String)
Dim cmd As DB2Command
Dim reader As DB2DataReader
Dim query As String = ""
outXML = DB2Xml.Null
retcode = "Start"
' get caller's connection to the database
cmd = DB2Context.GetCommand
Try
query = " " & vbCrLf & _
"CREATE TABLE TmpExtendedPromo(" & vbCrLf & _
" ProductID VARCHAR(30)," & vbCrLf & _
" PromoEnd DATE," & vbCrLf & _
" ExtendedDate DATE," & vbCrLf & _
" PromoPrice NUM(12,2)" & vbCrLf & _
" )" & vbCrLf
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
cmd.ExecuteNonQuery()
query = vbCrLf & _
"INSERT INTO TmpExtendedPromo" & vbCrLf & _
" SELECT" & vbCrLf & _
" Pid," & vbCrLf & _
" PromoEnd," & vbCrLf & _
" cast(extendedDate as DATE)," & vbCrLf & _
" PromoPrice" & vbCrLf & _
" FROM" & vbCrLf & _
" product" & vbCrLf & _
" INNER JOIN" & vbCrLf & _
" XMLTABLE(" & vbCrLf & _
" '$po/Suppliers/Supplier/Products/Product' PASSING cast(? as XML) as ""po""" & vbCrLf & _
" COLUMNS " & vbCrLf & _
" prod VARCHAR(30) PATH '@id'," & vbCrLf & _
" extendedDate DATE PATH './ExtendedDate'" & vbCrLf & _
" ) as promotionExtend" & vbCrLf & _
" ON product.pid = promotionExtend.prod" & vbCrLf
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 = vbCrLf & _
"XQUERY " & vbCrLf & _
" let $res:=db2-fn:sqlquery(" & vbCrLf & _
" """ & vbCrLf & _
"SELECT" & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"Customer""""," & vbCrLf & _
" (" & vbCrLf & _
" XMLCONCAT(" & vbCrLf & _
" XMLELEMENT(" & vbCrLf & _
" NAME """"Custid""""," & vbCrLf & _
" CUSTID" & vbCrLf & _
" )," & vbCrLf & _
" XMLELEMENT( " & vbCrLf & _
" NAME """"Total""""," & vbCrLf & _
" TOTAL" & vbCrLf & _
" )," & vbCrLf & _
" INFO" & vbCrLf & _
" )" & vbCrLf & _
" )" & vbCrLf & _
" ) " & vbCrLf & _
" FROM" & vbCrLf & _
" TABLE(" & vbCrLf & _
" SELECT " & vbCrLf & _
" CUSTID," & vbCrLf & _
" SUM( ( Price - PromoPrice ) * quantity) as TOTAL" & vbCrLf & _
" FROM" & vbCrLf & _
" TABLE(" & vbCrLf & _
" SELECT " & vbCrLf & _
" X.OrderDate," & vbCrLf & _
" P.CUSTID, " & vbCrLf & _
" X.partid," & vbCrLf & _
" X.quantity," & vbCrLf & _
" X.Price" & vbCrLf & _
" FROM " & vbCrLf & _
" TABLE(" & vbCrLf & _
" SELECT " & vbCrLf & _
" CUSTID, " & vbCrLf & _
" PORDER " & vbCrLf & _
" FROM " & vbCrLf & _
" PURCHASEORDER" & vbCrLf & _
" WHERE" & vbCrLf & _
" ORDERDATE > (SELECT PromoEnd FROM TmpExtendedPromo ORDER BY PromoEnd ASC FETCH FIRST 1 ROW ONLY)" & vbCrLf & _
" AND" & vbCrLf & _
" ORDERDATE < (SELECT ExtendedDate FROM TmpExtendedPromo ORDER BY ExtendedDate DESC FETCH FIRST 1 ROW ONLY)" & vbCrLf & _
" ) as P, " & vbCrLf & _
" XMLTABLE (" & vbCrLf & _
" '$po/PurchaseOrder/item/partid' PASSING P.PORDER as """"po""""" & vbCrLf & _
" COLUMNS " & vbCrLf & _
" OrderDate VARCHAR(30) PATH '../../@OrderDate'," & vbCrLf & _
" partid VARCHAR(30) PATH '.'," & vbCrLf & _
" quantity INT PATH '../quantity'," & vbCrLf & _
" price DECIMAL(12,2) PATH '../price'" & vbCrLf & _
" ) as X" & vbCrLf & _
" ) AS T1" & vbCrLf & _
" INNER JOIN" & vbCrLf & _
" TmpExtendedPromo" & vbCrLf & _
" ON T1.PARTID = TmpExtendedPromo.ProductID" & vbCrLf & _
" AND" & vbCrLf & _
" T1.OrderDate < TmpExtendedPromo.extendedDate" & vbCrLf & _
" AND" & vbCrLf & _
" T1.OrderDate > TmpExtendedPromo.PromoEnd" & vbCrLf & _
" AND" & vbCrLf & _
" T1.Price > TmpExtendedPromo.PromoPrice" & vbCrLf & _
" GROUP BY" & vbCrLf & _
" CUSTID" & vbCrLf & _
" ) AS T" & vbCrLf & _
" INNER JOIN" & vbCrLf & _
" CUSTOMER AS C" & vbCrLf & _
" ON C.CID = T.CUSTID" & vbCrLf & _
" """ & vbCrLf & _
" )" & vbCrLf & _
" return " & vbCrLf & _
" <Customers>" & vbCrLf & _
" {" & vbCrLf & _
" $res" & vbCrLf & _
" }" & vbCrLf & _
" </Customers>" & vbCrLf
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
cmd.Parameters.Clear()
reader = cmd.ExecuteReader
reader.CacheData = True
If reader.Read Then
outXML = reader.GetDB2Xml(0)
Else
outXML = DB2Xml.Null
End If
reader.Close()
retcode = "Good"
Catch e As Exception
retcode = e.ToString
Finally
Try
query = " " & vbCrLf & "DROP TABLE TmpExtendedPromo" & vbCrLf
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
cmd.ExecuteNonQuery()
Catch err As Exception
retcode = (retcode & vbCrLf & vbCrLf & "Drop Table Failed!" & vbCrLf & vbCrLf)
End Try
End Try
End Sub
End Class