Re: Converting QM Query to rpg embedded SQL

I'm with you there, Lynne - and even in shops where STRSQL is available, it's generally not useful for end users, and it can't be put into a CL program and give anything much back.

One thing I like is making a CL command, use the parameters to build values for the substitution variables. This is very much like the ability to set dependent values in Query for i -- on the record selection list you can put a colon ahead of a name in the value, then use STRQMQRY to run the query.

These were usually created for use with OfficeVision and merging data into form letters.

So passing parameters to a QMQRY is very cool - sounds like your stuff could do the same kind of thing.

Cheers
Vern

On 2/22/2018 7:09 PM, Lynne Noll wrote:

I get it now. In civilized shops, people can't use STRSQL on production, but I've been required to use it for the last 20 years, so I didn't appreciate the need for an ad hoc tool that goes to display or print.


QMQRY is more secure, because it can be locked down to just select statements, but STRSQL is easier for ad hoc, with its history and prompting.



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


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

On one thought I find I don't agree - I know that the general-purpose
SQL processor I built was not to get around not being able to do
embedded SQL, since we always had that capability wherever I was.

My purpose was to do SQL at a command line, and especially to get ad hoc
reports easily. It wasn't meant to return anything to the program using
the command. The latter is a nice feature, of course.

IBM's RUNSQL gives us the ability to do any statement other than SELECT
- the various QM-based commands let us do SELECTs as well as DDL
functions like INSERT and the various CREATEs, etc. And I've thought
that using CREATE TABLE with a SELECT doesn't make it if what I want is
a display of data or a report of it.

And the reporting ability and output options of STRQMQRY are a part of a
more complete version of these utilities, some capabilities that IBM's
RUNSQL can't do, either.

Regards
Vern

On 2/22/2018 5:05 AM, Lynne Noll wrote:

-snip-


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

-snip-



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


--

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD