Examples of Visual Basic .NET CLR functions

The following examples show a variety of Visual Basic UDF implementations. The CREATE FUNCTION statement is provided for each UDF with the corresponding Visual Basic source code from which the associated assembly can be built. The Visual Basic source file that contains the functions declarations used in the following examples is named gwenVbUDF.cs and has the following format:

  using System;
  using System.IO;
  using IBM.Data.DB2;
 
  Namespace bizLogic
  
     ...
     ' Class definitions that contain UDF declarations
     ' and any supporting class definitions
     ...   

End Namespace

The function declarations must be contained in a class within a Visual Basic file. The use of namespaces is optional. If a namespace is used, the namespace must appear in the assembly path name provided in the EXTERNAL clause of the CREATE PROCEDURE statement. The IBM.Data.DB2. inclusion is required if the function contains SQL.

This example shows the following:

• CREATE FUNCTION statement for a parameter style SQL table function
• Visual Basic code for a parameter style SQL table function

On each call of the table function, one record is read from the array and one row is generated in the table that is returned by the function. The row is generated in the table, by setting the output parameters of the table function to the desired row values. After the final call of the table function occurs, the table of generated rows is returned.

  CREATE FUNCTION TableUDF(double)
  RETURNS TABLE (name varchar(20), 
                 job varchar(20), 
                 salary double)
  EXTERNAL NAME 'gwenVbUDF.dll:bizLogic.empOps!TableUDF'
  LANGUAGE CLR
  PARAMETER STYLE SQL
  NOT DETERMINISTIC
  FENCED
  SCRATCHPAD 10
  FINAL CALL
  DISALLOW PARALLEL
  NO DBINFO
  EXECUTION CONTROL SAFE
  

  Class Person
  ' The class Person is a supporting class for  
  ' the table function UDF, tableUDF, below.

    Private name As String 
    Private position As String
    Private salary As Int32

    Public Sub New(ByVal newName As String, _
                   ByVal newPosition As String, _
                   ByVal newSalary As Int32)

      name = newName
      position = newPosition
      salary = newSalary
    End Sub
    
    Public Property GetName() As String
      Get
        Return name
      End Get
  
      Set (ByVal value As String)
        name = value
      End Set
    End Property

    Public Property GetPosition() As String
      Get
        Return position
      End Get
    
      Set (ByVal value As String)
        position = value
      End Set
    End Property

    Public Property GetSalary() As Int32
      Get
        Return salary
      End Get
 
      Set (ByVal value As Int32)
        salary = value
      End Set
    End Property

  End Class

   Class empOps

     Public Shared Sub TableUDF(byVal factor as Double, _
                                byRef name As String, _
                                byRef position As String, _
                                byRef salary As Double, _
                                byVal factorNullInd As Int16, _
                                byRef nameNullInd As Int16, _
                                byRef positionNullInd As Int16, _
                                byRef salaryNullInd As Int16, _
                                byRef sqlState As String, _
                                byVal funcName As String, _
                                byVal specName As String, _
                                byRef sqlMessageText As String, _
                                byVal scratchPad As Byte(), _
                                byVal callType As Int32)

       Dim intRow As Int16

       intRow = 0

       ' Create an array of Person type information
       Dim staff(2) As Person
       staff(0) = New Person("Gwen", "Developer", 10000)
       staff(1) = New Person("Andrew", "Developer", 20000)
       staff(2) = New Person("Liu", "Team Leader", 30000) 

       ' Initialize output parameter values and NULL indicators
       salary = 0
       name = position = ""
       nameNullInd = positionNullInd = salaryNullInd = -1

       Select callType              
          Case -2   ' Case SQLUDF_TF_FIRST:
          Case -1   ' Case SQLUDF_TF_OPEN:
            intRow = 1
            scratchPad(0) = intRow  ' Write to scratchpad
          Case 0    ' Case SQLUDF_TF_FETCH:
            intRow = scratchPad(0)
            If intRow > staff.Length
               sqlState = "02000"  ' Return an error SQLSTATE
            Else
               ' Generate a row in the output table 
               ' based on the staff array data.
               name = staff(intRow).GetName()
               position = staff(intRow).GetPosition()
               salary = (staff(intRow).GetSalary()) * factor
               nameNullInd = 0
               positionNullInd = 0
               salaryNullInd = 0
            End If
            intRow = intRow + 1
            scratchPad(0) = intRow  ' Write scratchpad
            
          Case 1    ' Case SQLUDF_TF_CLOSE:
            
          Case 2    ' Case SQLUDF_TF_FINAL:
       End Select

     End Sub           
   
   End Class

This example shows the following:

• CREATE FUNCTION statement for a parameter style SQL scalar function
• Visual Basic code for a parameter style SQL scalar function

This scalar function can be easily invoked if for example we have a table defined as follows:

CREATE TABLE T (i1 INTEGER);
INSERT INTO T VALUES 12, 45, 16, 99;

A simple query such as the following can be used to invoke the scalar function:

SELECT my_count(i1) as count, i1 FROM T;

The output of such a query would be:

  COUNT           I1
  -----------     ----------
  1               12
  2               45
  3               16
  4               99 

This scalar UDF is quite simple. Instead of returning just the count of the rows, you could use a scalar function to format data in an existing column. For example you might append a string to each value in an address column or you might build up a complex string from a series of input strings or you might do a complex mathematical evaluation over a set of data where you must store an intermediate result.

  CREATE FUNCTION mycount(INTEGER) 
  RETURNS INTEGER
  LANGUAGE CLR
  PARAMETER STYLE SQL
  NO SQL
  SCRATCHPAD 10
  FINAL CALL
  FENCED
  EXECUTION CONTROL SAFE
  NOT DETERMINISTIC
  EXTERNAL NAME 'gwenUDF.dll:bizLogic.empOps!CountUp';

  Class empOps
    Public Shared Sub CountUp(byVal input As Int32, _
                              byRef outCounter As Int32, _
                              byVal nullIndInput As Int16, _
                              byRef nullIndOutCounter As Int16, _
                              byRef sqlState As String, _
                              byVal qualName As String, _
                              byVal specName As String, _
                              byRef sqlMessageText As String, _
                              byVal scratchPad As Byte(), _
                              byVal callType As Int32)

       Dim counter As Int32
       counter = 1

       Select callType
          case -1           ' case SQLUDF_TF_OPEN_CALL
             scratchPad(0) = counter
             outCounter = counter
             nullIndOutCounter = 0
          case 0              'case SQLUDF_TF_FETCH_CALL:
             counter = scratchPad(0)
             counter = counter + 1
             outCounter = counter
             nullIndOutCounter = 0
             scratchPad(0) = counter
          case 1             'case SQLUDF_CLOSE_CALL:
             counter = scratchPad(0)
             outCounter = counter
             nullIndOutCounter = 0
          case Else          ' Should never enter here
             ' These cases won't occur for the following reasons:
             ' Case -2  (SQLUDF_TF_FIRST)     —>No FINAL CALL in CREATE stmt
             ' Case 2   (SQLUDF_TF_FINAL)     —>No FINAL CALL in CREATE stmt
             ' Case 255 (SQLUDF_TF_FINAL_CRA) —>No SQL used in the function
             '
             ' * Note!*
             ' ---------
             ' The Else is required so that at compile time
             ' out parameter outCounter is always set *
             outCounter = 0
             nullIndOutCounter = -1
       End Select  
    End Sub        

  End Class