The following example of Visual Basic source code shows how to call an iSeries™ server stored procedure and then retrieve the returned values into Visual Basic variables.
Visual Basic is able to call external functions that are found in a DLL. Since all ODBC drivers are DLLs, Visual Basic can be used to code directly to the ODBC APIs. By coding directly to the ODBC APIs a Visual Basic application can call an iSeries server stored procedure and return result values. See Code directly to ODBC APIs for more information.
'***********************************************************************
'* *
'* Because of the way Visual Basic stores and manages the String data *
'* type, it is recommended that you use an array of Byte data type *
'* instead of a String variable on the SQLBindParameter API. *
'* *
'***********************************************************************
Dim sTemp As String
Custnum As Integer
Dim abCustname(34) As Byte
Dim abAddress(34) As Byte
Dim abCity(24) As Byte
Dim abState(1) As Byte
Dim abPhone(14) As Byte
Dim abStatus As Byte
Dim RC As Integer
Dim nullx As Long 'Used to pass null pointer, not pointer to null
Dim lpSQL_NTS As Long 'Used to pass far pointer to SQL_NTS
Static link(7) As Long 'Used as an array of long pointers to the size
'each parameter which will be bound
'***********************************************************************
'* *
'* Initialize the variables needed on the API calls *
'* *
'***********************************************************************
link(1) = 6
link(2) = Ubound(abCustname) +1
link(3) = Ubound(abAddress) +1
link(4) = Ubound(abCity) +1
link(5) = Ubound(abState) +1
link(6) = Ubound(abPhone) +1
link(7) = 1
RC = 0
nullx = 0
lpSQL_NTS = SQL_NTS ' -3 means passed as sz string
'***********************************************************************
'* *
'* Create the procedure on the iSeries. This will define the *
'* procedure's name, parameters, and how each parameter is passed. *
'* Note: This information is stored in the server catalog tables and *
'* and only needs to be executed one time for the life of the stored *
'* procedure. It normally would not be run in the client application. *
'* *
'***********************************************************************
sTemp = "Create Procedure Storedp2 (:Custnum in integer, "
sTemp = sTemp & ":Custname out char(35), :Address out char(35),"
sTemp = sTemp & ":City out char(25), :State out char(2),"
sTemp = sTemp & ":Phone out char(15), :Status out char(1))
sTemp = sTemp & "(External name rastest.storedp2 language cobol General)"
RC = SQLExecDirect(Connection.hstmt, sTemp, Len(sTemp))
'Ignore error assuming that any error would be from procedure already
'created.
'***********************************************************************
'* *
'* Prepare the call of the procedure to the iSeries. *
'* For best performance, prepare the statement only one time and
'* execute many times.
'* *
'***********************************************************************
sTemp = "Call storedp2(?, ?, ?, ?, ?, ?, ?)"
RC = SQLPrepare(Connection.hstmt, sTemp, Len(sTemp))
If (RC <> SQL_SUCCESS) Then
DescribeError Connection.hdbc, Connection.hstmt
frmMain.Status.Caption = "Error on SQL_Prepare " & RTrim$(Tag)
End If
'***********************************************************************
'* *
'* Bind all of the columns passed to the stored procedure. This will *
'* set up the variable's data type, input/output characteristics, *
'* length, and initial value. *
'* The SQLDescribeParam API can optionally be used to retrieve the
'* parameter types.
'* *
'* To properly pass an array of byte to a stored procedure and receive *
'* an output value back, you must pass the first byte ByRef. *
'* *
'***********************************************************************
RC = SQLBindParameter(Connection.hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT, _
SQL_NUMERIC, 6, 0, Custnum, 6, link(1))
RC = SQLBindParameter(Connection.hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
SQL_CHAR, 35, 0, abCustname(0), UBound(abCustname)+1, link(2))
RC = SQLBindParameter(Connection.hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
SQL_CHAR, 35, 0, abAddress(0), UBound(abAddress)+1, link(3))
RC = SQLBindParameter(Connection.hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
SQL_CHAR, 25, 0, abCity(0), UBound(abCity)+1, link(4))
RC = SQLBindParameter(Connection.hstmt, 5, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
SQL_CHAR, 2, 0, abState(0), UBound(abState)+1, link(5))
RC = SQLBindParameter(Connection.hstmt, 6, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
SQL_CHAR, 15, 0, abPhone(0), UBound(abPhone)+1, link(6))
RC = SQLBindParameter(Connection.hstmt, 7, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
SQL_CHAR, 1, 0, abStatus, 1, link(7))
'***********************************************************************
'* *
'* The Prepare and Bind only needs to be execute once. The Stored
'* procedure can now be called multiple times by just changing the data
'* *
'***********************************************************************
Do While
'***********************************************************************
'* Read in a customer number *
'* *
'***********************************************************************
Custnum = Val(input.text)
'***********************************************************************
'* *
'* Execute the call of the procedure to the iSeries. *
'* *
'***********************************************************************
RC = SQLExecute(Connection.hstmt)
frmMain.Status.Caption = "Ran Stored Proc" & RTrim$(Tag)
If (RC <> SQL_SUCCESS) Then
DescribeError Connection.hdbc, Connection.hstmt
frmMain.Status.Caption = "Error on Stored Proc Execute " & RTrim$(Tag
End If
'***********************************************************************
'* *
'* Set text labels to display the output data *
'* You must convert the array of Byte back to a String
'* *
'***********************************************************************
lblCustname = StrConv(abCustname(), vbUnicode)
lblAddress = StrConv(abAddress(), vbUnicode)
lblCity = StrConv(abCity(), vbUnicode)
lblState = StrConv(abState(), vbUnicode)
lblPhone = StrConv(abPhone(), vbUnicode)
lblStatus = StrConv(abStatus(), vbUnicode)
Loop