EMBEDDED SQL IN C: GETTING STARTED

Frans Coenen

Liverpool University

Department of Computer Science

September 1992 (Revised May 1997 & March 1998)

Version III

Contents:

  1. Introduction.
  2. Syntax.
  3. SQLCA.
  4. DEClaration GENerator.
  5. The cursor.
  6. Example program.
  7. Final note on empty fields.

1. INTRODUCTION

Embedded SQL is an embedding of the database language SQL into a procedural programming language (C in this document) referred to as the host language.

2. SYNTAX

The syntax of a SQL database statement embedded in any host language is as follows:

(margin) EXEC SQL SQL_STATEMENT (terminator)

In C there is no specified margin, the terminator is ";".

2.1 An example program outline

Below is given a (very rough) outline of an example embedded SQL program which features the elements common to most embedded SQL applications. The program retrieves an employee's name where emp_number is equal to 10001 from a table called employees contained in a database called frans and then prints them.

/* Begin program */

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION
         host_name character_string(20)
         host_emp_number integer
EXEC SQL END DECLARE SECTION

EXEC SQL WHENEVER SQLERROR STOP

EXEC SQL CONNECT frans

/* Formulate query, something like: */

EXEC SQL SELECT name emp_number
         INTO host_name host_emp_number
         FROM employees
         WHERE emp_number = 10001

/* Print host_name and host_emp_number */

EXEC SQL DISCONNECT

/* End program */

All statements that begin EXEC SQL are embedded SQL database statements. The function of some of the above statements are obvious others require some additional explanation as follows:

  1. EXEC SQL INCLUDE SQLCA: Incorporates SQL's error handling mechanism (SQL Communications Area).
  2. DECLARE SECTION: Host variables must be declared to SQL prior to their use in any embedded SQL statements (the variable names used may be identical to those contained in the actual table).
  3. EXEC SQL WHENEVER SQLERROR STOP: An error handling mechanism must precede all executable embedded SQL statements in a program.
  4. EXEC SQL CONNECT personnel: Initiates access to the frans data base. A CONNECT statement must precede any references to a database.
  5. EXEC SQL SELECT: This is the familiar SQL select statement. It is followed by an INTO clause. This associates retrieved values with host variables in the program.
  6. EXEC SQL DISCONNECT: Severs the connection between the program and the database.

We are not limited to SELECT statements. We can embed any statement available in standard SQL. For example we can create a table as follows:

EXEC SQL CREATE TABLE employees
          (name c20,
          emp_number int)

This creates a table called employees that has two fields, name a character field with 20 characters and emp_number an integer field.

3. SQLCA

One of the results of issuing an INCLUDE SQLCA statement is the declaration of the SQLCA structure, which can be used for error handling. This structure is as follows:

typedef struct {
        char sqlcaid[8];
        long sqlcabc;
        long sqlcode;
        struct {
                short sqlerrml;
                char sqlerrmc [70];
        } sqlerrm;
        struct {
                char sqlwarn0;
                char sqlwarn1;
                char sqlwarn2;
                char sqlwarn3;
                char sqlwarn4;
                char sqlwarn5;
                char sqlwarn6;
                char sqlwarn7;
        } sqlwarn
        char sqlext[8];
} IISQLCA;
static IISQLCA sqlca = {0};

Many of the variables defined by the IISQLCA structure are not used. However the following should be noted:

  1. The SQLCA is initialised after the connect statement is issued. The fields sqlcaid and sqlcabc are initialised to the string "SQCLA" and the constant 136, respectively. These values do not change.
  2. The nested structure sqlerrm is a varying length character string consisting of two variables sqlerrml and sqlerrmc. If sqlerrml is set to 1 an error has been encountered, otherwise it is set to 0.
  3. sqlcode is an integer which is set as follows:
    • 0 = Statement executed successfully.
    • < 0 = An error has occurred. The value is the negative of the error number.
    • > 0 = Statement executed successfully but some exceptional condition has occurred, e.g. no data processed (empty field encountered).
    N.B.
    • sqlcode = 100: empty row encountered.
    • sqlcode = -40202: row containing empty fields encountered.
  4. The sqlerrd array is a set of six 4 byte integers. Only sqlerrd(3) is used to indicate the number of rows affected by an INSERT, UPDATE or DELETE statement.
  5. Variables sqlwarn0 to sqlwarn7 denote warnings.

Error handling with SQCLA can be done implicitly by using WHENEVER statements, or explicitly by checking the contents of the SQLCA fields sqlcode, sqlerrml and sqlwarn[3].

4. DCLGEN (DEClaration GENerator).

DCLGEN is a structure-generating utility that maps the columns of a database table into a structure that you can include in a variable declaration. DCLGEN is invoked thus: 

dclgen language dbname tablename filename structurename

where: