Can I call a Stored Procedure on IBMi via SELECT_SQL and share a result set ?

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
PatrickF
Posts: 10
Joined: Thu Nov 26, 2015 1:31 pm

Can I call a Stored Procedure on IBMi via SELECT_SQL and share a result set ?

Post by PatrickF » Thu Nov 16, 2017 6:23 am

I have a requirement to call an existing stored procedure on IBMi from LANSA (not using L/I SQL Service) . The documentation at http://docs.lansa.com/14/en/lansa015/co ... FREE_E_eg5 shows an example of SELECT_SQL calling a stored procedure that returns a result set.
I have written a server module and web page that uses SELECT_SQL USING(#SQLQUERY) where #SQLQUERY has the 'CALL <storedprocedurename>' .
The stored procedure defines a cursor that returns a result set. It's crashing and the LWEB_JOB indicates a cursor issue. My questions are :
1) Can SELECT_SQL call an IBMi SQL syntax stored procedure, or is there another recommended way
2) If it can. how is the result set shared between the stored procedure and the SELECT_SQL

My RDMLX in the Server Module Looks like this :
Def_List Name(#xTitleList) Fields(#xEmployeeIdentification #xEmployeeTitle #xEmployeeSurname #xEmployeeGivenNames) Counter(#ListCount) Type(*Working) Entrys(*Max)
..
Srvroutine Name(GetTitle)
Field_Map For(*Input) Field(#STD_TEXT) Parameter_Name(Title)
List_Map For(*Output) List(#xTitleList) Parameter_Name(List)
#SQLQRY := 'CALL GETTITL'
* + '(' + #QUOTE + #std_text.Trim + #QUOTE + ')'

* Get all records from the file using some or all of the key
Select_Sql Fields(#xEmployeeIdentification #xEmployeeTitle #xEmployeeSurname #xEmployeeGivenNames) From_Files((xEmployee)) Using(#SQLQRY)

Add_Entry To_List(#xTitleList)
Endselect
Endroutine

The stored Procedure source code is :
-- Generate SQL
-- Version: V7R1M0 100423
-- Generated on: 11/14/17 21:21:55
-- Relational Database: E0069B3P
-- Standards Option: DB2 for i
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","PATRICKF" ;

CREATE PROCEDURE ODCDEMOLIB.GETTITL2 (
IN IN_TITLE CHAR(40) DEFAULT 'Mr' )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC ODCDEMOLIB.GETTITL2
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
CLOSQLCSR = *ENDACTGRP,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = ODCDEMOLIB ,
DYNDFTCOL = *YES ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
P1 : BEGIN
DECLARE C1 CURSOR WITH RETURN TO CALLER FOR
SELECT XEMPLOYID , XEMPTITLE , XSURNAME , XGIVENAME
FROM ODCDEMOLIB . XEMPLOYEE WHERE XEMPTITLE = IN_TITLE ;
OPEN C1 ;
END P1 ;

COMMENT ON PARAMETER SPECIFIC PROCEDURE ODCDEMOLIB.GETTITL2
( IN_TITLE IS 'Title' ) ;

GRANT ALTER , EXECUTE
ON SPECIFIC PROCEDURE ODCDEMOLIB.GETTITL2
TO PATRICKF ;

GRANT EXECUTE
ON SPECIFIC PROCEDURE ODCDEMOLIB.GETTITL2
TO PUBLIC ;

Attached is the Joblog and a screen shot showing a successful test in navigator Run SQL Scripts
Attachments
STOREDPROC.PDF
(30.84 KiB) Downloaded 106 times
Stored Procedure Output.png
Stored Procedure Output.png (49.6 KiB) Viewed 1018 times

BrendanB
Posts: 35
Joined: Tue Nov 24, 2015 10:29 am

Re: Can I call a Stored Procedure on IBMi via SELECT_SQL and share a result set ?

Post by BrendanB » Thu Nov 16, 2017 9:34 am

Patrick,

just a suggestion,

try changing:

#SQLQRY := 'CALL GETTITL'

to

#SQLQRY := 'CALL ODCDEMOLIB.GETTITL'

also, test that this works using STRSQL from the command line.

in general, SQL wont use the library list, so you need to be explicit.

When I have used Stored Procedures on the IBMi, I always test from STRSQL on the command line (green-screen), since if it wont work there, it wont work.
I discovered that if STRSQL is not available, it is likely that stored procedures wont work either.

BrendanB.

eddie9394
Posts: 22
Joined: Tue Aug 14, 2018 11:03 pm

Re: Can I call a Stored Procedure on IBMi via SELECT_SQL and share a result set ?

Post by eddie9394 » Tue Aug 13, 2019 12:55 am

Any luck on getting the stored procedure call to work? I try to do the same thing but end up with the ultimate decimal 3 error. the SQL procedure works fine on both "SQL-Scripts" and "STRSQL".

Post Reply