Manage Learn to apply best practices and optimize your operations.

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

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

Dig Deeper on Lotus Notes Domino Administration Tools

Join the conversation

2 comments

Send me notifications when other members comment.

Please create a username to comment.

Dont work
Cancel
The following Lotus Connector error has occurred: Error: Cannot locate list element

I get this error at " Set fieldOut1 = results.Getfield(1)"

I think i've got no result, but this is'nt possible
Cancel

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchContentManagement

Close