Re: Viewing the resultant SQL in an RPG program

[Pete Helgren <Pete@xxxxxxxxxx>]

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
>
>
>