'*************************************************************************
' (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: XmlSimpleProc.vb
'
' SAMPLE: Code implementation of stored procedure XML_Simple_Proc
' The stored procedures defined in this program are called by the
' client application XmlSimpleProc.vb. Before building and
' running XmlSimpleProc.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 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
'
' Class Simple_XmlProc contains one method which solves the following scenario:
' This method will take Customer Information ( of type XML) as input ,
' 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.
'
' SQL Statements USED:
' CREATE
' SELECT
' INSERT
'
'***************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the XmlSimpleProc.vb file with bldapp.bat by entering
' the following at the command prompt:
'
' bldXMLapp XmlSimpleProc
'
' or compile XmlSimpleProc.vb with the makefile by entering the
' following at the command prompt:
'
' nmake XmlSimpleProc
'
' 2. Copy the XmlSimpleProc.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
Imports System.Data
Imports System.IO
Imports System.Text
Imports IBM.Data.DB2
Imports IBM.Data.DB2Types
Imports Microsoft.VisualBasic
Public Class XML_Simple_Proc
Public Shared Sub printmsg(ByVal log As FileStream, ByVal msg As String)
log.Write(Encoding.ASCII.GetBytes(msg), 0, Encoding.ASCII.GetBytes(msg).Length)
End Sub
Public Shared Sub Simple_Proc_NET(ByVal inXML As String, ByRef outXML As DB2Xml, ByRef retcode As String)
Dim cmd As DB2Command = Nothing
Dim reader As DB2DataReader = Nothing
Dim ReturnReader As DB2DataReader = Nothing
Dim custid As Integer = 0
Dim count As Integer = 0
Dim city As String = ""
Dim query As String = ""
' get the input XML document into an application variable
Dim ipdata As String = ""
retcode = "Start"
outXML = DB2Xml.Null
Try
ipdata = inXML.ToString
cmd = DB2Context.GetCommand
' find whether the customer with that Info exists in the customer table
query = vbCrLf & _
"SELECT " & vbCrLf & _
" CAST(COUNT(*) as INTEGER) " & vbCrLf & _
" FROM " & vbCrLf & _
" customer " & vbCrLf & _
" WHERE " & vbCrLf & _
" XMLEXISTS( " & vbCrLf & _
" ' " & vbCrLf & _
" $info/customerinfo[@Cid=$id] " & vbCrLf & _
" ' PASSING by ref " & vbCrLf & _
" cast(? as XML) AS ""info"", " & vbCrLf & _
" cid as ""id"" " & vbCrLf & _
" ) " & vbCrLf
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
cmd.Parameters.Add("@ipdate", DB2Type.Clob).Value = ipdata
reader = cmd.ExecuteReader
If reader.Read Then
count = reader.GetInt32(0)
Else
count = 0
End If
reader.Close()
' if customer doesn't exist ...... insert into the table
If (count < 1) Then
' get the custid from the customer information
query = vbCrLf & _
"SELECT " & vbCrLf & _
" XMLCAST( " & vbCrLf & _
" XMLQUERY( " & vbCrLf & _
" ' " & vbCrLf & _
" $info/customerinfo/@Cid " & vbCrLf & _
" ' passing by ref " & vbCrLf & _
" cast(? as XML) as ""info"" " & vbCrLf & _
" ) as INTEGER " & vbCrLf & _
" ) " & vbCrLf & _
" FROM " & vbCrLf & _
" SYSIBM.SYSDUMMY1 "
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
'As a note we are not binding ipdata a second time because it was already bound
'above to this command.
reader = cmd.ExecuteReader
If reader.Read Then
custid = reader.GetInt32(0)
Else
custid = 0
End If
reader.Close()
' insert into customer table with that custid
query = "INSERT INTO customer(Info, Cid) VALUES (?,?)"
cmd.CommandText = query
cmd.Prepare()
'As a note we are not binding ipdata a second time because it was already bound
'above to this command.
cmd.Parameters.Add("@custid", DB2Type.Integer).Value = custid
cmd.ExecuteNonQuery()
End If
' find the city of the customer and assign it to an application variable
query = vbCrLf & _
"SELECT " & vbCrLf & _
" XMLCAST( " & vbCrLf & _
" XMLQUERY( " & vbCrLf & _
" ' " & vbCrLf & _
" $info/customerinfo//city " & vbCrLf & _
" ' passing by ref " & vbCrLf & _
" cast(? as XML) as ""info"" " & vbCrLf & _
" ) as VARCHAR(100) " & vbCrLf & _
" ) " & vbCrLf & _
" FROM " & vbCrLf & _
" SYSIBM.SYSDUMMY1 " & vbCrLf
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
'cmd.Parameters.RemoveAt("@custid");
reader = cmd.ExecuteReader
If reader.Read Then
city = reader.GetString(0)
Else
city = ""
End If
reader.Close()
' select location fron the input XML and assign it to output parameter
query = vbCrLf & _
"SELECT " & vbCrLf & _
" XMLQUERY( " & vbCrLf & _
" ' " & vbCrLf & _
" let " & vbCrLf & _
" $city := $info/customerinfo//city " & vbCrLf & _
" let " & vbCrLf & _
" $prov := $info/customerinfo//prov-state " & vbCrLf & _
" return " & vbCrLf & _
" <Location> " & vbCrLf & _
" { " & vbCrLf & _
" $city, " & vbCrLf & _
" $prov " & vbCrLf & _
" } " & vbCrLf & _
" </Location>" & vbCrLf & _
" ' passing by ref " & vbCrLf & _
" cast(? as XML) as ""info"" " & vbCrLf & _
" ) " & vbCrLf & _
" FROM " & vbCrLf & _
" SYSIBM.SYSDUMMY1 " & vbCrLf
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
reader = cmd.ExecuteReader
reader.CacheData = True
If reader.Read Then
outXML = reader.GetDB2Xml(0)
Else
outXML = DB2Xml.Null
End If
reader.Close()
'cmd = DB2Context.GetCommand();
' findout all the customers from that city and return as an XML to caller
query = vbCrLf & _
"XQUERY " & vbCrLf & _
" for " & vbCrLf & _
" $cust in db2-fn:xmlcolumn( " & vbCrLf & _
" ""CUSTOMER.INFO"")/customerinfo/addr[city = """ & city.Trim() & """] " & vbCrLf & _
" return " & vbCrLf & _
" <Customer>" & vbCrLf & _
" { " & vbCrLf & _
" $cust/../@Cid " & vbCrLf & _
" } " & vbCrLf & _
" { " & vbCrLf & _
" $cust/../name " & vbCrLf & _
" } " & vbCrLf & _
" </Customer>" & vbCrLf
query = Regex.Replace(query, "\s+", " ")
cmd.CommandText = query
cmd.Prepare()
cmd.Parameters.Clear()
ReturnReader = cmd.ExecuteReader(CommandBehavior.Default)
retcode = "GOOD"
Catch e As Exception
Console.WriteLine(e)
Try
retcode = e.ToString
Catch err As Exception
retcode = "BAD"
End Try
End Try
Return
End Sub
End Class