Re: Viewing the resultant SQL in an RPG program

Joe,

That is what I usually do but what I think I see and what actually gets passed to DB2/400 can sometimes be subtly different enough to get past me. If I could see the final statement, and, better yet, copy and past that statement into an interactive session, it would be so much better.

This issue is related to my prior posts for *LOVAL and *HIVAL. I have several values that could be submitted in the SQL and I am using the technique where I use the BETWEEN keyword and either place a value which is the same for each of the between host variables or I use *LOVAL *HIVAL in the between. eg

C/free
update ppay408b set twlped = :NewYYMMDD where
twlped between :OldYYMMDD1 and :OldYYMMDD2 and
twlssn between :ssn1 and :ssn2 and
twlcus between :usr1 and :usr2 and
twljob between :job1 and :job2 and
twlxjb between :act1 and :act2;

When I view the *LOVAL and *HIVAL variables for the character fields in debug I just see a highlighted empty block which I assume are special characters. I'm going to comment out a few of the "between" statements and see if I can figure out what is going on. However, somewhere in the bowels of this system i I know that a fully resolved sql statement is lurking, I'd love to be able to see it so I know what is being executed.

Pete


Joe Pluta wrote:

From: Pete Helgren

How are folks debugging SQL statements that have host variables that are
resolved at runtime? Is there a way to debug a RPG program with
embedded SQL so you can see the "resolved" statement as it is passed to
DB2/400? I have an RPG program with embedded SQL that is returning no
rows but when I run a statement that should be similar (the "should be"
is the catch!) in interactive SQL I do get rows returned.

I'd like to "see" what DB2/400 sees when the SQL statement is
processed. Any way to do this ?

Typically I would just set a breakpoint before the statement and examine the
host variables, then execute the statement in STRSQL. For example, if I
have "SELECT PRICE FROM ITEMS WHERE ITMNO BETWEEN :TOP and :BOTTOM", then I
would stop there, examine TOP and BOTTOM, and then type it in.

There are issues with this, especially with quotes, but in general it works
very well. What are you having a problem with, Pete?

Joe