Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Can RPG and SQL be in the same stored procedure?
has no ratings.
Published: 13 Mar 2013
Revised: 19 Mar 2013 - 1798 days ago
Last viewed on: 19 Feb 2018 (3515 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Can RPG and SQL be in the same stored procedure? Published by: Chris Proctor on 13 Mar 2013 view comments(4)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Can RPG and SQL be in the same stored procedure?
Posted: 4 years 11 months 8 days 8 hours 50 minutes ago
Edited: Thu, 14 Mar, 2013 at 07:26:54 (1803 days ago)

Yes, though I think I usually use general parameter types, and don't forget READS SQL DATA, if you're creating cursors to return as result sets. If you're just building data into an array for result set then you can just use USES SQL DATA.

And also a caveat I discovered just the other week, watch any service program procedure calls that you might make. I updated a service program to write audit/error records using SQL and blew up a stored procedure, had to get them to change it to MODIFIES SQL DATA!

And in cases where I want the process used by both external SQL calls as well as local programs, I will sometimes create a service program procedure that calls the same program.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Can RPG and SQL be in the same stored procedure?
Posted: 4 years 11 months 3 days 4 hours 6 minutes ago

Thanks for the tip, Neil. I too have a process in a srvpgm that writes out errors. I'll be sure to insure that "modifies SQL data" is in my sprocs.

Chris

Posted by: Ringer
Premium member *
Comment on: Can RPG and SQL be in the same stored procedure?
Posted: 4 years 11 months 3 days 3 hours 10 minutes ago

That SQL CLAUSE refers to the entire CALL stack. I learned this one day when I added SQL to an existing program downstream in the CALL and suddenly my stored procedure stopped working.

CONTAINS SQL: Only SQL statements that neither READ nor MODIFY SQL
              data may be executed in the call stack (like a SET).
READS SQL DATA: Only SQL statements that do not MODIFY SQL    
                data may be included in the call stack.
MODIFIES SQL DATA: May run most any SQL statement.  

Chris Ringer

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Can RPG and SQL be in the same stored procedure?
Posted: 4 years 11 months 3 days 2 hours 44 minutes ago

The impact comes where you have non-i folks writing Stored Procedures for web faced processes. Their Stored Procedure calls an i program to get some information - pricing, material usage - and the i folks change the program to audit the calculation or data fetch.

The i folks don't notice the problem, their function runs quite well on the green screen applications. The web guys aren't informed that the i resident process has been redesigned. You can end up with customers finding the issue for you.

The futher the service programmer is removed from the requester the more likely the issue is to come up.