Re: Dynamic embedded SQL (in RPG) example

[rbbaird@xxxxxxxxxxx (Rick Baird)]

Dan Swartz wrote:
> 
> Can anyone point me to a good example of dynamic embedded SQL in an RPG
> program?
> 
> I'd like to build the select statement depending on parameter values passed
> to the program.

try one of these: (code is presented as is and no warranty is expressed
or otherwise yadda, yadda, yadda.)

  ie. spacing is not correct, may mix rpg4 with rpgiii.)  :^)

 *  define file fields as data strucure:
D $file   E DS                 EXTNAME(filename)   ----rpg4

or 

E$FILE    E DSfile name                            ----rpgiii

 *describe sql data area.

c/exec sql
c+        include sqlda
c/end-exec

 * input parms

c        *entry      plist
c                    parm         parm1
c                    parm         parm2

 * method one - only one record is expected to meet selection criteria.

c/exec sql
c+         select *
C+           from filename
c+           into :$file
C+          where fld1 = :parm1
C+            and fld2 = :parm2
C/end-exec           

 *or   ---------------------------------------------------- 
 * method 2 - more than one record could meet criteria, read all.

c/exec sql
c+        declare c1
c+         cursor for
c+         select *
C+           from filename   
C+          where fld1 = :parm1
C+            and fld2 = :parm2
c+       order by fld3, fld4       
c+            for fetch only
c/end-exec

c/exec sql
c+           open c1
c/end-exec
            
c        1           DOWEQ1             

c/exec sql
c+           fetch c1
c+            into :$file
c/end-exec

C        SQLCOD      IFNE  0
C                    LEAVE
C                    END
C                 

C                    EXSR PROCES
C                    ENDDO

c/exec sql
c+           close c1
c/end-exec

 * or ------------------------------------------------
 * method 3 
 * create a character variable ($string) to contain:
 * "select * from filename where fld1 = ? and fld2 = ? order by fld3"

 *(the question marks are called "parameter markers" to be used to
pass   *variables into the statement using "open with" statement.)

c/exec sql
C+        prepare p1 from :$string
c/end-exec

c/exec sql
c+        declare c1
c+         cursor for p1
c/end-exec

c/exec sql
c+           open c1 
c+          using :parm1, parm2
c/end-exec
            
c        1           DOWEQ1             

c/exec sql
c+           fetch c1
c+            into :$file
c/end-exec

C        SQLCOD      IFNE  0
C                    LEAVE
C                    END
C                 

C                    EXSR PROCES
C                    ENDDO

c/exec sql
c+           close c1
c/end-exec

    -------------------------------------------

I'm not sure why you sometimes need to "prepare" statements, - never
took the time to figure it out - sometimes it won't work the first way,
but if I prepare it, it does work.  Also, if you are going to run the
statements many times, with different values in your parameters, it is
supposed to be faster using the "prepare" and "open... using" method
than just the "declare/open" method.

hope this helps.

rick@work.zzz
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MAJORDOMO@midrange.com
|    and specify 'unsubscribe MIDRANGE-L' in the body of your message.
| Questions should be directed to the list owner/operator: david@midrange.com
+---