Re: Converting QM Query to rpg embedded SQL

I've seen the QMQRY utilities. They sparked my imagination. Giovanni Perotti added the substitution marker idea as part of HTML scripts-- I saw them before QMQRY. So many things need a few substitutions in a standard script!


However, the main purpose of the QMQRY utilities was getting around the lack of purchased SQL tools like embedded SQL in RPG.


I suggest building a simple RPG, COBOL or C utility to run the passed in select statement. It is very simple as long as the SQL is composed to return one field, which can be fields concatenated together. I have a couple--one a universal lookup window, and one that just returns a field at a time to a caller. Beyond letting you get data back to the caller without an output table, you can have substitution markers in your own template SQL string.


If you are going to an output table, you can use a CREATE TABLE AS command to run the select from RUNSQL. No substitution markers, though; you need to create a separate command to handle them.


You can set up for multiple variable columns back in your utility as well, but that is more work. More payback, but more work, and needs a budget. What I've described is super easy, and can be done as part of any project it eases.



Sent from Outlook<http://aka.ms/weboutlook>


________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> on behalf of Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Sent: Wednesday, February 21, 2018 8:37 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Converting QM Query to rpg embedded SQL

To expand a little on Lynne's explanation, it is possible to have a
QMQRY string like this - &Q1&Q2&Q3&Q4&Q5 - this would allow an SQL
statement up to 275 characters long, since each substitution variable
can be at most 55 long. This is the basis of almost all the utilities
people have made (Buck Calabro had one I made a variation of, and
others) that can run any SQL statement, including SELECTs (IBM's RUNSQL
does not directly run SELECT statements). The statement is broken up
into 55-character variables and passed in the SETVAR parameter of
STRQMQRY, which combines them into the statement, as Lynne describes.

I have a presentation and lab I present at COMMON about QM - usually the
lab is all we put on the schedule these days.

Regards
Vern

--