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:
- A statement for the creation of the procedure
- 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