-Recordset processing with SQL. I can definitely see putting INSERT, UPDATE and DELETE logic in a sub procedure and a DB access service program perhaps, but what about queries that select resulting recordsets.
Can I run an SQL from a sub procedure and return the resultset or cursor to the main program like I would in other languages ? I’m guessing I can but most of my embedded SQL has been in single mainline programs and not buried in sub procedures thus far. >>Also does a named cursor open survive across calls ? I’m starting to visualize it does because you have the CLOSQLSR parm on CRTSQLRPGI which can tell you when to close the cursor.
You have to learn to think differently (not in "Programs") but in "reusable code".
CRUD Functions are good candidates for (exported) sub-procedures. Finally you should have only a single procedure on your system which will write a new record to a file and a single one which will do the update and a single one which will do the delete.
Within these function you may call other functions, for examples for to check or set default values or check dependencies between two columns (in for a INSERT or UPDATE Statement) and to check if there are dependent rows for deletes (sure it would be better if the tables would be secured with Check Constraints, Referential Integrities and Triggers ... but most databases/tables are NOT).
... and if you want to do an update from outside, just wrap the function as a Web Service.
Instead of returning RESULT SETS, I'd suggest to write a procedure that defines the Cursor and loops through the cursor ... and calls from there additional procedures.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Richard Schoen
Sent: Saturday, 21 March 2026 15:40
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: RPG Service Program vs Include Source for Shared Subprocedures and SQL Calls
Thanks for all the input.
Service Programs seem to make sense.
You’ll have to forgive my relative ignorance on this specific topic.
RPG hasn’t been a primary use language for me in several years so I’m trying to use all the modern goodness.
Now the question is what types of things do you put in your sub procedures and service programs ? In other languages I usually create re-usable functions for things such as:
string handling, file handling, inserting, querying recordsets, updating or deleting records and other small units of work. I guess that’s how I’ve used sub procedures in RPG before as well to date but have used includes mainly.
What about core recordset processing ? Should that specialized logic stay in each individual program or in a sub procedure.
Ex: Create cursor and open it in a sub procedure. Then iterate in the mainline. Then call a sub procedure to close the cursor. Seems a bit overkill. But maybe not if you want to encapsulate all your DB access logic into a single service program for Open, Query, Iterate Next Row, Insert, Update, Delete and Close Cursor.
I suppose it really depends on how many places that logic will be re-used.
There are two examples I can thing of where I might not want to encapsulate logic into a service program.
-When I am processing table a record at a time using record level IO it may not seem to make sense because the open file is in the main module. Or do the service program modules see the open file pointers as well ? In other words does the file open file cross program and procedure boundaries and make it accessible on the inner sub procedure calls ? When a subproc is embedded in an RPG program I believe it can still see the record level opens that are program scoped. Is that the same with service programs ? I guess the summary here is would record level access be appropriate in a service program for chain, read, update, delete.
-Recordset processing with SQL. I can definitely see putting INSERT, UPDATE and DELETE logic in a sub procedure and a DB access service program perhaps, but what about queries that select resulting recordsets. Can I run an SQL from a sub procedure and return the resultset or cursor to the main program like I would in other languages ? I’m guessing I can but most of my embedded SQL has been in single mainline programs and not buried in sub procedures thus far. Also does a named cursor open survive across calls ? I’m starting to visualize it does because you have the CLOSQLSR parm on CRTSQLRPGI which can tell you when to close the cursor.
As with all things software development I’m sure you all have your own patterns that you use. I’m just trying to establish my new ILE pattern thought process since some current projects require a bunch of new RPG coding which will include embedded SQL.
Hope this makes sense :-)
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
