Re: SQL Issues

["Schadd D Gray" <list@xxxxxxxxxxxxx>]

Chris,

Stored procedures written in RPG can return multiple result sets if the
result sets are CURSOR based.  RPG can only return a single ARRAY based
result set.

Your issue, I believe, is your CREATE PROCEDURE.  Here is the one I used.  I
get two result sets back on a V4R5 machine.

CREATE PROCEDURE TESTPROC/TESTPROC6(IN CDE CHAR (10), IN PRM CHAR (1024),
OUT RTN INT) RESULT SETS 2
LANGUAGE RPGLE SPECIFIC TESTPROC/TESTPROC6 NOT DETERMINISTIC READS
SQL DATA EXTERNAL NAME TESTPROC/TESTPROC6 PARAMETER STYLE GENERAL

The key item is the READS SQL DATA.  This allows you to open and return the
cursor.

You may want to try a product of ours called STE (Stored procedure Test
Environment).  It allows you to test this from the green screen.  Much
easier than OPS NAV.

If you use STE to test this, your version will show the following error in
the JOBLOG.

Reading SQL data not permitted.
Cursor C1 not open.

Thanks,
Schadd Gray
Damon Technologies, Inc.
www.damontech.com

----- Original Message ----- 
From: "Chris Wolcott" <CWolcott@xxxxxxxxxxxxxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, June 16, 2004 2:48 PM
Subject: RE: SQL Issues


>
> I compiled my SQLRPGLE program with DBGVIEW(*SOURCE) and CALLED it from
> a command line.  I can fetch the cursor contents into a DS table with no
> problem.  However, when I call it as a Stored Procedure the result set
> is still empty.  Ack!  Phht!  If I use RPG commands to populate the
> table, I can see them in the result set but not if I use a cursor.
>
> Here is the CREATE PROCEDURE code:
>
> CREATE PROCEDURE library/rpgproc
> (IN CDE CHAR (10), IN PRM CHAR (1024), OUT RTN INT)
> DYNAMIC RESULT SETS 1
> LANGUAGE RPGLE  NOT DETERMINISTIC  CONTAINS SQL
> EXTERNAL NAME library/rpgproc
> PARAMETER STYLE GENERAL WITH NULLS
> CREATE PROCEDURE statement complete.
>
> Any ideas?
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>