Re: Viewing the resultant SQL in an RPG program - RESOLVED

Vern,

Interesting that your way is the way that I had done it in the past. Pretty much built a SQL statement as a variable so that I *could* cut and paste the resolved statement into an interactive session and do a reality check. However, as I was poking around on some other issue and came across a post from Birgitta (I think) touting the benefits of the "between" method in terms of speed. It certainly made the statement simpler to read. So that is why I took the approach. The downside being that there isn't a way to see the resolved statement.

Ah well. You learn something new every day. If I end of doing much of this kind of thing I will look at additional tools to make it easier. Using the SQL performance monitor was a kick! Nice tool. I can even see all the JDBC stuff that way, which is awesome.

Thanks for your helpful pointers.

Pete


vhamberg@xxxxxxxxxxx wrote:

Pete

Just occurred to me - that's why I often will build the statement in a variable and do a PREPARE or EXECUTE IMMEDIATE or some such - then I know exactly what I have - unfortunately, I lose the benefits of performance from having parameter markers.

Again, if you need this a lot, it might be worth talking to Centerfield Technology - www.centerfieldtechnology.com - I believe they have products that do fully render the statement.

Later
Vern

-------------- Original message ----------------------
From: Pete Helgren <Pete@xxxxxxxxxx>

OK. Thanks for all the help and information. Although none of it directly solved the issue, each little piece added another clue.

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.

Thanks to all,

Pete Helgren