'*************************************************************************
' (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: XmlXQueryProcClient.vb
'
' SAMPLE: Call the stored procedure implemented in XmlXQueryProc.vb
'
' Steps to run the sample with command line window:
' 1. Compile the server source file with:
' nmake XML_XQuery_Proc
' OR
' bldXMLapp XmlXQueryProc
' 2. Erase the existing library/class files (if exists),
' XmlXQueryProc.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
'
' XmlXQueryProcClient
' Calls a stored procedure that accepts an XML document with extended
' promodate for the products and returns another XML document
' with Customer Information and excess amount paid by them.
' 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 XmlXQueryProcClient.vb file with bldapp.bat by entering
' the following at the command prompt:
'
' bldXMLapp XmlXQueryProcClient
'
' or compile XmlXQueryProcClient.vb with the makefile by entering the
' following at the command prompt:
'
' nmake XmlXQueryProcClient
'
' 2. Run the XmlXQueryProcClient program by entering the program name
' at the command prompt:
'
' XmlXQueryProcClient
'
'****************************************************************************
'
' 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 IBM.Data.DB2Types
Imports Microsoft.VisualBasic
Class Xquery_XmlProc_Client
Inherits XML_Util
Public Shared Sub Main(ByVal args() As String)
' Declare a DB2Connection
Dim Run_Sample As Xquery_XmlProc_Client = New Xquery_XmlProc_Client
Try
Console.WriteLine()
Console.WriteLine(vbCrLf & "THIS SAMPLE Call the stored procedure implemented in XML_XQuery_Proc.cs" & vbCrLf)
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
If Run_Sample.ConnectDb(args) Then
' Do Some Sample Stuff
'Different ways to create an index on XML columns
Run_Sample.Call_XML_Proc()
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
' callSupp_XML_Proc_Java procedure to call the stored procedure
Public Sub Call_XML_Proc()
Dim inXml As String = ""
Dim query As String = ""
Dim outXML As DB2Xml
Dim returnValue As String = ""
Dim cmd As DB2Command
Dim parm As DB2Parameter
cmd = dbconn.CreateCommand
Try
' prepare the CALL statement
query = "Xquery_Proc"
cmd.CommandText = query
cmd.CommandType = CommandType.StoredProcedure
' input data
inXml = vbCrLf & _
"<Suppliers>" & vbCrLf & _
" <Supplier id=""100"">" & vbCrLf & _
" <Products>" & vbCrLf & _
" <Product id=""100-100-01"">" & vbCrLf & _
" <ExtendedDate>" & vbCrLf & _
" 2007-01-02" & vbCrLf & _
" </ExtendedDate>" & vbCrLf & _
" </Product>" & vbCrLf & _
" <Product id= ""100-101-01"">" & vbCrLf & _
" <ExtendedDate>" & vbCrLf & _
" 2007-02-02" & vbCrLf & _
" </ExtendedDate>" & vbCrLf & _
" </Product>" & vbCrLf & _
" </Products>" & vbCrLf & _
" </Supplier>" & vbCrLf & _
" <Supplier id=""101"">" & vbCrLf & _
" <Products>" & vbCrLf & _
" <Product id=""100-103-01"">" & vbCrLf & _
" <ExtendedDate>" & vbCrLf & _
" 2007-03-22" & vbCrLf & _
" </ExtendedDate>" & vbCrLf & _
" </Product>" & vbCrLf & _
" </Products>" & vbCrLf & _
" </Supplier>" & vbCrLf & _
"</Suppliers>" & vbCrLf
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, 16384)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@retcode", DB2Type.Clob)
parm.Direction = ParameterDirection.Output
' call the stored procedure
Console.WriteLine(vbCrLf & "Calling stored procedure Xquery_Proc" & vbCrLf)
cmd.ExecuteReader()
Console.WriteLine("Xquery_Proc called successfully")
returnValue = CType(cmd.Parameters("@retcode").Value, String)
Console.WriteLine(vbCrLf & vbCrLf & " Return code : " & returnValue)
If Not cmd.Parameters("@outXml").Value.Equals(DBNull.Value) Then
outXML = CType(cmd.Parameters("@outXml").Value, DB2Xml)
If Not outXML.IsNull Then
Console.WriteLine(vbCrLf & vbCrLf & _
" Customers Inforrmation :" & vbCrLf & display_xml_parsed_struct(outXML.GetXmlReader))
Else
Console.WriteLine(vbCrLf & vbCrLf & _
" Customers Inforrmation : NULL " & vbCrLf)
End If
End If
Catch e As Exception
Console.WriteLine(e)
Console.WriteLine("--FAILED----")
End Try
End Sub
End Class