Re: Converting QM Query to rpg embedded SQL

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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.