Today I had to modify some code at a client location where at one location the database file contained fields A,B,C and at the new location the same file contains fields A,B,C,D, and E.

Using Embedded SQL and the RPGPPOPT parameter (sadly it requires IBM i v5r3 or later) you can have the SQL Preprocessor respect and acknowledge the RPG compiler directives including:

• /INCLUDE
• /COPY
• /DEFINE
• /IF DEFINE
• /UNDEFINE
• /ELSE
• /ELSEIF
• /ENDIF

Without this parameter, or using its default of *NONE, the /COPY statement is expanded, and the /INCLUDE statement is not. No other directives are acknowledged. This is also the pre-v5r3 behavior. Here is the nickel-tour of the RPGPPOPT parameter:

RPG preprocessor options (RPGPPOPT) - IBM i v5r3

Specifies whether or not the RPG compiler will be used to preprocess and expand the source member based on compiler directives, before running the SQL precompile. When this option is anything other than *NONE, the RPG IV compiler is call BEFORE the SQL pre-processor. This allows the RPG IV compiler to process the compiler directives, such as /INCLUDE, /DEFINE, and /COPY and generate a resulting source member that is subsequently sent to the SQL Preprocessor. The RPG IV compiler is called after the SQL preprocessing is performed so that it can then, obviously, compile the resulting RPG source code.  The valid parameters (as of IBM i V5R3 through V7r1) are:

• *NONE - No RPG IV directive preprocessing is performed. But use of it indicates that /COPY statements are preformed.
• *LVL1 - Preprocessing is performed for all compiler directives EXCEPT /INCLUDE. This means /COPY, /DEFINE, /IF DEFINED, etc.
• *LVL2 - Preprocessing is performed for all compiler directives, including the /INCLUDE directive.

The default RPGPPOPT(*NONE) means "do no additional preprocessor processing except that which SQL has always done" or in other words, if you think of *NONE as *LVL0 and then consider the following:

• *NONE = /COPY are processed
• *LVL1 = everything from *LVL0 plus directive /DEFINE, /IF, /EOF, /ELSEIF etc.
• *LVL2 = everything from *LVL1 plus /INCLUDE statements.

It makes sense--just poor choice for implementation but that's pretty much how its been with RPG IV for a decade or so.

I recommend that you change your compiler default to RPGPPOPT(*LVL1) and use *LVL2 when necessary. Ironically, you cannot embed RPGPPOPT in the Header Specification nor on the SQL SET OPTION parameter.

In addition to the RPGPPOPT parameter, the SQL preprocessor has allowed additional RPG compiler command parameters to be specified right inside the RPG IV source itself, for at least the last 3 or more releases of the OS. Here's an example:

 EXEC SQL SET OPTION commit = *NONE
            COMPILEOPT='DBGVIEW(*LIST)';

This code should be embedded at the top of the RPG IV source member and would cause the COMMIT(*NONE) parameter to be used along with DBGVIEW(*LIST). Any other CRTBNDRPG parameter may be specified, within the quotes, for the COMPILEOPT parameter.

What good does this do for you?

Suppose you have customized code. A system where you want different data recorded when in development vs an in-production system. So you have a database table on SystemA that contains fields A, B, C and D, but on the development box, the table contains fields A, B, C, D, and E.

You could wrap your SELECT or other SQL statement in an RPG compiler directive such as /IF DEFINED and control which of the fields you want to include in "this" compile. Something like the following:

..... /DEFINE DEVBOX
     D A               S             10I 0
     D B               S             35A
     D C               S             12A
     D D               S              5A
     D E               S             10A
      /free
      /IF DEFINED(DEVBOX)
           EXEC SQL Select a,b,c,d,e
                    INTO  :a, :b, :c, :d, :e
                    FROM  custmast
                    WHERE a > 100;
      /ELSE
           EXEC SQL Select a,b,c,d
                    INTO  :a, :b, :c, :d
                    FROM  custmast
                    WHERE a > 100;
      /ENDIF

Now, by simply commenting-out the /DEFINE DEVBOX, you have the ability to modify what SQL code is compiled--something you could not do with Embedded SQL prior to IBM i v5r3.