DB2: Embedded SQL for C and C++ 

 
This will walk you through "compiling" an embedded SQLC program. The Ramakrishnan text gives examples of writing embedded SQLC and C++ programs, and the text is basically correct for DB2's version of embedded SQL in C (and C++). See the directories
  • ~db2educ/sqllib/samples/c/ 

    • and
  • ~db2educ/sqllib/samples/cpp/
for examples and compilation instructions for C and C++, respectively. See below for another embedded SQL C program.

Embedded SQL for C 
  1. Connect. 


    2. One needs to first be connected to the database under which the application program (that is, the embedded SQL C program) is intended to run for the sake of the next two steps. So 

    % db2 connect to database 
  2. Pre-compile. 


    3. One needs to pre-compile the embedded SQL code to replace the embedded SQL with legitimate C calls. This is done by 

    % db2 precompile program.sqc bindfile 
    It is convention to name one's embedded SQL C program with ".sqc" at the end to indicate that it is an SQL-C program. The command 
    % db2 prep program.sqc bindfile 
    is identical. The keywords "prep" and "precompile" mean the same for DB2. 

    This pre-compilation step will create a program.c file which is pure C code. It has in it function calls that replace the embedded SQL and will be linked via libraries in the linkage step below. 

    This step also creates a program.bnd file, which is used in the bind step discussed next. 

  3. Binding. 


    4. The bind step creates a package in DB2. An application program (APP) must have a package counterpart within DB2 to function. The package defines in essence a profile of the APP for the database. In "binding" the package, one can define many aspects of how the APP should be run (for instance, isolation levels), and permissions are checked and verified by DB2 at this stage. Whenever the APP is run later, it will be run with respect to the database as the DB2 user who owns the package to which it is bound. 

    To bind the APP (and hence, to create a package) say 

    % db2 bind program.bnd 
    Various options can be trailed on the command above. These effect the package created, which in turn effects how the APP is run with respect to the database. Read the documentation on "bind" if you are curious. 

    The package created will have the same name as program. Packages are like tables in a DB2 database. They are objects which belong to schemas. To see one's current packages, type 

    % db2 list packages 
    To drop a package, say 
    % db2 drop package package-name 
    Of course, the associated APP will no longer work. One would have to bind it again before it will work again. Also note that there is no "create package ...". Packages are created via "bind". 
  4. Compile. 


    5. Finally, we are at the stage to compile the program. First do the following: 

    % source ~db2educ/sqllib/db2cshrc 
    To make life easier, set an environment variable DB2PATH as follows. 
    % setenv DB2PATH ~db2educ/sqllib 
    All the libraries, includes, and such needed for the compile and linking are located there. 

    It is a good idea for us to have some error-checking utilities of DB2 built into the APP. So first compile that. 

    % cc -I$DB2PATH/include -c $DB2PATH/samples/c/utilapi.c 
    Now, we finally compile the APP program itself. 
    % cc -I$DB2PATH/include -c program.c 
    Note: Just because the DB2 pre-compiler and the bind may have not reported errors, it does not mean that the C compiler will not report errors to you here! This is the first time your program has been parsed and compiled by the C compiler, and will be the first time you get a report of any C-code problems. 
  5. Linking. 


    6. In the final step, one must link with the appropriate libraries to create the executable APP. 

    % cc -o program program.o utilapi.o -L$DB2PATH/lib -R$DB2PATH/lib -ldb2 
If all went well, you now have a working executable APP called program.

Embedded SQL for C++ 
This is basically the same as for C as described above. Just replace the compiler "cc" with "CC" for C++.

The convention for naming the DB2 folks use for embedded SQL C++ programs is to add the suffix ".sqC" to the program code file. And they use the suffix ".C" for C++ programs. So if you want to link in the DB2 error utilities, say

% CC -I$DB2PATH/include -c $DB2PATH/samples/cpp/utilapi.C
instead.

An example embedded SQL C program 
Here is an example embedded SQL C program created last term for the database go3421. It does not do much, but is instructive. The APP queries a table sailor in schema techstu. User techstu has granted select privileges to all on table sailor, so the bind step will be legal. 
sage.sqc
  
/*----------------------------------------------------------------------------
EXAMPLE of an embedded SQL C Program for DB2.

Connect to database GO3421 for this regardless of your section.

This APP takes one argument on the command line, a sailor's SID.  It
then finds the sailor SID's age out of the table TECHSTU.SAILOR (in
database GO3421) and reports it.  A dumb and not very interesting APP,
but it shows how things are done.

Written by D. Zilio
Tweaked by P. Godfrey
NOV 1999
----------------------------------------------------------------------------*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlcodes.h>
#include <sys/time.h>

#define EXIT   0
#define NOEXIT 1

/*----------------------------------------------------------------------------
Include DB2's SQL error reporting facility.
----------------------------------------------------------------------------*/

EXEC SQL INCLUDE SQLCA ;

