Embedded SQL in C (for DB2 V7.1 on CDF)

          by A. Shrufi, A. Bonner and D. Rafiei

      Minor modifications by A. Bonner, 26 March 2001

          Modified by Kevin Brewer, March 2006

*************************************************************************

This section covers the basics of how to embed SQL statements in a
host the C programming languages.  Basically, any SQL statement that
can be issued from the interactive mode (e.g. DDL statements such as
create, drop or alter, as well as DML statements such as select,
update, insert) are supported in embedded SQL.  There are two types of
embedded SQL statements: static and dynamic.  In this sequel, we only
cover static SQL which should be sufficient for your work in this
course.

(I) Compiling your application:
An application program written in embedded SQL must be precompiled
before it can be compiled.  The precompilation phase converts all
embedded SQL statements into C statements involving special db2
subroutines and data structures.  The resulting C program is compiled
using the C compiler and a special db2 header file, and then linked
using a special db2 subroutine library.

Precompiling an application program is done from db2, and involves two
steps: first connect to the database, then precompile, and finally
disconnect.  This can be done from Unix, as follows:

   % db2 connect to mydatabase
   % db2 prep myprogram.sqc
   % db2 connect reset

where "% " represents the Unix prompt, "mydatabase" is the name of
your database, and "myprogram.sqc" is the name of the file containing
your embedded SQL program.  In DB2, all C programs with embedded SQL
must be stored in files whose name ends in ".sqc". Note that the tables
must exist in your database for the precompiler to work properly.

The result of precompiling the file "myprogram.sqc" is a file of C
code called "myprogram.c".  This file must now be compiled using the C
compiler in conjunction with special flags that identify special db2
header and library files. Note that due to incompatibilities with the 
current cdf version of gcc, you must use an older version of gcc, 2.95
This is done with the following Unix-level command (which should all be 
typed on one line):

   % gcc-2.95 myprogram.c -Wall
                     -I/db2/db2inst1/sqllib/include
                     -L/db2/db2inst1/sqllib/lib
                     -ldb2
                     -Wl,-rpath,/usr/IBMdb2/V7.1/lib

or with the following command (the same directories under a different
symbolic name):

   % gcc-2.95 myprogram.c -Wall
                     -I/usr/IBMdb2/V7.1/include
                     -L/usr/IBMdb2/V7.1/lib
                     -ldb2
                     -Wl,-rpath,/usr/IBMdb2/V7.1/lib

Here, "include" is the db2 header file, and "lib" is the db2
subroutine library.  (Type "man gcc" to unix for a description of the
various flags.)  The result of this compilation is a new file called
a.out, which you can now execute, as follows:

   % a.out

(II) A Look at an Embedded SQL application:
Again, the easiest way to learn how to write application in embedded
SQL is to see an example application. In the program listing shown
below, please pay attention to the line numbers next to some of the
lines in the second application code. These numbers will be referred 
in the explanations that follow this listing:

^L
/*****          Sample Application program  #1                  *****/
/********************************************************************
* SAMPLE PROGRAM: example1.sqc
*
* PURPOSE: This sample program demonstrates the use of embedded SQL
*          in DB2. The program makes use of CURSOR to manage the
*          query output. Assuming the following table in our database,
*          the program retrieves all video titles.
*
*    create table video(video_id int not null, \
*                  video_title varchar(30), \
*                  director varchar(20), \
*                  primary key(video_id))
*
********************************************************************/

#include <stdio.h>
#include <stlib.h>
#include <string.h>


/* sqlca: is the sql communications area. All error codes
   are returned from db2 in that structure which is filled
   each time an interaction with db2 takes place.

        *************************************
        * No error :    sqlca.sqlcode = 0   *
        * SQL warning : sqlca.sqlcode > 0   *
        * SQL error :   sqlca.sqlcode < 0   *
        * not found :   sqlca.sqlcode = 100 *
        *************************************
*/

EXEC SQL INCLUDE SQLCA;          /* SQL communication area structure */

EXEC SQL BEGIN DECLARE SECTION;  /* declare host variables    */
 char db_name[8];                /* database name             */
 char video_title[30];           /* title of the video        */
 short video_id;                 /* serial number             */
 char director[20];              /* director name             */
EXEC SQL END DECLARE SECTION;

void main() {

strcpy(db_name, "csc2509h");

/* C variables are preceded by a colon when they are passed to DB2 */

EXEC SQL CONNECT TO :db_name;

if (sqlca.sqlcode != 0) {
        printf("Connect failed!: reason %ld\n", sqlca.sqlcode);
        exit(1);
}

/* cursor declaration. Have to declare a cursor each time you
   want tuples back from db2
*/

EXEC SQL DECLARE c1 CURSOR FOR
        SELECT video_title
        FROM video;

/* you have to open the cursor in order to get tuples back */

EXEC SQL OPEN c1;

do {

  /* fetch tuples from the cursor. This will execute the statement
     the cursor implements and will return the results */

  EXEC SQL FETCH c1 into :video_title;
  if (SQLCODE != 0) break;      /* SQLCODE refers to sqlca.sqlcode */

  /* host variables should have ':' prefix when they are used in DB2 commands */

  printf("%s\n",video_title);

} while (1);
EXEC SQL CLOSE c1;
EXEC SQL CONNECT RESET;
}




