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

I don't see a problem with doing SQL statements and/or SQL cursor processing in a program.

If you don't want to implement too complex SQL queries in your SQLRPGLE programs, you have several good options:

First - by creating SQL views you can hide / encapsulate the complex query in a SQL object. Views do not make anything slower and do not result in any additional work for the SQL engine. Views are simply stored SQL queries - that's all.

Second - if the logic is even more complex, and a view is not an option, you can create UDTFs (User Defined Table Functions) easily in SQL or in RPG or any other HLL. All information about that can be found in Info Center - but Scott Klement has written a very good piece about implementing UDTFs in RPG which makes it a lot easier.

https://www.scottklement.com/udtf/

The last option would be using a stored procedure returning a result set - but UDTFs are the more modern and flexible tech IMHO.

When using SQL I don't see any necessity to abstract I/O away from the application program.

The only problem that stays is defining your RPG fields in a way, that a modification (change of data type or size) is reflected in your application.

We tackle that by using externally defined DS templates in copy books, and LIKEing your RPG variables on these definitions. Then don't use "*" in your programs and you won't have a big problem - but you will still have to recompile the programs after a table field changes type or length, to reflect that. Our SCM detects that and does the recompile automatically when the table change is run.

HTH
Daniel

Am 22.03.2026 um 00:02 schrieb Buck Calabro <kc2hiz@xxxxxxxxx>:

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