I know we're all moving towards SQL, but in the odd scenario you want to modularize record level access, would you write a subprocedure and have it open the file, do the read/write/update and then close the file as part of its process.
Or open the file in main program but just do the chain, read, write, update in the subprocedure.
Just trying to think of those scenarios where I want to modularize standard record level IO as well. Or is that over kill 😊
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
------------------------------
message: 2
date: Sun, 22 Mar 2026 11:08:48 +0100
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: c
Subprocedures and SQL Calls
-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
midrange.com
