Visual LANSA Forum

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

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.

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