'****************************************************************************
' (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: SpClient.vb
'
' SAMPLE: Call different types of stored procedures from SpServer.vb
'
'
'****************************************************************************
'
' Steps to run the sample with the command line window:
'
' I) If you have a compatible nmake program on your system,
' do the following:
'
' 1. Compile this server source file, SpServer.vb. This
' erases any existing assembly of the same name and copies
' the newly compiled assembly SpServer.dll from the
' current directory to the $(DB2PATH)\function directory).
' This also runs a batchfile SpCat that executes SQL to
' drop (if already existing) and create the stored
' procedures in DB2 that have their procedure-body
' implemented by the methods in this file, SpServer.vb:
'
' nmake/make SpServer
'
' To run SpClient successfully you must have succesfully
' completed step 1 in order to be able to call the stored
' procedures.
'
' 2. Compile the client source file SpClient.vb.
'
' nmake/make SpClient
'
' 3. Run the client SpClient:
'
' SpClient
'
' II) If you don't have a compatible nmake program on your
' system do the following:
'
' 1. Compile the SpServer.vb file with bldrn.bat, to compile
' the C# stored procedure implementations that the client
' application will call. Do this by entering the following
' at the command prompt:
'
' bldrtn SpServer
'
' This will compile SpServer.vb and copy the assembly file
' SpServer.dll from the current directory to the DB2 function
' directory: $(DB2PATH)\function. It will first remove any
' existing assemblies with the same name first.
'
' 2. Create the stored procedures in DB2 by entering the following at
' the command prompt:
'
' spcat
'
' This will drop the procedures from DB2, if they exist, and
' then executes SQL CREATE PROCEDURE statements to create the
' procedures in DB2. These statements associate each procedure
' name with an external C# method in the assembly SpServer.dll
' that is in the DB2 function directory.
'
' 3. Compile the SpClient.vb file with the bldapp.bat batch file by
' entering the following at the command prompt:
'
' bldapp SpClient
'
' 4. Run the SpClient program by entering the program name at
' the command prompt:
'
' SpClient
'
' SpClient calls several methods that call the stored procedures:
'
' 1. CallOutLanguage: returns language of the stored procedure
' library
' Parameter types used: OUT CHAR(8), OUT INTEGER, OUT VARCHAR(32)
' 2. CallOutParameter: return median salary of EMPLOYEE table
' Parameter types used: OUT DECIMAL, OUT INTEGER, OUT VARCHAR(32)
'
' 3. CallInParameters: accepts 3 salary values and a department name
' and updates the salaries in the EMPLOYEE table according to some logic
' Parameter types used: IN DECIMAL(9,2)
' IN DECIMAL(9,2)
' IN DECIMAL(9,2)
' IN CHAR(3)
' OUT INTEGER,
' OUT VARCHAR(32)
' 4. CallInOutParameter: accepts an input value and returns
' the median salary of the EMPLOYEE table for employees who
' make more than the input value.
' Parameter types used: INOUT DECIMAL, OUT INTEGER, OUT VARCHAR(32)
'
' 5. CallOneResultSet: returns a result set to the client
' application
' Parameter types used: IN DECIMAL, OUT INTEGER, OUT VARCHAR(32)
'
' 6. callClobExtract: returns a string sub-section of a CLOB
' Parameter types used: IN CHAR(6)
' OUT VARCHAR(1000)
' OUT INTEGER
' OUT VARCHAR(32)
' 7. CallAllDataTypes: uses all of the common data types in a
' stored procedure
' Parameter types used: INOUT SMALLINT
' INOUT INTEGER
' INOUT BIGINT
' INOUT REAL
' INOUT DOUBLE
' OUT CHAR(1)
' OUT CHAR(15)
' OUT VARCHAR(12)
' OUT DATE
' OUT TIME
' OUT INTEGER
' OUT VARCHAR(32)
'
' *****************************************************************************
' The .NET stored procedures in this sample are implemented using
' parameter style GENERAL; this is the recommended parameter style.
'
' For examples of .NET procedures written in other parameter styles,
' please see the related documentation topics in the Information Center
' or in the Application Development Guide: Server Applications.
' Search for: Examples of CLR procedures in C#
' *****************************************************************************
'
' SQL Statements USED:
' CALL
' SELECT
'
' DB2 .NET Data Provider Classes USED:
' DB2Connection
' DB2Command
' DB2Transaction
'
' SpClient SpClient
'******************************************************************************
'
'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
Imports System.Data
Imports Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class SpClient
Shared Dim outMedian As Decimal = 0 ' Global variable
' - set by CallOutParameter
Public Shared Sub Main(args() As String)
' Declare a DB2Connection and a DB2Transaction
Dim conn As DB2Connection
Dim trans As DB2Transaction
' Declare a String to store the output from CallOutLanguage()
Dim language As String = ""
Try
Console.WriteLine( _
vbNewLine & _
" THIS SAMPLE SHOWS HOW TO CALL THE STORED" & _
" PROCEDURES IN SpServer.vb")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
conn = ConnectDb(args)
' Return the language in which the stored procedures are implemented
trans=conn.BeginTransaction()
language = CallOutLanguage(conn, trans)
' Display the mean salary of the EMPLOYEE table
trans=conn.BeginTransaction()
CallOutParameter(conn, trans)
' Update salaries in the EMPLOYEE table
trans=conn.BeginTransaction()
CallInParameters(conn, trans)
' Display the median salary of the EMPLOYEE table for employees who
' make more than 'outMedian'
trans=conn.BeginTransaction()
CallInoutParameter(conn, outMedian, trans)
' Display the median salary of the EMPLOYEE table for employees who
' make more than 99999.99
trans=conn.BeginTransaction()
CallInoutParameter(conn, 99999.99, trans)
' Obtains and displays a result set
trans=conn.BeginTransaction()
CallOneResultSet(conn, trans)
' Obtain and display a sub-section of a CLOB data type
trans=conn.BeginTransaction()
CallClobExtract("000140", conn, trans)
' Use all the common data types in a stored procedure
trans=conn.BeginTransaction()
CallAllDataTypes(conn, trans)
' Disconnect from the database
Console.WriteLine( _
vbNewLine & " Disconnect from the database")
conn.Close()
Catch e As Exception
Console.WriteLine(e.Message)
Try
conn.Close()
Catch x As Exception
Console.WriteLine(x.Message)
End Try
End Try
End Sub ' Main
' This method establishes a connection to a database
Public Shared Function ConnectDb(argv() As String) As DB2Connection
Dim server As String
Dim dbalias As String
Dim userId As String
Dim password As String
Dim portNumber As Int32 = -1
Dim connectString As String
If (argv.Length > 5) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
Else
If (argv.Length = 1) Then
If( String.Compare(argv(0),"?") = 0 Or _
String.Compare(argv(0),"-?") = 0 Or _
String.Compare(argv(0),"/?") = 0 Or _
String.Compare(argv(0),"-h",true) = 0 Or _
String.Compare(argv(0),"/h",true) = 0 Or _
String.Compare(argv(0),"-help",true) = 0 Or _
String.Compare(argv(0),"/help",true) = 0 ) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
End If
End If
End If
Select Case (argv.Length)
Case 0 ' Use all defaults
dbalias = "sample"
userId = ""
password = ""
Case 1 ' dbAlias specified
dbalias = argv(0)
userId = ""
password = ""
Case 2 ' userId & passwd specified
dbalias = "sample"
userId = argv(0)
password = argv(1)
Case 3 ' dbAlias, userId & passwd specified
dbalias = argv(0)
userId = argv(1)
password = argv(2)
Case 4 ' use default dbAlias
dbalias = "sample"
server = argv(0)
portNumber = Convert.ToInt32(argv(1))
userId = argv(2)
password = argv(3)
Case 5 ' everything specified
dbalias = argv(0)
server = argv(1)
portNumber = Convert.ToInt32(argv(2))
userId = argv(3)
password = argv(4)
End Select
If(portNumber = -1) Then
connectString = "Database=" & dbalias
Else
connectString = "Server=" & server & ":" & portNumber & _
";Database=" & dbalias
End If
If (userId <> "")
connectString += ";UID=" & userId & ";PWD=" & password
End If
Dim conn As DB2Connection = new DB2Connection(connectString)
conn.Open()
Console.WriteLine(" Connected to the " & dbalias & " database")
Return conn
End Function ' ConnectDb
' This method returns the language in which the stored procedures are
' implemented
Public Shared Function CallOutLanguage(conn As DB2Connection, _
trans As DB2Transaction) As String
Dim outLang As String = ""
Try
' Create a DB2Command to execute the stored procedure OUT_LANGUAGE
Dim procName As String = "OUT_LANGUAGE"
Dim callsmt As String = "CALL " & procName & "(?, ?, ?)"
Dim cmd As DB2Command = conn.CreateCommand()
Dim outReturnCode As Int32 = 0
Dim outErrorMsg As String = ""
Dim parm As DB2Parameter
cmd.Transaction = trans
cmd.CommandText = callsmt
cmd.CommandType = CommandType.Text
' Register output parameters for the DB2Command
parm = cmd.Parameters.Add("@language", DB2Type.Char, 8)
parm.Direction = ParameterDirection.Output
cmd.Parameters.Add("@outReturnCode", DB2Type.Integer)
parm.Direction = ParameterDirection.Output
cmd.Parameters.Add("@outErrorMsg", DB2Type.VarChar)
parm.Direction = ParameterDirection.Output
' Call the stored procedure
Console.WriteLine()
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
' Retrieve output parameters
outLang = CType(cmd.Parameters("@language").Value, String)
outReturnCode = CType(cmd.Parameters("@outReturnCode").Value, Integer)
outErrorMsg = CType(cmd.Parameters("@outErrorMsg").Value, String)
If outReturnCode < 0
Console.Writeline( _
" Stored procedure failed with return code: " & outReturnCode)
If outErrorMsg <> ""
Console.Writeline( _
" Error message: " & outErrorMsg.Trim())
Else
Console.Writeline( _
" No error message returned.")
End If
Else
Console.WriteLine( _
" Stored procedure is implemented in language " & outLang)
End If
' Rollback the transaction to restore SAMPLE
' database to original state
trans.Rollback()
Catch e As Exception
trans.Rollback()
Console.WriteLine(e)
End Try
Return outLang
End Function ' CallOutLanguage
' This method displays the mean salary of the EMPLOYEE table
Public Shared Sub CallOutParameter(conn As DB2Connection, _
trans As DB2Transaction)
Try
' Create a DB2Command to execute the stored procedure OUT_PARAM
Dim procName As String = "OUT_PARAM"
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procName
' Register the output parameters for the DB2Command
Dim parm As DB2Parameter = cmd.Parameters.Add("@medianSalary", _
DB2Type.Decimal, _
7)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outReturnCode", DB2Type.Integer)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outErrorMsg", DB2Type.VarChar, 32)
parm.Direction = ParameterDirection.Output
' Call the stored procedure
Console.WriteLine()
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
' Retrieve output parameters
outMedian = CType(cmd.Parameters("@medianSalary").Value, Decimal)
Dim outReturnCode As Int32
outReturnCode = CType(cmd.Parameters("@outReturnCode").Value, Int32)
Dim outErrorMsg As String
outErrorMsg = CType(cmd.Parameters("@outErrorMsg").Value, String)
If outReturnCode < 0
Console.Writeline( _
" Stored procedure failed with return code: " & outReturnCode)
If outErrorMsg <> ""
Console.Writeline( _
" Error message: " & outErrorMsg.Trim())
Else
Console.Writeline( _
" No error message returned.")
End If
Else
Console.WriteLine(" " & procName & " completed successfully")
Console.WriteLine(" Median salary returned from " & procName & _
" = " & FormatNumber(outMedian,2))
End If
' Rollback the transaction to restore SAMPLE
' database to original state
trans.Rollback()
Catch e As Exception
trans.Rollback()
Console.WriteLine(e)
End Try
End Sub ' CallOutParameter
' This method updates salaries in the EMPLOYEE table according to 3 values
Public Shared Sub CallInParameters(conn As DB2Connection, _
trans As DB2Transaction)
Try
' Create a DB2Command to execute a query from 'employee' and the
' CALL statement for IN_PARAMS
Dim procName As String = "IN_PARAMS"
Dim callsmt As String = "CALL " & procName & "(?, ?, ?, ?, ?, ?)"
Dim cmd As DB2Command = conn.CreateCommand()
Dim parm as DB2Parameter
Dim reader as DB2DataReader
cmd.Transaction = trans
' Display total salary before calling IN_PARAMS
cmd.CommandText = "SELECT SUM(salary) FROM employee WHERE workdept = ?"
cmd.Parameters.Add("@dept",DB2Type.Char,8).Value = "E11"
reader = cmd.ExecuteReader()
reader.Read()
Dim sumSalary As Decimal = CType(reader.GetDecimal(0), Decimal)
reader.Close()
Console.WriteLine()
Console.WriteLine(" Sum of salaries for dept. E11 = " & _
FormatNumber(sumSalary,2) & " before " & procName)
' Prepare DB2Command for calling IN_PARAMS and register input and
' output parameters
cmd.CommandText = callsmt
cmd.Parameters.RemoveAt("@dept")
parm = cmd.Parameters.Add("@lowSal", DB2Type.Decimal, 7)
parm.Direction = ParameterDirection.Input
parm = cmd.Parameters.Add("@medSal", DB2Type.Decimal, 7)
parm.Direction = ParameterDirection.Input
parm = cmd.Parameters.Add("@highSal", DB2Type.Decimal, 7)
parm.Direction = ParameterDirection.Input
parm = cmd.Parameters.Add("@dept", DB2Type.Char, 8)
parm.Direction = ParameterDirection.Input
parm = cmd.Parameters.Add("@outReturnCode", DB2Type.Integer)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outErrorMsg", DB2Type.VarChar, 32)
parm.Direction = ParameterDirection.Output
' Initialize parameters
cmd.Parameters("@lowSal").Value = 15000
cmd.Parameters("@medSal").Value = 20000
cmd.Parameters("@highSal").Value = 25000
cmd.Parameters("@dept").Value = "E11"
cmd.Parameters("@outReturnCode").Value = 0
cmd.Parameters("@outErrorMsg").Value = ""
' Call the stored procedure
Console.WriteLine()
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
' Retrieve output parameters
Dim outReturnCode As Int32
outReturnCode = CType(cmd.Parameters("@outReturnCode").Value, Int32)
Dim outErrorMsg As String
outErrorMsg = CType(cmd.Parameters("@outErrorMsg").Value, String)
If outReturnCode < 0
Console.Writeline( _
" Stored procedure failed with return code: " & outReturnCode)
If outErrorMsg <> ""
Console.Writeline( _
" Error message: " & outErrorMsg.Trim())
Else
Console.Writeline( _
" No error message returned.")
End If
Else
Console.WriteLine(" " & procName & " completed successfully")
' Display total salary after calling IN_PARAMS
cmd.CommandText = "SELECT SUM(salary)" & _
" FROM employee " & _
" WHERE workdept = ?"
cmd.Parameters.Clear()
cmd.Parameters.Add("@dept",DB2Type.Char,8).Value = "E11"
reader = cmd.ExecuteReader()
reader.Read()
sumSalary = CType(reader.GetDecimal(0), Decimal)
reader.Close()
Console.WriteLine(" Sum of salaries for dept. E11 = " & _
FormatNumber(sumSalary,2) & " after " & procName)
End IF
' Rollback the transaction to restore SAMPLE
' database to original state
trans.Rollback()
Catch e As Exception
trans.Rollback()
Console.WriteLine(e)
End Try
End Sub ' CallInParameters
' This method displays the median salary of the EMPLOYEE table for
' employees who make more than 'median'
Public Shared Sub CallInoutParameter(conn As DB2Connection, _
median As Decimal, _
trans As DB2Transaction)
Try
' Create a DB2Command to execute the stored procedure INOUT_PARAM
Dim procName As String = "INOUT_PARAM"
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procName
' Register input-output and output parameters for the DB2Command
Dim parm As DB2Parameter = cmd.Parameters.Add("@medianSalary", _
DB2Type.Decimal, _
7)
parm.Direction = ParameterDirection.InputOutput
' Set input parameter to median value passed back by OUT_PARAM
parm.Value = median
parm = cmd.Parameters.Add("@outReturnCode", DB2Type.Integer)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outErrorMsg", DB2Type.VarChar,32)
parm.Direction = ParameterDirection.Output
' Call the stored procedure
Console.WriteLine()
Console.WriteLine(" Call stored procedure named " & procName)
If (Equals(median,99999.99)) Then
Console.WriteLine(" with an input value that causes a NOT FOUND" & _
" error")
End If
cmd.ExecuteNonQuery()
' Retrieve output parameters
Dim inoutMedian As Decimal
Dim outReturnCode As Int32
Dim outErrorMsg As String
inoutMedian = CType(cmd.Parameters("@medianSalary").Value, Decimal)
outReturnCode = CType(cmd.Parameters("@outReturnCode").Value, String)
outErrorMsg = CType(cmd.Parameters("@outErrorMsg").Value, String)
If outReturnCode < 0
Console.Writeline( _
" Stored procedure failed with return code: " & outReturnCode)
If outErrorMsg <> ""
Console.Writeline( _
" Error message: " & outErrorMsg.Trim())
Else
Console.Writeline( _
" No error message returned.")
End If
Else
Console.WriteLine(" " & procName & " completed successfully")
Console.WriteLine(" Median salary returned from " & procName & _
" = " & FormatNumber(inoutMedian,2))
End If
' Rollback the transaction to restore SAMPLE
' database to original state
trans.Rollback()
Catch e As Exception
trans.Rollback()
Console.WriteLine(e)
End Try
End Sub ' CallInoutParameter
' This method obtains and displays a result set
Public Shared Sub CallOneResultSet(conn As DB2Connection, _
trans As DB2Transaction)
Try
' Create a DB2Command to execute the CALL statement for
' ONE_RESULT_SET
Dim procName As String = "ONE_RESULT_SET"
Dim callsmt As String = "CALL " & procName & "(?, ?, ?)"
Dim cmd As DB2Command = conn.CreateCommand()
Dim parm as DB2Parameter
cmd.Transaction = trans
cmd.CommandText = callsmt
cmd.CommandType = CommandType.Text
' Register input and output parameters for the CALL statement
parm = cmd.Parameters.Add("@salThreshold", DB2Type.Decimal, 7)
parm.Direction = ParameterDirection.Input
' Set input parameter to median value passed back by OUT_PARAM
parm.Value = outMedian
parm = cmd.Parameters.Add("@outReturnCode", DB2Type.Integer)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outErrorMsg", DB2Type.VarChar, 32)
parm.Direction = ParameterDirection.Output
' Call the stored procedure
Console.WriteLine()
Console.WriteLine(" Call stored procedure named " & procName)
Dim reader As DB2DataReader = cmd.ExecuteReader()
' Retrieve output parameters
Dim outReturnCode As Int32 = CType(cmd.Parameters("@outReturnCode").Value, _
Int32)
Dim outErrorMsg As String = CType(cmd.Parameters("@outErrorMsg").Value, _
String)
If outReturnCode < 0
Console.Writeline( _
" Stored procedure failed with return code: " & outReturnCode)
If outErrorMsg <> ""
Console.Writeline( _
" Error message: " & outErrorMsg.Trim())
Else
Console.Writeline( _
" No error message returned.")
End If
Else
Console.WriteLine(" " & procName & " completed successfully")
Console.WriteLine(" ==================================" & _
"=====================")
' Display the result set
DisplayResultSet(reader)
reader.Close()
End If
' Rollback the transaction to restore SAMPLE
' database to original state
trans.Rollback()
Catch e As Exception
trans.Rollback()
Console.WriteLine(e)
End Try
End Sub ' CallOneResultSet
' This method obtains and displays a sub-section of a CLOB data type
Public Shared Sub CallClobExtract(empNo As String, _
conn As DB2Connection, _
trans As DB2Transaction)
Dim outInterests As String
Try
' Create a DB2Command to execute the stored procedure CLOB_EXTRACT
Dim procName As String = "CLOB_EXTRACT"
Dim cmd As DB2Command = conn.CreateCommand()
Dim parm As DB2Parameter
cmd.Transaction = trans
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procName
' Register input and output parameters for the DB2Command
parm = cmd.Parameters.Add("@empno", DB2Type.Char, 6)
parm.Direction = ParameterDirection.Input
parm.Value = empNo
parm = cmd.Parameters.Add("@interests", DB2Type.VarChar, 1000)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outReturnCode", DB2Type.Integer)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outErrorMsg", DB2Type.VarChar, 32)
parm.Direction = ParameterDirection.Output
' Call the stored procedure
Console.WriteLine()
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
' Retrieve output parameters
outInterests = CType(cmd.Parameters("@interests").Value, String)
Dim outReturnCode As Int32 = CType(cmd.Parameters("@outReturnCode").Value, _
Int32)
Dim outErrorMsg As String = CType(cmd.Parameters("@outErrorMsg").Value, _
String)
If outReturnCode < 0
Console.Writeline( _
" Stored procedure failed with return code: " & outReturnCode)
If outErrorMsg <> ""
Console.Writeline( _
" Error message: " & outErrorMsg.Trim())
Else
Console.Writeline( _
" No error message returned.")
End If
Else
Console.WriteLine(" " & procName & " completed successfully")
Console.WriteLine(" Resume section returned for employee " _
& empNo & " =" & vbNewLine & _
vbNewLine & " " & outInterests)
End If
' Rollback the transaction to restore SAMPLE
' database to original state
trans.Rollback()
Catch e As Exception
trans.Rollback()
Console.WriteLine(e)
End Try
End Sub ' CallClobExtract
' This method uses all the common data types in a stored procedure
Public Shared Sub CallAllDataTypes(conn As DB2Connection, _
trans As DB2Transaction)
Try
' Create a DB2Command to execute the CALL statement for
' ALL_DATA_TYPES
Dim procName As String = "ALL_DATA_TYPES"
Dim callsmt As String = "CALL " & _
procName & _
"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = callsmt
cmd.CommandType = CommandType.Text
' Register input-output and output parameters for the CALL statement
' and initialize input-output parameters
Dim parm As DB2Parameter = cmd.Parameters.Add("@inoutSmallint", _
DB2Type.SmallInt)
parm.Direction = ParameterDirection.InputOutput
parm.Value = 32000
parm = cmd.Parameters.Add("@inoutInteger", DB2Type.Integer)
parm.Direction = ParameterDirection.InputOutput
parm.Value = 2147483000
parm = cmd.Parameters.Add("@inoutBigInt", DB2Type.BigInt)
parm.Direction = ParameterDirection.InputOutput
parm.Value = 2147483000
parm = cmd.Parameters.Add("@inoutReal", DB2Type.Real)
parm.Direction = ParameterDirection.InputOutput
parm.Value = 100000
parm = cmd.Parameters.Add("@inoutDouble", DB2Type.Double)
parm.Direction = ParameterDirection.InputOutput
parm.Value = 2500000
parm = cmd.Parameters.Add("@outChar", DB2Type.Char, 1)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outChars", DB2Type.Char, 15)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outVarchar", DB2Type.VarChar, 12)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outDate", DB2Type.Date)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outTime", DB2Type.Time)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outReturnCode", DB2Type.Integer)
parm.Direction = ParameterDirection.Output
parm = cmd.Parameters.Add("@outErrorMsg", DB2Type.VarChar,32)
parm.Direction = ParameterDirection.Output
' Call the stored procedure
Console.WriteLine()
Console.WriteLine(" Call stored procedure named " & procName)
Try
cmd.ExecuteNonQuery()
Catch
Console.Writeline("Error when calling procedure")
End Try
' Retrieve the return code output parameter and error message if any
Dim outReturnCode As Int32 = CType(cmd.Parameters("@outReturnCode").Value, _
Int32)
Dim outErrorMsg As String = CType(cmd.Parameters("@outErrorMsg").Value, _
String)
If outReturnCode < 0
Console.Writeline( _
" Stored procedure failed with return code: " & outReturnCode)
If outErrorMsg <> ""
Console.Writeline( _
" Error message: " & outErrorMsg.Trim())
Else
Console.Writeline( _
" No error message returned.")
End If
Else
Console.WriteLine(" " & procName & " completed successfully")
' Retrieve and display output parameters
Console.WriteLine(" Value of SMALLINT = " & _
cmd.Parameters("@inoutSmallint").Value)
Console.WriteLine(" Value of INTEGER = " & _
cmd.Parameters("@inoutInteger").Value)
Console.WriteLine(" Value of BIGINT = " & _
cmd.Parameters("@inoutBigint").Value)
Console.WriteLine(" Value of REAL = " & _
cmd.Parameters("@inoutReal").Value)
Console.WriteLine(" Value of DOUBLE = " & _
cmd.Parameters("@inoutDouble").Value)
Console.WriteLine(" Value of CHAR(1) = " & _
cmd.Parameters("@outChar").Value)
Console.WriteLine( _
" Value of CHAR(15) = " & _
cmd.Parameters("@outChars").Value.ToString().Trim())
Console.WriteLine( _
" Value of VARCHAR(12) = " & _
cmd.Parameters("@outVarchar").Value.ToString().Trim())
Console.WriteLine(" Value of DATE = " & _
cmd.Parameters("@outDate").Value)
Console.Write(" Value of TIME = ")
Console.Write(cmd.Parameters("@outTime").Value)
Console.WriteLine()
End If
' Rollback the transaction to restore SAMPLE
' database to original state
trans.Rollback()
Catch e As Exception
trans.Rollback()
Console.WriteLine(e)
End Try
End Sub ' CallAllDataTypes
' Helper method: This method displays the result set contained in the
' DB2DataReader
Public Shared Sub DisplayResultSet (reader As DB2DataReader)
Do While reader.Read()
Console.Write(" ")
Dim k As integer
For k=0 To reader.FieldCount-1
Dim str As String
If (Equals(reader.GetFieldType(k), _
Type.GetType("System.Decimal"))) Then
str = FormatNumber(CType(reader.GetValue(k),Decimal),2)
Else
str = reader.GetValue(k).ToString()
End If
If (str.Length < 9) Then
Console.Write(str.PadRight(9,Convert.ToChar(" ") ) & " ")
Else
Console.Write(str.Substring(0,9) & " ")
End If
Next k
Console.WriteLine()
Loop
End Sub ' DisplayResultSet
End Class ' SpClient