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