Re: RPG Service Program vs Include Source for Shared Subprocedures and SQL Calls

Richard said:

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 ?

Yes! You can do it as a result set with ASSOCIATE RESULT SET LOCATORS
but it's sometimes convenient to use a callback; have the service
program do the actual access and have the caller 'do something' with
the records. Why a service program? Because you can do things like set
the authority on the table/view to public(*EXCLUDE) and then have the
service program apply whatever programmatic access, auditing,
notifications the business wants. For example, Accounting can get an
account balance but not the employee birth dates. What sort of 'do
something?' Imagine that you want to give an identical result set to a
DDS subfile, a JSON export for a web service, and an Excel file. With
a callback, the three different UIs each present the data as needed,
and none of them need to know anything about the actual structure of
the database - the service program uses SQL, F-specs, or a combination
to aggregate and return the data...

Also does a named cursor open survive across calls ?

It can, depending on activation group and cursor scope, but the caller
needs to track state and signal when it's done with the I/O. That's
kind of close coupling, but they pay us to make these decisions :-)

--buck