Re: Viewing the resultant SQL in an RPG program

I created a copy of source file QSQLTEMP1 in my library and changed the
CRTSQLRPGLI command TOSRCFILE option to point to that source file when
compiling an SQLRPGLE program. I also changed the DBGVIEW option to
*SOURCE, *LIST also works. Debug can then pick up this source and you can
see the SQL fields which are inserted by the precompiler. Debug can then be
used to view the values used in or retrieved by the SQL statements.

Here is an example that I am currently working on.

c*exec sql
c* Insert into CLM520P2 (
c* Select trcmyr, trcmyc, trlcvg, trlsyy, trrctp, trpsmm, trpsyy,
tramnt,
c* 'L',
c* Case trpctp
c* When 'AD' Then 'AA'
c* When 'BA' Then 'AA'
c* Else trpctp
c* end
c* From hclmtran, hlcvgcd
c* Where trlsyy between :stryear and :endyear and
c* (trpsyy between :stryear and (:endyear - 1) or
c* (trpsyy = :endyear and trpsmm <= :cutoff)) and
c* trpctp in ('AD','BA','CP','GK','PA','PP','P2','TP','T8') and
c* trlcvg = lccd2g and gpcd2g in ('A','L'))
c*end-exec
C EVAL SQL_00005 =
STRYEAR SQL
C EVAL SQL_00006 =
ENDYEAR SQL
C EVAL SQL_00007 =
STRYEAR SQL
C EVAL SQL_00008 =
ENDYEAR SQL
C EVAL SQL_00009 =
ENDYEAR SQL
C EVAL SQL_00010 =
CUTOFF SQL
C Z-ADD -4
SQLER6 SQL 3
C CALL
'QSQROUTE' SQL
C PARM
SQLCA SQL
C PARM
SQL_00000 SQL


Gene

On 9/6/07, Pete Helgren <Pete@xxxxxxxxxx> wrote:

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 ?

Pete Helgren

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.