Examples of Visual Basic .NET CLR procedures

The Visual Basic source file that contains the procedure implementations of the following examples is named gwenVbProc.vb and has the following format:

  using System;
  using System.IO;
  using IBM.Data.DB2;
 
  Namespace bizLogic
  
     Class empOps
                ... 
       ' Visual Basic procedures 
                ...
     End Class
  End Namespace

This example shows the following:

• CREATE PROCEDURE statement for a parameter style GENERAL procedure
• Visual Basic code for a parameter style GENERAL procedure

This procedure takes an employee ID and a current bonus amount as input. It retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus is calculated, based on the employee salary, and returned along with the employee's full name. If the employee is not found, an empty string is returned.

  CREATE PROCEDURE SetEmpBonusGEN(IN empId CHAR(6),
                                  INOUT bonus Decimal(9,2),
                                  OUT empName VARCHAR(60))
  SPECIFIC setEmpBonusGEN
  LANGUAGE CLR
  PARAMETER STYLE GENERAL
  DYNAMIC RESULT SETS 0
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!SetEmpBonusGEN'

    Public Shared Sub SetEmpBonusGEN(ByVal empId As String, _
                                     ByRef bonus As Decimal, _
                                     ByRef empName As String)

       Dim salary As Decimal
       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader

       salary = 0

       myCommand = DB2Context.GetCommand()
       myCommand.CommandText = _
                  "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                + "FROM EMPLOYEE " _
                + "WHERE EMPNO = '" + empId + "'"
       myReader = myCommand.ExecuteReader()

       If myReader.Read()  ' If employee record is found
          ' Get the employee's full name and salary
          empName = myReader.GetString(0) + " " _
                  + myReader.GetString(1) + ". " _
                  + myReader.GetString(2)

          salary = myReader.GetDecimal(3)

          If bonus = 0
             If salary > 75000
                bonus = salary * 0.025
             Else
                bonus = salary * 0.05
             End If
          End If
       Else  ' Employee not found
          empName = ""  ' Set output parameter
       End If

       myReader.Close()

    End Sub

This example shows the following:

• CREATE PROCEDURE statement for a parameter style GENERAL WITH NULLS procedure
• Visual Basic code for a parameter style GENERAL WITH NULLS procedure

This procedure takes an employee ID and a current bonus amount as input. If the input parameter is not null, it retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus based on salary is calculated and returned along with the employee's full name. If the employee data is not found, a NULL string and integer is returned.

  CREATE PROCEDURE SetEmpBonusGENNULL(IN empId CHAR(6),
                                      INOUT bonus Decimal(9,2),
                                      OUT empName VARCHAR(60))
  SPECIFIC SetEmpBonusGENNULL
  LANGUAGE CLR
  PARAMETER STYLE GENERAL WITH NULLS
  DYNAMIC RESULT SETS 0
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!SetEmpBonusGENNULL'

    Public Shared Sub SetEmpBonusGENNULL(ByVal empId As String, _
                                         ByRef bonus As Decimal, _
                                         ByRef empName As String, _
                                         byVal nullInds As Int16())

       Dim salary As Decimal
       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader

       salary = 0

       If nullInds(0) = -1   ' Check if the input is null
          nullInds(1) = -1   ' Return a NULL bonus value
          empName = ""       ' Set output parameter
          nullInds(2) = -1   ' Return a NULL empName value
          Return
       Else
          myCommand = DB2Context.GetCommand()
          myCommand.CommandText = _
                     "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                   + "FROM EMPLOYEE " _
                   + "WHERE EMPNO = '" + empId + "'"

          myReader = myCommand.ExecuteReader()

          If myReader.Read()  ' If employee record is found
             ' Get the employee's full name and salary
             empName = myReader.GetString(0) + " "  _
                     + myReader.GetString(1) + ". " _
                     + myReader.GetString(2)

             salary = myReader.GetDecimal(3)

             If bonus = 0
                If salary > 75000
                   bonus = Salary * 0.025
                   nullInds(1) = 0 'Return a non-NULL value
                Else
                   bonus = salary * 0.05
                   nullInds(1) = 0 ' Return a non-NULL value
                End If
             Else  'Employee not found
                empName = ""       ' Set output parameter
                nullInds(2) = -1   ' Return a NULL value
             End If
          End If

          myReader.Close()

       End If

    End Sub

