'*************************************************************************
' (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: XmlSimpleProcClient.cs
'
' SAMPLE: Call the stored procedure implemented in Simple_XmlProc.cs
'
' Steps to run the sample with command line window:
' 1. Compile the server source file with:
' nmake XmlSimpleProc
' OR
' bldXMLapp XmlSimpleProc
' 2. Erase the existing library/class files (if exists),
' XmlSimpleProc.dll from the following path,
' $(DB2PATH)\function.
' 3. Copy the class files, XmlSimpleProc.dll from the
' current directory to the $(DB2PATH)\function.
' 4. Catalog the stored procedures in the database with the script:
' Xml_Spcat
' 5. Compile XmlSimpleProcClient with:
' nmake XmlSimpleProcClient
' OR
' bldXMLapp XmlSimpleProcClient
' 6. Run XmlSimpleProcClient with:
' XmlSimpleProcClient
'
' XML_Simple_Proc_Client calls XML_Simple_Proc method that calls the stored
' This method will take Customer Information ( of type XML) as input,
' procedure: finds whether the customer with Cid in Customer
' Information exists in the customer table or not, if not this will
' insert the customer information into the customer table with same
' Customer id, and returns all the customers from the same city of
' the input customer information in XML format to the caller along
' with location as an output parameter in XML format.
'
' 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 XmlSimpleProcClient.vb file with bldapp.bat by entering
' the following at the command prompt:
'
' bldXMLapp XmlSimpleProcClient
'
' or compile XmlSimpleProcClient.vb with the makefile by entering
' the following at the command prompt:
'
' nmake XmlSimpleProcClient
'
' 2. Run the XmlSimpleProcClient program by entering the program name at the command
' prompt:
'
' XmlSimpleProcClient
'
'****************************************************************************
'
' 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
Public Class XML_Simple_Proc_Client
Inherits XML_Util
Public Shared Sub Main(ByVal args() As String)
' Declare a DB2Connection
Dim Run_Sample As XML_Simple_Proc_Client = New XML_Simple_Proc_Client
Try
Console.WriteLine()
Console.WriteLine(vbCrLf & "This sample calls the stored procedure implemented in XML_Simple_Proc.cs" & vbCrLf)
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
If Run_Sample.ConnectDb(args) Then
' Do Some Sample Stuff
Run_Sample.callSimple_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
' callSimple_Proc procedure to call the stored procedure
Public Sub callSimple_Proc()
Dim query As String = ""
Dim outXML As DB2Xml = DB2Xml.Null
Dim returnValue As String = ""
Dim cmd As DB2Command = Nothing
Dim parm As DB2Parameter = Nothing
Dim reader As DB2DataReader = Nothing
cmd = dbconn.CreateCommand
Try
' prepare the CALL statement
query = "XML_Simple_Proc_NET"
cmd.CommandText = query
cmd.CommandType = CommandType.StoredProcedure
' input data
Dim inXml As String = vbCrLf & _
"<customerinfo Cid=""5002"">" & vbCrLf & _
" <name>" & vbCrLf & _
" Kathy Smith " & vbCrLf & _
" </name>" & vbCrLf & _
" <addr country=""Canada"">" & vbCrLf & _
" <street>" & vbCrLf & _
" 25 EastCreek " & vbCrLf & _
" </street>" & vbCrLf & _
" <city>" & vbCrLf & _
" Markham" & _
" " & vbCrLf & _
" </city>" & vbCrLf & _
" <prov-state>" & vbCrLf & _
" Ontario " & vbCrLf & _
" </prov-state>" & vbCrLf & _
" <pcode-zip>" & vbCrLf & _
" N9C-3T6 " & vbCrLf & _
" </pcode-zip>" & vbCrLf & _
" </addr>" & vbCrLf & _
" <phone type=""work"">" & vbCrLf & _
" 905-566-7258 " & vbCrLf & _
" </phone>" & vbCrLf & _
"</customerinfo>" & 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)
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 XML_Simple_Proc" & vbCrLf)
reader = cmd.ExecuteReader
Console.WriteLine("XML_Simple_Proc called successfully")
fetchAll(reader)
' retrieve output parameters
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 & " Location is :" & vbCrLf & display_xml_parsed_struct(outXML.GetXmlReader))
Else
Console.WriteLine(vbCrLf & " " & vbCrLf & " Location is : NULL " & vbCrLf)
End If
End If
returnValue = CType(cmd.Parameters("@retcode").Value, String)
Console.WriteLine(vbCrLf & vbCrLf & " Return code : " & returnValue)
Catch e As Exception
Console.WriteLine(e)
Console.WriteLine("--FAILED----")
End Try
Return
End Sub
Public Sub fetchAll(ByVal rs As DB2DataReader)
Dim numOfColumns As Integer
Dim r As Integer = 0
Dim i As Integer = 0
Try
Console.WriteLine("=============================================================")
' retrieve the number, types and properties of the
' resultset's columns
numOfColumns = rs.FieldCount
Console.WriteLine(vbCrLf & vbCrLf & " Procedure returned :")
While rs.Read
r = (r + 1)
Console.Write(vbCrLf & r & ":" & vbCrLf & display_xml_parsed_struct(rs.GetXmlReader(0)))
i = 1
Do While (i < numOfColumns)
Console.Write("," & vbCrLf & " ")
Console.Write(rs.GetString(i))
i = (i + 1)
Loop
Console.WriteLine()
End While
Catch e As Exception
Console.WriteLine("Error: fetchALL: exception" & vbCrLf & e.ToString())
End Try
End Sub
End Class