'****************************************************************************
' (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: UDFcli.vb
'
' SAMPLE: Client application that calls the UDFs in UDFsrv.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, UDFsrv.vb. This
' erases any existing assembly of the same name and copies
' the newly compiled assembly UDFsrv.dll from the
' current directory to the $(DB2PATH)\function directory).
' This also runs a batchfile UDFCat that executes SQL to
' drop (if already existing) and create the UDFs
' in DB2 that have their UDF-body implemented by the
' methods in this file, UDFsrv.vb:
'
' nmake/make UDFsrv
'
' To run UDFcli successfully you must have succesfully
' completed step 1.
'
' 2. Compile the client source file UDFcli.vb.
'
' nmake/make UDFcli
'
' 3. Run the client UDFcli:
'
' UDFcli
'
'
' II) If you don't have a compatible nmake program on your
' system do the following:
'
' 1. Compile this file, UDFsrv.vb, containing the Visual Basic
' (VB) UDF methods that implement the UDF-bodies, with
' bldrn.bat. This batch file first erases any
' existing assembly of the same name. Then it
' compiles UDFsrv.vb and copies the assembly file
' UDFsrv.dll from the current directory to the DB2 function
' directory: $(DB2PATH)\function.
'
' bldrtn UDFsrv
'
'
' 2. Create the UDFs in DB2 by entering the following
' at the command prompt:
'
' UDFCat
'
' This will drop the UDFs from DB2, if they exist, and
' then executes SQL CREATE FUNCTION statements to create the
' UDFs in DB2. These statements associate each function
' name with an external VB method in the assembly UDFsrv.dll
' that is in the DB2 function directory.
'
' 3. Compile the UDFcli.vb file with the bldapp.bat batch file by
' entering the following at the command prompt:
'
' bldapp UDFcli
'
' 4. Run the UDFcli program by entering the program name at
' the command prompt:
'
' UDFcli
'
' UDFcli calls several methods that call the UDFs:
'
' Class UDFsrv contains the following methods:
'
' 1. ScalarUDF Updates employee salary based on job type
' 2. ScratchpadScalarUDF Increments a count each time UDF is called
' 3. ScalarUDFReturningError Shows how to return an error from a UDF
' 4. TableUDF Given a salary update multiplier returns a
' table-reference with employees' names, jobs,
' and updated salaries
'
'****************************************************************************
'
' SQL Statements USED:
' SELECT
' UPDATE
'
'
'****************************************************************************
'
' 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.
'
'****************************************************************************/
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class UDFClient
Public Shared Sub Main(args() As String)
' Declare a DB2Connection and a DB2Transaction
Dim conn As DB2Connection
Dim trans As DB2Transaction
Try
Console.WriteLine( _
vbNewLine & _
" THIS SAMPLE SHOWS HOW TO CALL THE UDFs" & _
" IN UDFsrv.vb")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
conn = ConnectDb(args)
' Invoke an external scalar UDF: ScalarUDF
trans=conn.BeginTransaction()
InvokeExternalScalarUDF(conn, trans)
' Invoke a scalar UDF with a scratchpad: ScratchpadScUDF
trans=conn.BeginTransaction()
InvokeScratchpadScUDF(conn, trans)
' Invoke a scalar UDF that returns an error: ScUDFReturningErr
trans=conn.BeginTransaction()
InvokeScUDFReturningErr(conn, trans)
' Invoke a table UDF with a scratchpad: TableUDF
trans=conn.BeginTransaction()
InvokeTableUDF(conn, trans)
' Disconnect from the database
Console.WriteLine( _
vbNewLine & " Disconnect from the database")
conn.Close()
Catch e As Exception
Console.WriteLine( _
vbNewLine & "Error caught in client of UDFsrv application.")
Console.WriteLine(e.ToString())
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
' 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 < 10) Then
Console.Write(str.PadRight(10, Convert.ToChar(" ") ) & " ")
Else
Console.Write(str.Substring(0, 10) & " ")
End If
Next k
Console.WriteLine()
Loop
End Sub ' DisplayResultSet
Public Shared Sub InvokeExternalScalarUDF(conn As DB2Connection, _
trans As DB2Transaction)
Dim reader As DB2DataReader
Dim cmd As DB2Command
Try
Console.WriteLine("")
Console.WriteLine(" Invoke the scalar UDF 'ScalarUDF' in a SELECT" & _
" statement:")
Console.WriteLine()
Console.WriteLine(" SELECT name, job, salary, ")
Console.WriteLine(" ScalarUDF(cast(job as char(5)), salary)")
Console.WriteLine(" AS calculated_salary")
Console.WriteLine(" FROM staff")
Console.WriteLine(" WHERE name LIKE 'S%'")
Console.WriteLine("")
' Create a DB2Command to execute a SELECT statement with a UDF reference
cmd = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "SELECT name, job, salary, " & _
" ScalarUDF(cast(job as char(5)), salary) " & _
" AS calculated_salary " & _
" FROM staff " & _
" WHERE name LIKE 'S%'"
reader = cmd.ExecuteReader()
Console.WriteLine(" NAME JOB SALARY CALCULATED_SALARY")
Console.WriteLine(" ---------- ---------- ---------- -----------------")
' Display the result set
DisplayResultSet(reader)
reader.Close()
' Rollback the transactions to preserve the SAMPLE
' database's original state
trans.Rollback()
Catch e As exception
reader.Close()
trans.Rollback()
Console.WriteLine(e.ToString())
End Try
End Sub ' InvokeScalarUDF
' This method invokes external scratchpad scalar UDF, ScratchpadScUDF, in
' a SELECT query. The scratchpad scalar UDF generates a row value for each
' row that is returned by the query - a quick way to display a numbered
' list to accompany row values without having to alter the table.
public Shared Sub InvokeScratchpadScUDF(conn As DB2Connection, _
trans As DB2Transaction)
Dim reader As DB2DataReader
Dim cmd As DB2Command
Try
Console.WriteLine()
Console.WriteLine(" Invoke the scratchpad scalar UDF " & _
"'ScratchpadScUDF' in a SELECT statement:")
Console.WriteLine()
Console.WriteLine(" SELECT ScratchpadScUDF() AS row_id, name, job")
Console.WriteLine(" FROM staff")
Console.WriteLine(" WHERE name LIKE 'S%'")
Console.WriteLine()
' Create a DB2Command to execute a SELECT statement with a UDF reference
cmd = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "SELECT ScratchpadScUDF() AS row_id, name, job " & _
" FROM staff " & _
" WHERE name LIKE 'S%'"
reader = cmd.ExecuteReader()
Console.WriteLine(" ROW_ID NAME JOB")
Console.WriteLine(" ---------- ---------- -------")
' Display the result set
DisplayResultSet(reader)
reader.Close()
' Rollback the transactions to preserve the SAMPLE
' database's original state
trans.Rollback()
Catch e As Exception
reader.Close()
trans.Rollback()
Console.WriteLine(e.ToString())
End Try
End Sub ' InvokeScratchpadScUDF
' This method invokes a scratchpad scalar UDF in a SELECT query to show how
' how an error is returned from a UDF. The UDF performs a simple division.
' If the input divisor is 0, an error is returned.
public Shared Sub InvokeScUDFReturningErr(conn As DB2Connection, _
trans As DB2Transaction)
Dim reader As DB2DataReader
Dim cmd As DB2Command
Try
Console.WriteLine()
Console.WriteLine(" Invoke the scalar UDF 'ScUDFReturningErr'," & _
" which returns an error, in a SELECT statement:")
Console.WriteLine()
Console.WriteLine(" SELECT name, job, ScUDFReturningErr(salary, 0.0)")
Console.WriteLine(" AS calculated_salary")
Console.WriteLine(" FROM staff")
Console.WriteLine(" WHERE name LIKE 'S%' ")
Console.WriteLine()
' Create a DB2Command to execute a SELECT statement with a UDF reference
cmd = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "SELECT ScUDFReturningErr(cast(salary as double), 0.00) " & _
" AS divided_salary " & _
" FROM staff " & _
" WHERE name LIKE 'S%'"
reader = cmd.ExecuteReader()
' Display the result set
DisplayResultSet(reader)
reader.Close()
' Rollback the transactions to preserve the SAMPLE
' database's original state
trans.Rollback()
Catch e As Exception
reader.Close()
trans.Rollback()
Console.WriteLine(e.ToString())
End Try
End Sub ' InvokeScUDFReturningErr
' This method updates salaries in the EMPLOYEE table according to 3 values
public Shared Sub InvokeTableUDF(conn As DB2Connection, _
trans As DB2Transaction)
Dim reader As DB2DataReader
Dim cmd As DB2Command
Try
Console.WriteLine()
Console.WriteLine(" Invoke the tableUDF 'TableUDF'," & _
" in a SELECT statement:")
Console.WriteLine()
Console.WriteLine(" SELECT udfTable.name, udfTable.job,")
Console.WriteLine(" udfTable.salary")
Console.WriteLine(" FROM TABLE(TableUDF(1.5))")
Console.WriteLine(" AS udfTable")
Console.WriteLine()
' Create a DB2Command to execute a SELECT statement with
' a UDF table-reference (note the use of the keyword TABLE)
cmd = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "SELECT udfTable.name, udfTable.job, udfTable.salary" & _
" FROM TABLE(TableUDF(1.5))" & _
" AS udfTable"
reader = cmd.ExecuteReader()
Console.WriteLine(" NAME JOB SALARY")
Console.WriteLine(" ---------- ---------- -------")
' Display the result set
DisplayResultSet(reader)
reader.Close()
' Rollback the transactions to preserve the SAMPLE
' database's original state
trans.Rollback()
Catch e As Exception
reader.Close()
trans.Rollback()
Console.WriteLine(e.ToString())
End Try
End Sub ' InvokeTableUDF
End Class