RE: Dynamic sql embedded

["Jeff Wurst" <jwurst@xxxxxxxxxxx>]

This is RPG III code but the important part is the same.

C                     MOVE *BLANKS   SQLSTT          
C/exec sql                                           
C+        declare scr2Cursor Cursor for scr2Statement
C/end-exec                                           
C/exec sql                                           
C+        prepare scr2Statement from :RUNSQL         
C/end-exec                                           
C/exec sql                                           
C+        open scr2Cursor                            
C/end-exec                                           
C/exec sql                                           
C+        fetch next from scr2Cursor into :STMCOD    
C/end-exec                                           
C           SQLSTT    DOWEQ'00000'                
C           SQLSTT    OREQ *BLANKS                
C           STMCOD    CHAINSTATREC              10
C                     MOVE STDESL    S2LONG       
C                     MOVE STDESS    S2SHRT       
C                     MOVE STMCOD    S2CODE       
C                     ADD  1         RRN2E        
C                     Z-ADDRRN2E     RRN2         
C                     WRITECTRLCS2                
C/exec sql                                        
C+        fetch next from scr2Cursor into :STMCOD
C/end-exec                                       
C                     ENDDO                      
 *                                               
C/exec sql                                       
C+        close scr2Cursor                       
C/end-exec                                       

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of James Rich
Sent: Tuesday, July 10, 2007 4:44 PM
To: rpg400-l
Subject: Dynamic sql embedded

Hi everyone,

I've been struggling with embedded SQL in RPG.  I need to create dynamic

statements.  Here's what I'm trying to get to work:

Dquery            S           1024A
...
C                   if        (company <> 0)
C                   eval      query = 'DECLARE C1 CURSOR FOR SELECT ' +
C                             'PENAME,PEADR1,PESS#,PEEMP# ' +
C                             'FROM PRMSTP ' +
C                             'WHERE (PENAME LIKE :csgrpkwda) AND ' +
C                             '(PECO# = :co#0) ' +
C                             'ORDER BY PENAME'
C                   else
C                   eval      query = 'DECLARE C1 CURSOR FOR SELECT ' +
C                             'PENAME,PEADR1,PESS#,PEEMP# ' +
C                             'FROM PRMSTP ' +
C                             'WHERE (PENAME LIKE :csgrpkwda) ' +
C                             'ORDER BY PENAME'
C                   endif
C/exec sql
C+ PREPARE DYNSQLSTMT FROM :query
C+ EXECUTE DYNSQLSTMT
C/end-exec
C                   eval      query = 'OPEN C1'
C/exec sql
C+ PREPARE DYNSQLSTMT FROM :query
C+ EXECUTE DYNSQLSTMT
C/end-exec

I can't even get this (or any other incantation I've tried) to compile. 
Does someone have some sample code of using a dynamic SQL statement to 
populate a subfile?

James Rich

It's not the software that's free; it's you.
        - billyskank on Groklaw