This example shows the following:

• CREATE PROCEDURE statement for a parameter style SQL procedure
• Visual Basic code for a parameter style SQL procedure

This procedure takes an employee ID and a current bonus amount as input. It retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus based on salary is calculated and returned along with the employee's full name. If the employee is not found, an empty string is returned.

  CREATE PROCEDURE SetEmpBonusSQL(IN empId CHAR(6),
                                  INOUT bonus Decimal(9,2),
                                  OUT empName VARCHAR(60))
  SPECIFIC SetEmpBonusSQL
  LANGUAGE CLR
  PARAMETER STYLE SQL
  DYNAMIC RESULT SETS 0
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!SetEmpBonusSQL'

    Public Shared Sub SetEmpBonusSQL(byVal empId As String, _
                                     byRef bonus As Decimal, _
                                     byRef empName As String, _
                                     byVal empIdNullInd As Int16, _
                                     byRef bonusNullInd As Int16, _
                                     byRef empNameNullInd As Int16, _
                                     byRef sqlState As String, _
                                     byVal funcName As String, _
                                     byVal specName As String, _
                                     byRef sqlMessageText As String)

       ' Declare local host variables
       Dim salary As Decimal
       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader

       salary = 0

       If empIdNullInd = -1   ' Check if the input is null
          bonusNullInd = -1   ' Return a NULL Bonus value
          empName = ""
          empNameNullInd = -1 ' Return a NULL empName value
       Else
         myCommand = DB2Context.GetCommand()
         myCommand.CommandText = _
                    "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                  + "FROM EMPLOYEE " _
                  + " WHERE EMPNO = '" + empId + "'"

          myReader = myCommand.ExecuteReader()

          If myReader.Read()  ' If employee record is found
             '  Get the employee's full name and salary
             empName = myReader.GetString(0) + " " 
                     + myReader.GetString(1) _
                     + ". " +  myReader.GetString(2)
             empNameNullInd = 0
             salary = myReader.GetDecimal(3)

             If bonus = 0
                If salary > 75000
                   bonus = salary * 0.025
                   bonusNullInd = 0  ' Return a non-NULL value
                Else
                   bonus = salary * 0.05
                   bonusNullInd = 0  ' Return a non-NULL value
                End If
             End If
          Else  ' Employee not found
             empName = ""            ' Set output parameter
             empNameNullInd = -1     ' Return a NULL value
          End If
          
          myReader.Close()
       End If
       
    End Sub

This example shows the following:

• CREATE PROCEDURE statement for an external Visual Basic procedure returning a result set
• Visual Basic code for a parameter style GENERAL procedure that returns a result set

This procedure accepts the name of a table as a parameter. It returns a result set containing all the rows of the table specified by the input parameter. This is done by leaving a DB2DataReader for a given query result set open when the procedure returns. Specifically, if reader.Close() is not executed, the result set will be returned.

  CREATE PROCEDURE ReturnResultSet(IN tableName VARCHAR(20))
  SPECIFIC ReturnResultSet
  DYNAMIC RESULT SETS 1
  LANGUAGE CLR
  PARAMETER STYLE GENERAL
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!ReturnResultSet'

Public Shared Sub ReturnResultSet(byVal tableName As String)

      Dim myCommand As DB2Command
      Dim myReader As DB2DataReader

      myCommand = DB2Context.GetCommand()

      ' Set the SQL statement to be executed and execute it.
      myCommand.CommandText = "SELECT * FROM " + tableName
      myReader = myCommand.ExecuteReader()

      ' 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 reader.Close()

    End Sub

This example shows the following:

• CREATE PROCEDURE statement for a procedure accessing the dbinfo structure
• Visual Basic code for a parameter style SQL procedure that accesses the dbinfo structure

