'***************************************************************************
' (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.vb
'
' 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.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldXMLapp XmlSQLXQuery
'
' or compile XmlSQLXQuery.vb 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/
'
'***************************************************************************/
Imports System.Collections
Imports System.Xml
Imports System.Text.RegularExpressions
Imports System
Imports System.Data
Imports System.IO
Imports IBM.Data.DB2
Imports Microsoft.VisualBasic
Class SqlXQuery
Inherits XML_Util
Public Shared Sub Main(ByVal args() As String)
' Declare a DB2Connection
Dim Run_Sample As SqlXQuery = New SqlXQuery
Dim custName As String = "Robert Shoemaker"
Dim partID As String = "100-101-01"
Try
Console.WriteLine()
Console.WriteLine("This sample will demonstrate how to run SQL/XML Queries" & vbCrLf)
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
If Run_Sample.ConnectDb(args) Then
' Do Some Sample Stuff
Run_Sample.firstPO1(custName)
Run_Sample.firstPO2(custName)
Run_Sample.sortCust_PO()
Run_Sample.numPO(custName, partID)
Else
Return
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Disconnect from the database
Try
Console.WriteLine(vbCrLf & " Disconnect from the database.")
Run_Sample.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
' Main
' The firstPO1 void returns the first item in the purchase order for customer custName passed as an argument
Public Sub firstPO1(ByVal custName As String)
Dim query As String
Dim cmd As DB2Command
Dim reader As DB2DataReader
Console.WriteLine(vbCrLf & _
"---------------------------------------------------------------------------" & vbCrLf & _
"RETURN THE FIRST ITEM IN THE PURCHASEORDER FOR THE CUSTOMER " _
& _
custName & _
"......" & vbCrLf & vbCrLf)
cmd = dbconn.CreateCommand
Try
query = vbCrLf & _
"SELECT" & vbCrLf & _
" XMLQUERY(" & vbCrLf & _
" '" & vbCrLf & _
" $p/PurchaseOrder/item[1]" & vbCrLf & _
" '" & vbCrLf & _
" passing p.porder AS ""p""" & vbCrLf & _
" )" & vbCrLf & _
" FROM" & vbCrLf & _
" PURCHASEORDER AS p," & vbCrLf & _
" CUSTOMER AS c" & vbCrLf & _
" WHERE" & vbCrLf & _
" XMLEXISTS(" & vbCrLf & _
" '" & vbCrLf & _
" $custinfo/customerinfo[name=$c and @Cid = $cid]" & vbCrLf & _
" '" & vbCrLf & _
" passing c.info AS ""custinfo""," & vbCrLf & _
" p.custid AS ""cid""," & vbCrLf & _
" cast(? as varchar(20)) as ""c""" & vbCrLf & _
" )" & vbCrLf & vbCrLf
Console.WriteLine(query)
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
' Set the value for the parameter marker
Console.WriteLine(vbCrLf & "Set 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), "| "))
End While
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
' 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 Sub firstPO2(ByVal custName As String)
Dim query As String
Dim cmd As DB2Command
Dim reader As DB2DataReader
Console.WriteLine(vbCrLf & _
"---------------------------------------------------------------------------" & vbCrLf & _
"RETURN THE FIRST ITEM IN THE PURCHASEORDER WHEN THE CUSTOMER IS IN SEQUENCE" & vbCrLf & _
"(X,Y,Z) AND CUSTOMER ID IN THE SEQUENCE (1001,1002,1003)........" & vbCrLf & vbCrLf)
cmd = dbconn.CreateCommand
Try
query = vbCrLf & _
"SELECT" & vbCrLf & _
" CID," & vbCrLf & _
" XMLQUERY(" & vbCrLf & _
" '" & vbCrLf & _
" $custinfo/customerinfo/name" & vbCrLf & _
" ' passing c.info AS ""custinfo""" & vbCrLf & _
" ) AS NAME," & vbCrLf & _
" XMLQUERY(" & vbCrLf & _
" '" & vbCrLf & _
" $p/PurchaseOrder/item[1]" & vbCrLf & _
" ' passing p.porder AS ""p""" & vbCrLf & _
" ) as PURCHASEORDER," & vbCrLf & _
" XMLQUERY(" & vbCrLf & _
" '" & vbCrLf & _
" $x/history" & vbCrLf & _
" ' passing c.history AS ""x""" & vbCrLf & _
" ) AS HISTORY" & vbCrLf & _
" FROM" & vbCrLf & _
" PURCHASEORDER AS p," & vbCrLf & _
" CUSTOMER AS c" & vbCrLf & _
" WHERE" & vbCrLf & _
" XMLEXISTS(" & vbCrLf & _
" '" & vbCrLf & _
" $custinfo/customerinfo[name=(X,Y,Z)" & vbCrLf & _
" or @Cid=(1000,1002,1003) and @Cid=$cid ]" & vbCrLf & _
" ' passing c.info AS ""custinfo"", p.custid AS ""cid""" & vbCrLf & _
" )" & vbCrLf & vbCrLf
Console.WriteLine(query)
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
' Set the value for the parameter marker
Console.WriteLine(vbCrLf & "Set 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(vbCrLf & "Cid: " & reader.GetString(0))
' Print the name as DB2 String
Console.WriteLine("Name: " & vbCrLf & display_xml_parsed_struct(reader.GetXmlReader(1)))
' Print the first item in the purchaseorder as DB2 XML String
Console.WriteLine("First Item in purchaseorder : " & vbCrLf & 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:" & vbCrLf & display_xml_parsed_struct(reader.GetXmlReader(3)))
End While
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
' firstPO2
' The sortCust_PO void sort the customers according to the number of purchaseorders
Public Sub sortCust_PO()
Dim query As String
Dim cmd As DB2Command
Dim reader As DB2DataReader
Console.WriteLine(vbCrLf & _
"--------------------------------------------------------------------------" & vbCrLf & _
"SORT THE CUSTOMERS ACCORDING TO THE NUMBER OF PURCHASEORDERS..........." & vbCrLf & vbCrLf)
cmd = dbconn.CreateCommand
Try
query = vbCrLf & _
"WITH count_table AS" & vbCrLf & _
"(" & vbCrLf & _
" SELECT" & vbCrLf & _
" count(poid) as COUNT_POID," & vbCrLf & _
" custid" & vbCrLf & _
" FROM" & vbCrLf & _
" PURCHASEORDER," & vbCrLf & _
" CUSTOMER" & vbCrLf & _
" WHERE" & vbCrLf & _
" cid=custid" & vbCrLf & _
" GROUP BY" & vbCrLf & _
" custid" & vbCrLf & _
")" & vbCrLf & _
"SELECT" & vbCrLf & _
" COUNT_POID," & vbCrLf & _
" XMLQUERY(" & vbCrLf & _
" '" & vbCrLf & _
" $s/customerinfo[@Cid=$id]/name" & vbCrLf & _
" ' passing" & vbCrLf & _
" CUSTOMER.INFO AS ""s""," & vbCrLf & _
" count_table.custid as ""id""" & vbCrLf & _
" ) AS CUSTOMER" & vbCrLf & _
" FROM" & vbCrLf & _
" CUSTOMER," & vbCrLf & _
" count_table" & vbCrLf & _
" WHERE" & vbCrLf & _
" custid=cid" & vbCrLf & _
" ORDER BY" & vbCrLf & _
" COUNT_POID" & vbCrLf & vbCrLf
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.WriteLine("|COUNT : " & reader.GetString(0) & vbCrLf & " CUSTOMER : " & vbCrLf & display_xml_parsed_struct(reader.GetXmlReader(1), "| "))
End While
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
' 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 Sub numPO(ByVal name As String, ByVal partId As String)
Dim query As String
Dim cmd As DB2Command
Dim reader As DB2DataReader
Console.WriteLine(vbCrLf & _
"---------------------------------------------------------------------------" & vbCrLf & _
"RETURN THE NUMBER OF PURCHASEORDER FOR THE CUSTOMER " & name & _
" HAVING THE" & vbCrLf & _
" PARTID " & partId & _
"......" & vbCrLf & vbCrLf)
cmd = dbconn.CreateCommand
Try
query = vbCrLf & _
"WITH cid_table AS" & vbCrLf & _
"(" & vbCrLf & _
" SELECT" & vbCrLf & _
" Cid" & vbCrLf & _
" FROM" & vbCrLf & _
" CUSTOMER" & vbCrLf & _
" WHERE" & vbCrLf & _
" XMLEXISTS(" & vbCrLf & _
" '" & vbCrLf & _
" $custinfo/customerinfo[name=$name]" & vbCrLf & _
" ' passing" & vbCrLf & _
" CUSTOMER.INFO AS ""custinfo""," & vbCrLf & _
" cast(? as varchar(20)) as ""name""" & vbCrLf & _
" )" & vbCrLf & _
")" & vbCrLf & _
"SELECT" & vbCrLf & _
" count(poid) as COUNT_POID" & vbCrLf & _
" FROM" & vbCrLf & _
" PURCHASEORDER," & vbCrLf & _
" cid_table" & vbCrLf & _
" WHERE" & vbCrLf & _
" XMLEXISTS(" & vbCrLf & _
" '" & vbCrLf & _
" $po/PurchaseOrder/item[partid=$id]" & vbCrLf & _
" ' passing" & vbCrLf & _
" PURCHASEORDER.PORDER AS ""po""," & vbCrLf & _
" cast(? as varchar(20)) as ""id""" & vbCrLf & _
" )" & vbCrLf & _
" AND" & vbCrLf & _
" PURCHASEORDER.CUSTID = cid_table.cid" & vbCrLf & vbCrLf
Console.WriteLine(query)
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
' Set the value for the parameter marker
Console.WriteLine(vbCrLf & "Set the paramter value: " & name)
cmd.Parameters.Add("@name", DB2Type.VarChar).Value = name
Console.WriteLine(vbCrLf & "Set 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.WriteLine("Number of purchase order with partid "" & partId & "" for customer "" & name & "" : " & reader.GetString(0))
End While
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
End Class
' SqlXQuery