Preprocessing C/C++ for embedded SQL Statements

When you use embedded SQL in your C/C++ code, you first run PREP command to process all EXEC SQL statements in your source. The PREP step happens before C/C++ compiler is able to invoke its preprocessor that will include header files, expand trigraph and replace DEFINE constants in your code.

This poses some challenges for the application developers since they can not use DEFINE constants in the variable that is within the bounds of EXEC SQL statements.

Let us examine this sample code that should return the server time.

test.sqc file

#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#define NAME_COLUMN_SIZE 27 
int main()
{
  EXEC SQL INCLUDE SQLCA;
  EXEC SQL BEGIN DECLARE SECTION; 
     char name[NAME_COLUMN_SIZE]; 
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO error_section; 
  EXEC SQL CONNECT TO SAMPLE;
  EXEC SQL VALUES CURRENT TIMESTAMP INTO :name;
  EXEC SQL CONNECT RESET;
  printf ("Server time is %s\n", name);
  return 0;
  error_section:
  {
      printf ("Error occured SQLCODE = %d\n", sqlca.sqlcode);
      return -1;
  }
}

In this code we define NAME_COLUMN_SIZE as 27 and we are using this constant in the following section.

 
  EXEC SQL BEGIN DECLARE SECTION; 
     char name[NAME_COLUMN_SIZE]; 
  EXEC SQL END DECLARE SECTION;

Let us try to run DB2 PREP command to create a C file from SQC.

$ db2 connect to sample
$ db2 prep test.sqc
LINE    MESSAGES FOR test.sqc
------  --------------------------------------------------------------------
        SQL0060W  The "C" precompiler is in progress.
   12   SQL0008N  The token "NAME_COLUMN_SIZE" found in a host 
                  variable declaration is not valid.
        SQL0092N  No package was created because of previous 
                  errors.
        SQL0091W  Precompilation or binding was ended with "2" 
                  errors and "0" warnings.

The PREP command is complaining that it does not understand the token NAME_COLUMN_SIZE. How do you use such constants in host variables? The answer lies in using PREPROCESSOR switch in the PREP command to let preprocessor run before PREP attempts to replace EXEC SQL statements from your C/C++ code.

$ db2 connect to sample
$ db2 "prep test.sqc preprocessor 'gcc -I$HOME/sqllib/include -E -o test.i' bindfile"
$ db2 bind test.bnd
$ gcc test.c -L$HOME/sqllib/lib -ldb2 -o testprg
$ ./testprg
Server time is 2007-04-12-12.29.57.169084

Please pay attention to the PREP command above. The whole command is surrounded by double quotes and the argument to PREPROCESSOR switch is enclosed in single quotes. We are passing -E switch to gcc to let it invoke preprocessor and with -o switch the output is put in .i file. If you are using AIX xlc compiler, you do not need to use -o switch. This is specific to gcc compiler. The DB2 PREP command knows that it needs to process .i file instead of .sqc if you use PREPROCESSOR switch to PREP command.

You might want to explain the SQL statements in your SP or batch programs using syntax shown as below so that it is helpful later on when you need to fine tune your C/C++ program.

$ db2 connect to sample
$ db2 -tf $HOME/sqllib/misc/EXPLAIN.DDL  (only once)
$ db2 "prep test.sqc preprocessor 'gcc -I$HOME/sqllib/include -E -o test.i' bindfile package test"
$ db2 bind test.bnd  explain yes explsnap yes
$ gcc test.c -L$HOME/sqllib/lib -ldb2 -o testprg
$ ./testprg
$ db2expln -d sample -c $USER -p test -graph -o test.expl
$ vi test.expl (see explain output)

Hope this helps you.

Note:Ramakrishna Venkataraman informed me that he had to use -P flag on AIX instead of -E flag as shown above.