To access the dbinfo structure, the DBINFO clause must be specified in the CREATE PROCEDURE statement. No parameter is required for the dbinfo structure in the CREATE PROCEDURE statement however a parameter must be created for it, in the external routine code. This procedure returns only the value of the current database name from the dbname field in the dbinfo structure.

  CREATE PROCEDURE ReturnDbName(OUT dbName VARCHAR(20))
  SPECIFIC ReturnDbName
  LANGUAGE CLR
  PARAMETER STYLE SQL
  DBINFO
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!ReturnDbName'

    Public Shared Sub ReturnDbName(byRef dbName As String, _
                                   byRef dbNameNullInd As Int16, _
                                   byRef sqlState As String, _
                                   byVal funcName As String, _
                                   byVal specName As String, _
                                   byRef sqlMessageText As String, _
                                   byVal dbinfo As sqludf_dbinfo)

      ' Retrieve the current database name from the
      ' dbinfo structure and return it.
      dbName = dbinfo.dbname
      dbNameNullInd = 0  ' Return a non-null value

      ' If you want to return a user-defined error in
      ' the SQLCA you can specify a 5 digit user-defined
      ' SQLSTATE and an error message string text.
      ' For example:
      '
      ' sqlState = "ABCDE"
      ' msg_token = "A user-defined error has occurred"
      '
      ' These will be returned by database managher in the SQLCA.
			' It will appear in the format of a regular sqlState
      ' error.
    End Sub

This example shows the following:

• CREATE PROCEDURE statement for a procedure using a main program style
• Visual Basic parameter style GENERAL WITH NULLS code in using a MAIN program style

To implement a routine in a main program style, the PROGRAM TYPE clause must be specified in the CREATE PROCEDURE statement with the value MAIN. Parameters are specified in the CREATE PROCEDURE statement however in the code implementation, parameters are passed into the routine in an argc integer parameter and an argv array of parameters.

  CREATE PROCEDURE MainStyle(IN empId CHAR(6),
                             INOUT bonus Decimal(9,2),
                             OUT empName VARCHAR(60))
  SPECIFIC mainStyle
  DYNAMIC RESULT SETS 0
  LANGUAGE CLR
  PARAMETER STYLE GENERAL WITH NULLS
  FENCED
  PROGRAM TYPE MAIN
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!Main' 

    Public Shared Sub Main( byVal argc As Int32, _
                            byVal argv As Object())

       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader
       Dim empId As String
       Dim bonus As Decimal
       Dim salary As Decimal
       Dim nullInds As Int16()

       empId = argv(0)  ' argv[0] (IN)    nullInd = argv[3]
       bonus = argv(1)  ' argv[1] (INOUT) nullInd = argv[4]
                        ' argv[2] (OUT)   nullInd = argv[5]
       salary = 0
       nullInds = argv(3)

       If nullInds(0) = -1     ' Check if the empId input is null
          nullInds(1) = -1     ' Return a NULL Bonus value
          argv(1) = ""         ' Set output parameter empName
          nullInds(2) = -1     ' Return a NULL empName value
          Return
       Else
          ' If the employee exists and the current bonus is 0,
          ' calculate a new employee bonus based on the employee's
          ' salary.  Return the employee name and the new bonus
          myCommand = DB2Context.GetCommand()
          myCommand.CommandText = _
                     "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                   + " FROM EMPLOYEE " _
                   + " WHERE EMPNO = '" + empId + "'"

          myReader = myCommand.ExecuteReader()


          If myReader.Read()  ' If employee record is found
             ' Get the employee's full name and salary
             argv(2) = myReader.GetString(0) + " "  _
                     + myReader.GetString(1) + ". " _
                     + myReader.GetString(2)
             nullInds(2) = 0
             salary = myReader.GetDecimal(3)

             If bonus = 0
                If salary > 75000
                   argv(1) = salary * 0.025
                   nullInds(1) = 0  ' Return a non-NULL value
                Else
                   argv(1) = Salary * 0.05
                   nullInds(1) = 0  ' Return a non-NULL value
                End If
             End If
          Else  ' Employee not found
             argv(2) = ""      ' Set output parameter
             nullInds(2) = -1  ' Return a NULL value
          End If

          myReader.Close()
       End If

    End Sub