Pete Helgren wrote:
8. Re: Viewing the resultant SQL in an RPG program - RESOLVED
(Pete Helgren)
OK. Thanks for all the help and information. Although none of it
directly solved the issue, each little piece added another clue.
Pete:
Although I don't have an answer, the general discussion might bring
better comments from those who know more than I do if I give my
understanding of what's happening. Once my comments are added, maybe
someone will offer corrections or additions.
In short, you aren't going to ever see this type of SQL statement in
debug with all the variables resolved. That's because it isn't resolved
in your program code; it's resolved in one or more of the SQL routines
that get called by your code.
When you write embedded static SQL with host variable references, the
SQL statement is handled somewhat like a statement with "parameter
markers". Each :host_variable has the initial colon (":") in order to
signal the SQL routines where each "parameter marker" is located in the
statement. The name that follows the colon is used by the pre-compiler
to set up references to the named variable.
The item that's possibly of interest to you is either the SQLDA if you
declare one yourself or whatever SQL uses when it does it automatically.
(It possibly still is an SQLDA; I've never looked but it makes sense to me.)
The SQLDA still won't have "the statement", but it should have variable
declarations and values that you can see in debug. The pre-compiler
and/or compiler know what attributes to assign to elements of the SQLDA
because you provide :host_variable names. Those host variable attributes
can be used by the pre-compiler and/or compiler to make sure that the
SQLDA matches up.
Now, when you hit a section of your program that runs SQL, the
pre-compiled code issues a CALL to an SQL routine. That CALL has PARMs.
The SQLDA is one of the things that are likely in the parm list and an
identifier that links to the pre-compiled SQL statement would be another
parm. The called SQL routine would use the values in the SQLDA to
replace the "parameter markers" and execute the function.
In your case, this probably happens when you OPEN and/or when you FETCH.
Regardless, the actual underlying SQL SELECT isn't actually in any
memory that you can see in debug. It's down inside the memory of the
called SQL program. That's why it takes the DB monitor to get a closer
representation of it at run-time -- it only 'exists' at that level.
AFAIK, that's kind of the whole point behind having a SQL "pre-compiler"
in the first place. In a sense, it hides all of the complexity of coding
SQL CLI.
But a lot of the same kind of stuff happens. You can see how it's done
if you review the actual pre-compiled version of your source.
That's about as far as I can go with musing how it all might work. Maybe
it clarifies, maybe it confuses. Just keep in mind that I made all of
this up over the time that I've worked with SQL. It's simply been how
I've thought it out. It keeps me from wondering things like what you're
struggling with.
Tom Liotta
The actual problem was not the *LOVAL/*HIVAL stuff, it was that I was
concatenating single quotes to each of the character fields out of
habit. Apparently the compiler is smart enough to recognize a character
field as such and a numeric field as such and properly format the
statement when referencing the host variables. When I code in Java I
need to make sure that character fields have single quotes so that the
statement is formatted correctly.
Dropping the appending of the single quote constants did the trick. All
of which brings me back around to my original "wish": If I could have
seen the resultant SQL (warts and all) and could do a cut and paste in
to an interactive session I would have immediately seen the issue and
fixed it. A debug view that shows the SQL statement fully rendered and
as it would be executed would be a sweet addition. I would have seen
this:
select * from myfile where
myssn between -999999 and 999999 and
myuser between ''PETE' ' and ''PETE' '
Which immediately I would have recognized as wrong.
midrange.com
