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 - 1467 days ago
Last viewed on: 25 Mar 2017 (2686 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)

Good morning. I'm curious if I can do the following.

Create a stored procedure that calls an RPG program, which builds 2 files (header and detail). Then, execute a couple of SQL statements to retrieve the data from the 2 files as the results sets.

Kinda' like this:

 

 

CreateProcedure PollForSAPOrders

 

resultsets2

specific PollForSAPOrders

externalname spncisaor

languageRPGLE

parameterstyleSQL

begin

declare hdrfile cursor for select.......................

declare dtlfile cursor for select.......................

end

 

Any suggestions would be appreciated. The reason I'm wanting to do it this way is so that the RPG program could return a batch# that would be used to select the rows from the files, because multiple jobs could call this at the exact same time.

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 days 15 hours 7 minutes ago
Edited: Thu, 14 Mar, 2013 at 07:26:54 (1472 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 6 days 10 hours 22 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 6 days 9 hours 26 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 6 days 9 hours 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.