'*****************************************************************************
' (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: SpServer.vb
'
' SAMPLE: VB.NET implementation of procedures created with SpCat.bat
'
'*****************************************************************************
'
' 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 this file, SpServer.vb, containing the C# stored
' procedure methods that implement the stored procedure-bodies,
' with bldrn.bat. This batch file first erases any existing
' assembly of the same name. Then it will compile SpServer.vb
' and copy the assembly file SpServer.dll from the current
' directory to the DB2 function directory: $(DB2PATH)\function.
'
' bldrtn SpServer
'
'
' 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:
'
' Class SpServer contains the following methods:
'
' 1. outLanguage: Return language of the stored procedure library
' 2. outParameter: Return median salary of EMPLOYEE table
' 3. inParams: Accept 3 values, use them to update salaries in
' the EMPLOYEE table
' 4. inoutParam: Aaccept an input value and Return the median
' salary of the EMPLOYEE table for employees who
' make more than the input value
' 5. resultSetToClient: Return a result set to the client application
' 6. clobExtract: Return a section of a CLOB type as a string
' 7. allDataTypes: Use all of the common data types
'
' ****************************************************************************
' 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:
' SELECT
' UPDATE
'
' SpClient SpClient
'*****************************************************************************
'
' For more information on the sample programs, see the README file.
'
' For information on developing JDBC 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 SpServer
'****************************************************************
' Routine: OutLanguage
'
' Purpose: Returns the code implementation language of
' routine 'OutLanguage' (as it appears in the
' database catalog) in an output parameter.
'
' Shows how to:
' - define an OUT parameter in PARAMETER STYLE GENERAL
' - execute an SQL query
'
' Presumptions:
' - that there is only one routine OUT_LANGUAGE
' in the database. SpecIfically, that there are not
' two routines with the same name and dIfferent
' properties (dIfferent parameter data types)
' - The predicate can be modIfied to search for any
' other criteria.
'
' Parameters:
'
' IN: (none)
' OUT: outLang - The programming language of
' this routine's implementation
' outReturnCode - A Return code to indicate a caught error
' outErrorMsg - To hold an optional error message
'
'******************************************************************
Public Shared Sub OutLanguage(ByRef outLang As String, _
ByRef outReturnCode As Integer, _
ByRef outErrorMsg As String)
' OUT_LANGUAGE is the name of the DB2 stored procedure
' for which this is the code implementation
Dim myCommand As DB2Command
Dim myReader As DB2DataReader
Dim routineName As String
routineName = "OUT_LANGUAGE"
outReturnCode = 0
outErrorMsg = ""
Try
' The DB2 system tables, in schema SYSIBM, store
' information about the objects in the database.
' Query the sysroutines table to get the implementation
' language of the routine named OUT_LANGUAGE.
myCommand = DB2Context.GetCommand()
myCommand.CommandText = "SELECT LANGUAGE " _
+ "FROM sysibm.sysroutines " _
+ "WHERE routinename = '" + routineName + "'"
myReader = myCommand.ExecuteReader()
If (myReader.Read())
' Set the output parameter value and
' indicate that it is a non-NULL value
outLang = myReader.GetString(0)
Else
' Set the output parameter to an empty string.
' Set the ReturnCode tonull value
' Set the SQLSTATE to a user-defined state
' and the diagnostic message string with
' a meaningful error message - these will
' be passed back to the client in the SQLCA.
outLang = ""
outReturnCode = -1
outErrorMsg = "ERR1: OUT_LANGUAGE not found"
End If
myReader.Close()
Catch
myReader.Close
outReturnCode = -1
outErrorMsg = "ERR2: Retrieving OUT_LANGUAGE"
End Try
End Sub 'OutLanguage procedure
'***************************************************************
' Routine: OutParameter
'
' Purpose: Sorts table STAFF by salary, locates and Returns
' the median salary
'
' Shows how to:
' - define OUT parameters in PARAMETER STYLE GENERAL
' - set an ouput parameter
' - set an output Return code
' - set an output string parameter to indicate last
' SQL statement executed when an error is caught
' (useful for debugging)
'
' Parameters:
'
' IN: (none)
' OUT: outMedianSalary - median salary in table STAFF
' outReturnCode - A Return code to indicate caught errors
' outErrorMsg - To hold an optional error message
'
'*****************************************************************
Public Shared Sub OutParameter(ByRef outMedianSal As Decimal, _
ByRef outReturnCode As Integer, _
ByRef outErrorMsg As String)
Dim myCommand As DB2Command
Dim myReader As DB2DataReader
Dim numRecords As Int32
Dim medianSal As Decimal
Dim counter As Integer
counter = 0
outReturnCode = 0
outErrorMsg = ""
outMedianSal = 0
myCommand = DB2Context.GetCommand()
myCommand.CommandText = "SELECT COUNT(*) " _
+ "FROM staff"
Try
myReader = myCommand.ExecuteReader()
myReader.Read()
numRecords = myReader.GetInt32(0)
Catch
outReturnCode = -1
outErrorMsg = "ERR1: Getting count(*) from staff"
Return
End Try
If numRecords = 0
outReturnCode = -1
outErrorMsg = "ERR2: No records in staff table"
Return
End If
myReader.Close()
myCommand = DB2Context.GetCommand()
myCommand.CommandText = "SELECT salary " _
+ "FROM staff " _
+ "ORDER BY salary"
Try
myReader = myCommand.ExecuteReader()
Catch
outReturnCode = -1
outErrorMsg = "ERR3: Getting records in staff"
Return
End Try
If (myReader.HasRows = 0)
outReturnCode = -1
outErrorMsg = "ERR4: No records in staff table"
Return
End If
Try
' Move to record in the median position
While (counter < (CType((numRecords/2 +1), Int32)))
myReader.Read()
counter = counter + 1
End While
Catch
outReturnCode = -1
outErrorMsg = "ERR5: Iterating over records"
Return
End Try
' Set the output to the median salary
outMedianSal = myReader.GetDecimal(0)
myReader.Close()
End Sub ' OutParameter procedure
'***************************************************************
' Routine: InParameters
'
' Purpose: Updates the salaries of employees in department
' 'indept' using inputs inLowSal, inMedSal, inHighSal
' as salary salary adjustment values.
'
' Shows how to:
' - define IN parameters using PARAMETER STYLE GENERAL
' - execute SQL operations on the database
' - Catch exceptions and Return an output Returncode
' - Return an error message with a Return code
'
' Parameters:
'
' IN: inLowSal - new salary for low salary employees
' inMedSal - new salary for mid salary employees
' inHighSal - new salary for high salary employees
' inDept - department to use in SELECT predicate
' outReturnCode - A Return code to indicate caught errors
' outErrorMsg - To hold an optional error message
'
'*****************************************************************/
Public Shared Sub InParameters(ByVal inLowSal As Decimal, _
ByVal inMedSal As Decimal, _
ByVal inHighSal As Decimal, _
ByVal inDept As String, _
ByRef outReturnCode As Integer, _
ByRef outErrorMsg As String)
Dim numRecords As Int32
Dim parm as DB2Parameter
Dim myCommand as DB2Command
Dim myReader as DB2DataReader
numrecords = 0
outReturnCode = 0
outErrorMsg = ""
' Check input parameters
If (inLowSal < 0 Or _
inMedSal < 0 Or _
inHighSal < 0)
outReturnCode = -1
outErrorMsg = "ERR1: Bad input: salaries < 0"
Return
End IF
myCommand = DB2Context.GetCommand()
Try
myCommand.CommandText = "SELECT COUNT(*) " _
+ "FROM employee " _
+ "WHERE workdept = ?"
parm = myCommand.Parameters.Add("@inDept", DB2Type.Char, 3)
parm.Direction = ParameterDirection.Input
parm.Value = inDept
myReader = myCommand.ExecuteReader()
myReader.Read()
numRecords = myReader.GetInt32(0)
Catch
outReturnCode = -1
outErrorMsg = "ERR2: Reading COUNT(*)"
Return
End Try
If (numRecords = 0)
outReturnCode = -1
outErrorMsg = "ERR3: No records satisfy query"
Return
End If
myReader.Close()
Try
myCommand.CommandText = "MERGE INTO EMPLOYEE as T " _
+ "USING (SELECT empno, salary " _
+ "FROM employee " _
+ "WHERE workdept = ?) as S " _
+ "ON T.empno = S.empno " _
+ "WHEN MATCHED THEN " _
+ "UPDATE SET salary = " _
+ "(CASE WHEN salary < ? THEN ? " _
+ " WHEN salary < ? THEN ? " _
+ " WHEN salary < ? THEN ? " _
+ " ELSE salary * 1.10 " _
+ " END)"
' Set up parameter marker information for the query
myCommand.Parameters.Clear()
parm = myCommand.Parameters.Add("@inDept", DB2Type.Char, 3)
parm.Direction = ParameterDirection.Input
parm.Value = inDept
parm = myCommand.Parameters.Add("@inLowSal", DB2Type.Decimal, 9)
parm.Direction = ParameterDirection.Input
parm.Value = inLowSal
parm = myCommand.Parameters.Add("@inLowSalSet", DB2Type.Decimal, 9)
parm.Direction = ParameterDirection.Input
parm.Value = inLowSal
parm = myCommand.Parameters.Add("@inMedSal", DB2Type.Decimal, 9)
parm.Direction = ParameterDirection.Input
parm.Value = inMedSal
parm = myCommand.Parameters.Add("@inMedSalSet", DB2Type.Decimal, 9)
parm.Direction = ParameterDirection.Input
parm.Value = inMedSal
parm = myCommand.Parameters.Add("@inHighSal", DB2Type.Decimal, 9)
parm.Direction = ParameterDirection.Input
parm.Value = inHighSal
parm = myCommand.Parameters.Add("@inHighSalSet", DB2Type.Decimal, 9)
parm.Direction = ParameterDirection.Input
parm.Value = inHighSal
myCommand.ExecuteNonQuery()
Catch
outReturnCode = -1
outErrorMsg = "ERR4: Updating employee table"
Return
End Try
End Sub ' InParameters procedure
'***************************************************************
' Routine: InOutParameter
'
' Purpose: Calculates the median salary of all salaries above
' the input median salary.
'
' Shows how to:
' - define INOUT/OUT parameters with
' PARAMETER STYLE GENERA
' - Catch errors and set an output Return code
' - set an output string parameter to indicate where
' error was caught (useful for debugging)
'
' Parameters:
'
' IN/OUT: inOutMedian - median salary
' (input value used in SELECT predicate
' output set to median salary found)
' outReturnCode - A Return code to indicate caught errors
' outErrorMsg - To hold an optional error message
'*******************************************************************
Public Shared Sub InOutParameter(ByRef inoutMedianSal As Decimal, _
ByRef outReturnCode As Integer, _
ByRef outErrorMsg As String)
Dim counter As Integer
Dim numRecords As Int32
Dim parm As DB2Parameter
Dim myCommand As DB2Command
Dim myReader As DB2DataReader
counter = 0
numRecords= 0
outReturnCode = 0
outErrorMsg = ""
Try
myCommand = DB2Context.GetCommand()
myCommand.CommandText = "SELECT COUNT(*)" _
+ " FROM staff " _
+ " WHERE salary > ? "
parm = myCommand.Parameters.Add("@inoutMedianSal", DB2Type.Decimal, 7)
parm.Direction = ParameterDirection.Input
parm.Value = inoutMedianSal
myReader = myCommand.ExecuteReader()
myReader.Read()
numRecords = myReader.GetInt32(0)
myReader.Close()
Catch
outReturnCode = -1
outErrorMsg = "ERR1: COUNT(*) from staff failed"
Return
End Try
If (numRecords = 0)
outReturnCode = -1
outErrorMsg = "ERR2: No records satisfy query"
Return
End If
Try
myCommand.CommandText = "SELECT salary" _
+ " FROM staff " _
+ " WHERE salary > ?" _
+ " ORDER BY salary"
' Reuse existing parameter marker values
myCommand.Parameters.Clear()
parm = myCommand.Parameters.Add("@inoutMedianSal2", DB2Type.Decimal, 7)
parm.Direction = ParameterDirection.Input
parm.Value = inoutMedianSal
myReader = myCommand.ExecuteReader()
While (counter < (CType(numRecords/2, Int32) + 1))
myReader.Read()
counter = counter + 1
End While
Catch
outReturnCode = -1
outErrorMsg = " ERR3: Iterating records in myReader"
Return
End Try
Try
' Set the input/output parameter value before Returning
inoutMedianSal = myReader.GetDecimal(0)
Catch
outReturnCode = -1
outErrorMsg = " ERR4: Reading median salary"
Return
End Try
myReader.Close()
End Sub ' InOutParameters procedure
'***************************************************************
' Routine: OneResultSetToClient
'
' Purpose: Return a resultset to the caller that contains
' records of employees with salaries
' greater than the value of input parameter insalary.
'
' Shows how to:
' - define IN parameters in PARAMETER STYLE GENERAL
' - Return a result set to the client
' - Catch errors, and If any occur, set an output
' Return code and optionally set an error message.
'
' Parameters:
'
' IN: inSalary - salary to be used in SQL query
'
' OUT: outReturnCode - A Return code to indicate caught errors
' outErrorMsg - To hold an optional error message
'
'*****************************************************************/
Public Shared Sub OneResultSetToClient( ByVal inSalary As Decimal, _
ByRef outReturnCode As Integer, _
ByRef outErrorMsg As String)
Dim myCommand As DB2Command
Dim myReader As DB2DataReader
Dim parm As DB2Parameter
outReturnCode = 0
outErrorMsg = ""
Try
' Get a command object from the thread's context object
myCommand = DB2Context.GetCommand()
' Get a resultset
myCommand.CommandText = "SELECT name, job, salary" _
+ " FROM staff " _
+ " WHERE salary > ?" _
+ " ORDER BY salary"
parm = myCommand.Parameters.Add("@inSalary", DB2Type.Decimal, 7)
parm.Direction = ParameterDirection.Input
parm.Value = inSalary
myReader = myCommand.ExecuteReader()
Catch
outReturnCode = -1
outErrorMsg = "ERR1: Error on SELECT from staff"
Return
End Try
' The DB2DataReader contains the result of the query.
' This result set can be Returned with the procedure,
' by simply NOT closing the DB2DataReader.
' SpecIfically, do NOT execute myReader.Close()
End Sub ' OneResultSetToClient
'***************************************************************
' Routine: ClobExtract
'
' Purpose: Copies a portion of a resume in a CLOB data type
' into an output VARCHAR parameter. The portion
' of the resume pertains to Department Information.
'
' Shows how to:
' - define IN and OUT parameters in STYLE GENERAL
' - locate information within a formatted clob
' - extract information from within a clob and
' copy it to an output parameter
'
' IN: empNumber - employee number
' OUT: outDept - department number
' outReturnCode - A Return code to indicate caught errors
' outErrorMsg - To hold an optional error message
'
'*****************************************************************
Public Shared Sub ClobExtract( ByVal inEmpNo As String, _
ByRef outDeptInfo As String, _
ByRef outReturnCode As Integer, _
ByRef outErrorMsg As String)
Dim parm As DB2Parameter
Dim myCommand As DB2Command
Dim myReader As DB2DataReader
Dim depPos As Integer
Dim eduPos As Integer
Dim strClob As String
depPos = 0
eduPos = 0
outDeptInfo = ""
outReturnCode = 0
outErrorMsg = ""
Try
myCommand = DB2Context.GetCommand()
myCommand.CommandText = "SELECT resume" _
+ " FROM emp_resume" _
+ " WHERE empno = ?" _
+ " AND resume_format = 'ascii'"
parm = myCommand.Parameters.Add("@empNo", DB2Type.Char, 6)
parm.Direction = ParameterDirection.Input
parm.Value = inEmpNo
myReader = myCommand.ExecuteReader()
Catch
outReturnCode = -1
outErrorMsg = "ERR1: SELECT resume from emp_resume failed"
myReader.Close()
Return
End Try
If (myReader.HasRows = 0)
outDeptInfo = ("\nEmployee " + inEmpNo + " does not have a resume.")
myReader.Close()
Return
Else
Try
myReader.Read()
' Get the data as a string
strClob = myReader.GetString(0)
' Locate the position of Department related information
depPos = strClob.IndexOf("Department Information", 1)
eduPos = strClob.IndexOf("Education", depPos)
If (depPos = -1)
outDeptInfo = "Resume does not contain a Department Info section."
Else
' Get the information between Department Info and Education
outDeptInfo = strClob.Substring(depPos, eduPos - depPos)
End If
Catch
outReturnCode = -1
outErrorMsg = "ERR2: Accessing portions of resume"
myReader.Close()
Return
End Try
End If
End Sub ' ClobExtract procedure
'***************************************************************
' Routine: AllDataTypes
'
' Purpose: Take each parameter and set it to a new output value.
' If an error is caught, indicate this with an output
' parameter for the Return code and optionally also
' set an output parameter with an error message.
'
' Shows how to:
' - define INOUT/OUT parameters in PARAMETER STYLE GENERAL
' - assign output values to INOUT/OUT parameters
'
'
' INOUT: smallInOut (SMALLINT)
' intInOut (INTEGER)
' bigInOut (BIGINT)
' realInOut (REAL)
' inOutDouble (DOUBLE)
'
' OUT: charOut (CHAR(1))
' charsOut (CHAR(15))
' varcharOut (VARCHAR(12) )
' dateOut DATE
' timeOut TIME
' outReturnCode
' outErrorMsg
'
'*****************************************************************
Public Shared Sub AllDataTypes( _
ByRef inoutSmallint As Int16, _
ByRef inoutInteger As Int32, _
ByRef inoutBigint As Int64, _
ByRef inoutReal As Single, _
ByRef inoutDouble As Double, _
ByRef outChar As String, _
ByRef outChars As String, _
ByRef outVarchar As String, _
ByRef outDate As DateTime, _
ByRef outTime As TimeSpan, _
ByRef outReturnCode As Integer, _
ByRef outErrorMsg As String)
' Initialize output parameters
Dim myReader As DB2DataReader
Dim myCommand As DB2Command
outChar = ""
outChars = ""
outVarchar = ""
outDate = new DateTime(1, 1, 1)
outTime = new TimeSpan(1, 1, 1)
outReturnCode = 0
outErrorMsg = ""
' Use the input value of inputSmallint
' to determine its output value
If (inoutSmallint = 0)
inoutSmallint = 1
Else
inoutSmallint = inoutSmallint / 2
End If
' Use the input value of inoutInteger
' to set its output value
If (inoutInteger = 0)
inoutInteger = 1
Else
inoutInteger = inoutInteger / 2
End If
' Use the input value of inoutBigint
' to set its output value
If (inoutBigint = 0)
inoutBigint = 1
Else
inoutBigint = inoutBigint / 2
End If
' Use the input value of inoutReal
' to set its output value
If (inoutReal = 0)
inoutReal = 1
Else
inoutReal = inoutReal / 2
End If
' Use the input value of inoutDouble
' to set its output value
If (inoutDouble = 0)
inoutDouble = 1
Else
inoutDouble = inoutDouble / 2
End If
Try
' Query table employee
myCommand = DB2Context.GetCommand()
myCommand.CommandText = "SELECT midinit, firstnme, lastname" _
+ " FROM employee" _
+ " WHERE empno = '000180'"
myReader = myCommand.ExecuteReader()
If (myReader.HasRows)
myReader.Read()
outChar = myReader.GetString(0)
outChars = myReader.GetString(2)
outVarchar = myReader.GetString(1)
myReader.Close()
Else
outReturnCode = -1
outErrorMsg = "No record with empNo='000180'"
Return
End If
Catch
outReturnCode = -1
outErrorMsg = "ERR1: Accessing name fields"
myReader.Close()
Return
End Try
Try
' Set outDate to the current date (retrieved by a query)
myCommand.CommandText = "VALUES(CURRENT DATE)"
myReader = myCommand.ExecuteReader()
myReader.Read()
outDate = myReader.GetDate(0)
myReader.Close()
Catch
outReturnCode = -1
outErrorMsg = "ERR2: Getting current date failed"
myReader.Close()
Return
End Try
Try
' Set outTime to the current time (retrieved by a query)
myCommand.CommandText = "VALUES(CURRENT TIME)"
myReader = myCommand.ExecuteReader()
myReader.Read()
outTime = myReader.GetTimeSpan(0)
myReader.Close()
Catch
outReturnCode = -1
outErrorMsg = "ERR2: Getting current date failed"
myReader.Close()
Return
End Try
End Sub ' AllDataTypes procedure
End Class ' SpServer class