Example: Call an IBM i stored procedure by using Visual Basic

The Visual Basic programming examples listed below show an IBM® i procedure call being prepared.

Two statements are shown:

  1. A statement for the creation of the procedure
  2. A statement to prepare the call

Create the procedure only once. The definition that it provides is available to ODBC applications, and any other application that can run SQL statements.

Because of the way Visual Basic stores and manages the String data type, using an array of Byte data type instead of a String variable is recommended for the following parameter types:

  • Input/output parameters
  • Output parameters
  • Any parameter that contains binary data (rather then standard ANSI characters)
  • Any input parameter that has a variable address which is set once, but referred to many times

The last case would be true for the if the application made multiple calls to SQLExecute, while modifying Parm1 between each call. The following Visual Basic functions assist in converting strings and arrays of byte:

Public Sub Byte2String(InByte() As Byte, OutString As String)
  'Convert array of byte to string   
   OutString = StrConv(InByte(), vbUnicode)
End Sub
 
Public Function String2Byte(InString As String, OutByte() As Byte) As Boolean
    'vb byte-array / string coercion assumes Unicode string
    'so must convert String to Byte one character at a time
    'or by direct memory access
    'This function assumes Lower Bound of array is 0
   
    Dim I As Integer
    Dim SizeOutByte As Integer
    Dim SizeInString As Integer
   
    SizeOutByte = UBound(OutByte) + 1
    SizeInString = Len(InString)
 
    'Verify sizes if desired   
 
    'Convert the string
    For I = 0 To SizeInString - 1
      OutByte(I) = AscB(Mid(InString, I + 1, 1))
    Next I
    'If size byte array > len of string pad with Nulls for szString
    If SizeOutByte > SizeInString Then           'Pad with Nulls
       For I = SizeInString To UBound(OutByte)
          OutByte(I) = 0
       Next I
    End If
 
   String2Byte = True
End Function
 
Public Sub ViewByteArray(Data() As Byte, Title As String)
   'Display message box showing hex values of byte array
   
   Dim S As String
   Dim I As Integer
   On Error GoTo VBANext
   
   S = "Length: " & Str(UBound(Data) - LBound(Data) + 1) & "  Data (in hex):"
   For I = LBound(Data) To UBound(Data) 
      If (I Mod 8) = 0 Then
         S = S & "  "         'add extra space every 8th byte
      End If
      S = S & Hex(Data(I)) & " "
   VBANext:
   Next I
   MsgBox S, , Title
   
End Sub