RE: if defined syntax in sql rpg?

[James Rich <james@xxxxxxxxxxx>]

On Wed, 15 Aug 2001 bmorris@ca.ibm.com wrote:

> >From: James Rich <james@eaerich.com>
> >
> >The problem is that I need to chose between two different SQL statements.
> >The idea here is that our code should be portable across different
> >customers.  ...
> >
> >C/if defined(USE_NUM_COLOC)
> >
> ><use numeric comparison in select>
> >
> >C/else
> >
> ><use alpha comparison in select>
> >
> >C/endif
>
> James, one way you could solve this problem would be to have a
> customer-specific copy file that just has the necessary defines,
> and /copy it into every program.

Hmm... I tried just /define-ing USE_NUM_COLOC and compiling (skipping any
attempt to use the command line parameters to the compiler).  But now I
realize that the SQL preprocessor runs prior to the RPG preprocessor so
that /defines aren't processed.  So the preprocessor select above doesn't
cause the compiler to ignore one block of code or the other - the SQL
preprocessor looks at both - very bad.  Compiles die with errors saying
that the SQL cursor has already been defined.  Here is the actual code:

C/if defined(USE_NUM_COLOC)
C/EXEC SQL DECLARE C1 CURSOR FOR
C+   SELECT CMCSNAME, CMADDR1, CMCITY, CMCST#6, CMCST#3
C+   FROM CUSTML1
C+   WHERE (CMCSNAME>=:SHCSNAME) AND
C+         (CMCSNAME) LIKE :csgrpkwda AND
C+         (CMSRVCO# IN (:co#0, :co#1, :co#2) AND
C+          CMDEL<>'D') ORDER BY CMCSNAME
C/END-EXEC
C*
C/else
C/EXEC SQL DECLARE C1 CURSOR FOR
C+   SELECT CMCSNAME, CMADDR1, CMCITY, CMCST#6, CMCST#3
C+   FROM CUSTML1
C+   WHERE (CMCSNAME>=:SHCSNAME) AND
C+         (CMCSNAME) LIKE :csgrpkwda AND
C+         (CMSRVCO# IN (:co#0a, :co#1a, :co#2a) AND
C+          CMDEL<>'D') ORDER BY CMCSNAME
C/END-EXEC
C/endif

As you can see the statements only differ in that one selects in fields
(:co#0, :co#1, :co#2) (all numeric fields) and the other selects in fields
(:co#0a, :co#1a, :co#2a) (all alpha fields).  Since the SQL preprocessor
runs before the RPG preprocessor (or just ignores /if def, etc. all
together) I end up with two  SQL DECLARE C1 CURSOR FOR ... statements.  So
even forcibly defining USE_NUM_COLOC doesn't help :(

So I guess I can:

1. wait for IBM to get it together and run the RPG preprocessor before the
SQL preprocessor or teach the SQL preprocessor to be as smart as the RPG
preprocessor.

2. keep two source files with lots of comments explaining why one is
different than the other.

3. ask if there is a way to put the SQL statements in a string and then
pass that string to the SQL engine (like MySQL or postgres does).  Maybe
like:

C       eval    string = 'EXEC SQL DECLARE C1 CURSOR FOR blah ...'
C<somehow tell the SQL machine to run the contents of string <string> >

So how about it?  Is there a way to do option 3?

James Rich
james@eaerich.com