Dynamic embedded SQL (in RPG) example -Reply

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

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

I have a utility I wrote one day in
frustration (not all our clients have IBM
interactive SQL utilty STRSQL, which makes
a "quick file fixes" into a "write an RPG
fix pgm" - yeeech).  

Pgm accepts the entire SQL statement as a
character string from a simple display
file, then executes it as follows

C/EXEC SQL
C+ EXECUTE IMMEDIATE :$SQL
C/END-EXEC

($SQL being the display file variable)

It's dirt simple & it works & obviously, if
one can type a statement dynamically into
variable $SQL, one could also build it
w/CATs and SUBST.  The drawback is that a
simple SELECT returning some (varient)
number of records/fields from a file
doesn't work - not knowing ahead of time
what the return "record format" is, the
query processor returns a pointer to a data
space (terminology may be incorrect, but
the idea is accurate).  Since I wrote the
utility in RPGIII (e.g. before pointer
support) this functionality was out of the
question, but should be addable in RPGIV
(maybe someday I'll get frustrated again
and do it myself :))

HTH

Scott Cornell
Mercy Information Systems
+---
| 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
+---