/*****          Sample Application program  #2                  *****/
/********************************************************************
* SAMPLE PROGRAM: example2.sqc
*
* PURPOSE: This sample program demonstrates the use of WHENEVER statement
	   in embedded SQL in DB2. The program again makes use of CURSOR 
*	   which is processed using static SQL.  Assuming the table 
*	   employees(name,age,sal) is already defined in our database, 
*	   the program will obtain the names of all employee tuples whose 
*	   last name is Smith and are 45 years old.
*
*          The program also illustrates how to insert a tuple into
*          the table employees.
*
********************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>        /*   (0)   */

EXEC SQL INCLUDE SQLCA;    /*   (1)   */

int main(void)
{

   EXEC SQL BEGIN DECLARE SECTION;      /*  (2)    */
        char   name[20]; 
        long   age;
        double sal;
   EXEC SQL END DECLARE SECTION;

   printf( "sample C program: embedded SQL in DB2\n" );

   EXEC SQL CONNECT TO csc2509h IN SHARE MODE;  /*   (3)   */

   if ( SQLCODE != 0 ) {
      printf( "CONNECT TO Error:  SQLCODE = %ld\n", SQLCODE );
      exit(1);
   }

   EXEC SQL WHENEVER SQLERROR  GO TO ext;  /*  (4)  */
   EXEC SQL WHENEVER NOT FOUND GO TO cls;  

   EXEC SQL DECLARE c1 CURSOR FOR          /*  (5)  */
            SELECT name, age, sal FROM employees WHERE name='Smith'
            and age = 45;

   /* note that in the above, we can alternatively copy the string value
      "Smith" into the host variable name, and use that in the SQL
      select statement, e.g. select * from employees where name = :name */
   

   printf( "name \t\t  age \t\t  salary \n");
   printf( "-------------------------------------------------------\n");

   EXEC SQL OPEN c1;                      /* (6)  */
   do {
      EXEC SQL FETCH c1 INTO :name, :age, :sal;  
      printf( "%s \t %d \t %10.2f \n", name, age, sal);
   } while (1);

cls:
   EXEC SQL CLOSE c1;                    /* (7) */

   strcpy(name,"Moses");
   age = 50;
   sal = 70000;

   EXEC SQL INSERT INTO employees VALUES(:name, :age, :sal);  /* (8)  */

   if ( SQLCODE != 0 )
      printf( "UPDATE error:  SQLCODE = %ld\n", SQLCODE );
   else 
      printf( "Insert was successful!\n" );
   
ext:                                             /*     (9)     */
   if ( SQLCODE != 0 )
      printf( "Error:  SQLCODE = %ld.\n", SQLCODE );

   EXEC SQL WHENEVER SQLERROR CONTINUE;   /*  (9)  */

   EXEC SQL CONNECT RESET;                /*  (10)  */
   if ( SQLCODE != 0 ) {
      printf( "CONNECT RESET Error:  SQLCODE = %ld\n", SQLCODE );
      exit(1);
   }
   return 0;
}




Explanation:

0) First you have to include header files that are specific to DB2.
The ones included in the above listing should be sufficient for our
purposes.

1) Include the SQLCA. The include SQLCA statement defines and declares
the SQLCA structure and defines the SQLCODE field which is updated
with the error information.

2) Declare host variables. The SQL BEGIN DECLARE SECTION and END
DECLARE SECTION statements delimit the host variable declarations.
These are variables that can be referenced in the SQL statements. Host
variables are used to pass data to the data manager or to hold data
returned by the database manager. Note the use of long instead of int
datatype, and keep in mind that you can not define C structures in the
SQL DECLARE section. Also use the double datatype to represent DB2
DECIMAL datatype.

3) Connect to database.  The program connects to the csc2509h database.

4) Setup SQL statement error handling. The WHENEVER statement
specifies how SQL errors are to be handled for the rest of the
program. This statement makes the program transfer control to the
specified label whenever a an SQLERROR is returned. Also it makes the
program transfer control to the lable cls whenever there are no more
rows in the cursor.

5) Declare Cursor. The SQL DECLARE c1 CURSOR FOR statement declares
and associates the cursor c1 to a query. The query identifies the rows
that the application retrieves using the FETCH statement.

6) Open the Cursor. The cursor c1 is opened causing the database
manager to perform the query and build the result table. The cursor is
positioned before the first row.

6) Retrieve a row. The FETCH statement positions the cursor at the
next row and moves the contents of the row into the host variables.
This row becomes the current row.

7) Close the Cursor. The CLOSE c1 statement close the cursor c1. This
statement is executed as a result of transferring the control to the
label cls: when there are no more rows to be FETCHed.

8) Inserting into the table. This statement simply illustrated how to
insert a row into the employees table. Note that values of the row
fields are filled in from the host variables (name, age and sal).

9) Process errors.  This label was specified in the WHENEVER
statement. If any SQL statement do not execute successfully (SQLCODE
is negative), control is transfered to this statement.

10) Turn off SQL error handling. At this point we want to turn off the
error handling mechanism and simply reset the connection. If we do not
do this, we may fall into an infinite loop if any other errors crop
up.

11) Disconnects the database by executing the CONNECT RESET statement.


One final thing to note about DB2 embedded SQL.
- Programs must prepare to receive NULL values whenever you have
columns in your tables that may contain NULL values.  In DB2 embedded
SQL, you can use indicator variables to test if the value returned
from a FETCH statement has NULL fields or not.  For example,

              EXEC SQL FETCH c1 into age :ageIND;
              if (ageIND < 0)
                printf("age is NULL \n");

Indicator variables such as ageIND should be declared as short
datatype in C.