Month: October 2022

Post Content

Tim Rowe is the Business Architect for IBM i responsible for System Management and Application Development.
Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Business Architect.

In most of IBMi we have a large number of QUERIES.

Often these QUERIES are provided to end users, with all limitations of this practice. I think this practice is deplorable.

How about turning these queries into SQL and then writing an RPG with embedded SQL?

The QUERY to SQL translation process is really simple, it only takes one command:

RTVQMQRY QMQRY(MyQueryLib/MyQuery) SRCFILE(MyLib/MySrcFile) ALWQRYDFN(*YES)

That’s it!

Below an example of a cursor declaration in an SQLRPGLE program (Sql embedded)

// preapre the data recordset

exec sql declare C1 cursor for
select OTORD00F.OTANN0,
OTORD00F.OTCOR0,
CLANA00F.CLNOM0
from OTORD00F
inner join CLANA00F on OTCCL0 = CLCCL0
where OTANN0 like :F1ANN0lke
and OTCOR0 between :F1COR0str and :F1COR0end
and CLNOM0 like :F1NOM0lke
order by OTCOR0
for read only;

Here is a complete SQLRPGLE sample program with SQL embedded.

#IBMiSample

Control where to which field the cursor goes next on a display file with #IBMiRPG.
💙#IBMi #rpgpgm #IBMChampion
rpgpgm.com/2020/06/contro…

– Simon Hutchinson (@RPGPGM)09:30 – Oct 20, 2022