Retrieving output parameters from a Stored Procedure using Lotus Connector

Here is a sample building block for writing a LotusScript agent that utilizes the Lotus Connector to execute a procedure that returns output parameter(s), instead of a result set.

In this example, the stored procedure receives 1 input parameter and returns 2 output parameters. The simple SQL searches the name and timestamp value of an employee. The search is performed using Employee number as a search criteria.

Please note in the LotusScript agent that the 'fields' field list needs to have 3 input parameters (one is the employee number and the other are blank), and the 'results' field list only have 2 fields in there.

The environment is the iSeries running OS/400 V5R1 with Lotus Domino R5.0.9a.

When you run the agent, you will see the following:
Calling stored procedure ...
Stored Procedure called successfully
The retrieved name is: NAME1
The retrieved timestamp is: 2002-04-02.13.28.49.706788
Agent completed successfully

Code

The stored procedure: ---------------------

CREATE PROCEDURE MYLIB.MYSPROC ( IN INPARM int,
                                    OUT OUTPARM1 char(10),
                                    OUT OUTPARM2 char(26) )
    LANGUAGE SQL
    SPECIFIC MYLIB.MYSP

P1 : BEGIN 
	SELECT MYLIB.TSFILE.OUTFLD1 AS OUTFLD1 , MYLIB.TSFILE.OUTFLD2 AS MYTS 
INTO OUTPARM1 , OUTPARM2 
	FROM MYLIB.TSFILE 
	WHERE RECNO = INPARM ; 
END P1                      


The LotusScript Agent:
----------------------

Option Public
Option Explicit
Uselsx "*LSXLC"

Sub Initialize
	On Error Goto Handler
	
	Dim session As New NotesSession	
	Dim sesslc As New LCSession
	Dim src As New LCConnection ("db2")
	Dim fields As New LCFieldList 
	Dim results As New LCFieldList
	Dim fieldIn As LCField
	Dim fieldOut1, fieldOut2 As LCField
	Dim Count As Long
	
	sesslc.ClearStatus
	
	src.Database = "MYAS400"
	src.Userid = "USERID"
	src.Password = "PASSWORD"
	src.MapByName = True
	
	src.Procedure = "MYLIB.MYSPROC"
	src.connect
	
	Set fieldIn = fields.Append("inParm", LCTYPE_NUMERIC)
	fieldIn.value = 1
	
	Set fieldIn = fields.Append("OutParm1", LCTYPE_TEXT)
	Set fieldIn = fields.Append("OutParm2", LCTYPE_TEXT)
	
	Print "Calling the stored procedure..."
	If (src.Call(fields, 1, results)= 0) Then
		Print "Stored Procedure called successfully."
		Set fieldOut1 = results.GetField(1)
		Set fieldOut2 = results.GetField(2)
		Call src.Fetch(results)
		Print "The retrieved Name is: " & fieldOut1.text(0)
		Print "The retrieved timestamp is: " & fieldOut2.text(0)
	Else
		Print "There was an error running the Stored Procedure"
	End If
	
	src.Disconnect
	
Handler:
	If (sesslc.Status <> LCSUCCESS) Then
		Print "The following Lotus Connector error has occurred: "; sesslc.GetStatusText
	Elseif Error$ <> "" Then
		Print "The following LotusScript error has occurred. "; Error$
	Else
		Print "Agent completed successfully"
	End If
	End
End Sub