/*----------------------------------------------------------------------------
Declare the SQL interface variables.
----------------------------------------------------------------------------*/

EXEC SQL BEGIN DECLARE SECTION ;
    short  sage;
    short  sid;
    char   sname[16];
EXEC SQL END DECLARE SECTION ;

/*----------------------------------------------------------------------------
Declare variables to be used in the following C program.
----------------------------------------------------------------------------*/

char msg[1025];
int rc;
int errcount;

/*----------------------------------------------------------------------------
This macro prints the message in the SQLCA if the return code is 0
and the SQLCODE is not 0.
----------------------------------------------------------------------------*/

#define PRINT_MESSAGE()                                               \
  {                                                                   \
      if (rc == 0 && sqlca.sqlcode != 0)                              \
      {                                                               \
        sqlaintp(msg, 1024, 0, &sqlca);                               \
        printf("%s\n",msg);                                           \
      }                                                               \
  }

/*----------------------------------------------------------------------------
This macro prints out all feilds in the SQLCA.
----------------------------------------------------------------------------*/

#define DUMP_SQLCA()                                                         \
  {                                                                          \
    printf("********************  DUMP OF SQLCA  ********************\n");   \
    printf("SQLCAID: %s\n", sqlca.sqlcaid);                                  \
    printf("SQLCABC: %d\n", sqlca.sqlcabc);                                  \
    printf("SQLCODE: %d\n", sqlca.sqlcode);                                  \
    printf("SQLERRML: %d\n", sqlca.sqlerrml);                                \
    printf("SQLERRMC: %s\n", sqlca.sqlerrmc);                                \
    printf("SQLERRP: %s\n", sqlca.sqlerrp);                                  \
    printf("SQLERRD[0]: %d\n", sqlca.sqlerrd[0]);                            \
    printf("SQLERRD[1]: %d\n", sqlca.sqlerrd[1]);                            \
    printf("SQLERRD[2]: %d\n", sqlca.sqlerrd[2]);                            \
    printf("SQLERRD[3]: %d\n", sqlca.sqlerrd[3]);                            \
    printf("SQLERRD[4]: %d\n", sqlca.sqlerrd[4]);                            \
    printf("SQLERRD[5]: %d\n", sqlca.sqlerrd[5]);                            \
    printf("SQLWARN: %s\n", sqlca.sqlwarn);                                  \
    printf("SQLSTATE: %s\n", sqlca.sqlstate);                                \
    printf("******************  END OF SQLCA DUMP  *******************\n");  \
  }

/*----------------------------------------------------------------------------
This macro prints the message in the SQLCA if one exists.  If the
return code is not 0 or the SQLCODE is not expected, an error occurred
and must be recorded.
----------------------------------------------------------------------------*/

#define CHECK_SQL(code,text_string,eExit)                             \
  {                                                                   \
    PRINT_MESSAGE();                                                  \
    if (rc != 0 || sqlca.sqlcode != code) {                           \
      printf("%s\n",text_string);                                     \
      printf("Expected code = %d\n",code);                            \
      if (rc == 0) {                                                  \
          DUMP_SQLCA();                                               \
      }                                                               \
      else printf("RC: %d\n",rc);                                     \
      errcount += 1;                                                  \
      if (eExit == EXIT) goto errorexit;                              \
    }                                                                 \
  }

/*----------------------------------------------------------------------------
The PROGRAM.
----------------------------------------------------------------------------*/

main (int argc, char *argv[])
{
    /* Grab the first command argument.  This is the SID. */
    if (argc > 1) {
        sid = atoi(argv[1]);
        printf("SID requested is %d.\n", sid);
    /* If there is no arguement, bail. */
    } else {
        printf("Which SID?\n");
        exit(0);
    }

        EXEC SQL CONNECT TO GO3421;
        CHECK_SQL(0, "Connect failed", EXIT);

        /* Find the name and age of sailor SID. */
        EXEC SQL SELECT SNAME, AGE into :sname, :sage
            FROM TECHSTU.SAILOR
            WHERE sid = :sid;

        CHECK_SQL(0, "The SELECT query failed.", EXIT);

        /* Report the age. */
        printf("Sailor %s's age is %d.\n", sname, sage);

        printf("Executed Successfuly\n") ;
        printf("Bye\n") ;

errorexit:
        EXEC SQL CONNECT RESET;
}
  
The instance of the table sailor at the time of writing is  
SNAME           SID         RATING AGE   
--------------- ----------- ------ ------
yuppy                    22      1     20
lubber                   31      1     25
guppy                    44      2     31
rusty                    58      3     47
So once you have a clean compile, if you ask for example
% sage 44
you should get the response
SID requested is 44.
Sailor guppy's age is 31.
Executed Successfuly
Bye
Do not forget to check out some of the other sample programs found in
  • ~db2v7c/sqllib/samples/c/ 

    • and
  • ~db2v7c/sqllib/samples/cpp/