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
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