I don't think "CONTAINS SQL" is correct. Try the READS SQL option instead.
-----Original Message-----
From: (WalzCraft) Jerry Forss [mailto:JForss@xxxxxxxxxxxxx]
Sent: Monday, February 13, 2017 8:49 AM
To: Midrange Systems Technical Discussion (midrange-l@xxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxx>
Subject: Stored Procedure returning result set
Hi All,
I have a RPG pgm that gets created as a stored procedure so the MS SQL side can use it.
Worked fine as SQLRPGLE putting together multiple occurrence DS and returning as set with only RPG reads.
I have done this many times.
The Results is being loaded using reads from mult file.
//? DS For Data Set
D Results DS Occurs(1000)
D RItemNo 15
D RItemDesc 30
D RItemClass 4
D RItemUOM 2
D RItemVSICode 8
D RLot 10
D RLocation 7
D ROnHand 10S 3
D RMLdConv 3S 0
D RMatch 1
Exec SQL
Set Result Sets Array :Results For :Row Rows;
Because of a new complexity I am using SQL to get the info from the files, load the Results DS.
There is chains and other logic to load some of the fields.
Thing is if I run the pgm by just calling it, it works correctly.
I create the procedure
CREATE PROCEDURE
Utilities/SPIvsiitmR
(IN VSICode CHARACTER ( 8),
IN Profile CHARACTER ( 4),
IN Class Character ( 4),
IN Environment Character ( 2))
Result Sets 1
LANGUAGE RPGLE
NOT DETERMINISTIC
CONTAINS SQL CALLED ON NULL
INPUT EXTERNAL NAME Utilities/SPIVSIItmR
PARAMETER STYLE GENERAL
When I call it as a stored procedure it fails (No data, not blows up).
In the job log
Query options retrieved file QAQQINI in library QUSRSYS.
Query options retrieved file QAQQINI in library QUSRSYS.
Query options retrieved file QAQQINI in library QUSRSYS.
The query access plan has been rebuilt.
PREPARE of statement LISTCMD completed.
Reading SQL data not permitted.
Cursor LISTCURSOR not open.
1 result sets processed by the SET RESULT SETS statement.
Query options retrieved file QAQQINI in library QUSRSYS.
1 result sets are available from procedure SPIVSIITMR in *N.
Message ID . . . . . . : SQL0579
Date sent . . . . . . : 02/13/17 Time sent . . . . . . : 08:27:51
Message . . . . : Reading SQL data not permitted.
Cause . . . . . : One of the following errors has occurred:
-- A procedure was called or a function was invoked that was created with
CONTAINS SQL DATA as the data access attribute. A procedure or function
created with CONTAINS SQL DATA or any procedure or function that is called
by the procedure or function, cannot read data and cannot call a procedure
or function that has the READS SQL DATA attribute.
-- CONTAINS SQL DATA cannot be specified on the CREATE PROCEDURE, ALTER
PROCEDURE, CREATE FUNCTION, or ALTER FUNCTION statements for an SQL
procedure or function if the routine body contains statements that read
data.
This is a first where I have an RPG pgm that executes SQL, loads a Result set DS that gets returned.
I am sure it is something simple I am not understanding so guidance is appreciated!
Jerry Forss
Information Technology Team
WalzCraft
608-781-6355 EXT 2530
jforss@xxxxxxxxxxxxx
Subject to Change Notice:
WalzCraft reserves the right to improve designs, and to change specifications without notice.
Confidentiality Notice:
This message and any attachments may contain confidential and privileged information that is protected by law. The information contained herein is transmitted for the sole use of the intended recipient(s) and should "only" pertain to "WalzCraft" company matters. If you are not the intended recipient or designated agent of the recipient of such information, you are hereby notified that any use, dissemination, copying or retention of this email or the information contained herein is strictly prohibited and may subject you to penalties under federal and/or state law. If you received this email in error, please notify the sender immediately and permanently delete this email. Thank You;
WalzCraft PO Box 1748 La Crosse, WI. 54602-1748
www.walzcraft.com<
http://www.walzcraft.com/> Phone... 800-237-1326
midrange.com
