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

On 2/21/2018 6:27 AM, Brian Parkins wrote:

Thanks for the excellent explanation, Lynne.

Brian.

On 21/02/2018 01:47, Lynne Noll wrote:

QMQRY is a stored string with markers in it that can be replaced; you can replace parts of tokens  or a whole order by clause because you are just replacing the marker.  It doesn't know sql until after all the strings are replaced;  before then it is just string handling.


This is similar to running dynamic SQL imbedded in RPG.  You compose the string with string handling, and then prepare it. Before the prepare, nothing actually knows that the string is going to be SQL, and how you compose the string is up to your creativity.


Host variables are no longer strings; they  are areas of memory that can be pointed to by pointer and are accessed by bound routines that actually do SQL.  The routines don't care what you named the memory; they just point to it.  So they are only used for discrete data elements.   You can't use one for something like an expression; you can use it to specify the data on which the expression works.  You can't supply 2*3 as the host variable, but you can specify a separate host variable that contains 2 and another that contains 3. The SQL routines do not need to know the variables in advance since they are just going to do the same things to the information at that memory location; all this can happen after parsing.


Table names are more than data elements; they bring in lots of metadata when the SQL is being interpreted.  So they are needed before you start parsing the SQL, and can't be supplied as a substitution variable.  All the SQL keywords (select, from, where, order by) also need to be there before the parsing. However, you can supply them as variables in QMQRY during the string handling phase.    If you try CLI, you very much notice what is supplied by pointer to memory and what is supplied as an SQL string.










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


________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> on behalf of Brian Parkins <goodprophet.bp@xxxxxxxxx>
Sent: Tuesday, February 20, 2018 4:07 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Converting QM Query to rpg embedded SQL

<phew!>

To my untrained eye, I don't believe you can use Host Variables on a
CREATE TABLE statement. (There doesn't appear to be a definitive
reference of where you CAN use HV's. The manual simply states they are
"commonly used" on WHERE, INTO, SELECT, SET, VALUES and CALL.)

Perhaps one of resident experts could add some insight?

If this is a showstopper, then you'll need to consider using Dynamic SQL
embedded in your RPG IV code.

Brian.