Resources

• Database Systems: The Complete Book by Hector Garcia, Jeff Ullman, and Jennifer Widom.

• A First Course in Database Systems by Jeff Ullman and Jennifer Widom.

• Gradiance SQL Tutorial.

Introduction to Pro*C
Embedded SQL 

---

• Overview
• Pro*C Syntax
• SQL
• Preprocessor Directives
• Statement Labels
• Host Variables
• Basics
• Pointers
• Structures
• Arrays
• Indicator Variables
• Datatype Equivalencing
• Dynamic SQL
• Transactions
• Error Handling
• SQLCA
• WHENEVER Statement
• Demo Programs
• C++ Users
• List of Embedded SQL Statements Supported by Pro*C

---

Overview

A Pro*C program is compiled in two steps.  First, the Pro*C precompiler recognizes the SQL statements embedded in the program, and replaces them with appropriate calls to the functions in the SQL runtime library. The output is pure C/C++ code with all the pure C/C++ portions intact. Then, a regular C/C++ compiler is used to compile the code and produces the executable.  For details, see the section on Demo Programs.

---

Pro*C Syntax

---

SQL

    {
        int a;
        /* ... */
        EXEC SQL SELECT salary INTO :a
                 FROM Employee
                 WHERE SSN=876543210;
        /* ... */
        printf("The salary is %d\n", a);
        /* ... */
    }

---

Preprocessor Directives

    #define THE_SSN 876543210
    /* ... */
    EXEC SQL SELECT salary INTO :a
             FROM Employee
             WHERE SSN = THE_SSN;    /* INVALID */

---

Statement Labels

    EXEC SQL WHENEVER SQLERROR GOTO error_in_SQL;
    /* ... */
error_in_SQL:
    /* do error handling */

---

Host Variables

---

Basics

• char
• char[n]
• int
• short
• long
• float
• double
• VARCHAR[n] - This is a psuedo-type recognized by the Pro*C precompiler. It is used to represent blank-padded, variable-length strings. Pro*C precompiler will convert it into a structure with a 2-byte length field and a n-byte character array.

A host variable reference must be prefixed with a colon ":" in SQL statements, but should not be prefixed with a colon in C statements. When specifying a string literal via a host variable, the single quotes must be omitted; Pro*C understands that you are specifying a string based on the declared type of the host variable. C function calls and most of the pointer arithmetic expressions cannot be used as host variable references even though they may indeed resolve to lvalues. The following code illustrates both legal and illegal host variable references:

int deptnos[3] = { 000, 111, 222 };
int get_deptno() { return deptnos[2]; }
int *get_deptnoptr() { return &(deptnos[2]); }
int main() {
    int x; char *y; int z;
    /* ... */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x, :y, :z);         /* LEGAL */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x + 1,              /* LEGAL: the reference is to x */
               'Big Shot',          /* LEGAL: but not really a host var */
               :deptnos[2]);        /* LEGAL: array element is fine */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x, :y,
               :(*(deptnos+2)));    /* ILLEGAL: although it has an
lvalue */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x, :y,
               :get_deptno());      /* ILLEGAL: no function calls */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x, :y,
               :(*get_depnoptr())); /* ILLEGAL: although it has an lvalue */
    /* ... */
}

---

Pointers

    int *x;
    /* ... */
    EXEC SQL SELECT xyz INTO :x FROM ...;

---

Structures

    typedef struct {
        char name[21];    /* one greater than column length; for '\0' */
        int SSN;
    } Emp;
    /* ... */
    Emp bigshot;
    /* ... */
    EXEC SQL INSERT INTO emp (ename, eSSN)
        VALUES (:bigshot);

---

Arrays

    int emp_number[50];
    char name[50][11];
    /* ... */
    EXEC SQL INSERT INTO emp(emp_number, name)
        VALUES (:emp_number, :emp_name);

Arrays can only be single dimensional. The example char name[50][11] would seem to contradict that rule. However, Pro*C actually considers name a one-dimensional array of strings rather than a two-dimensional array of characters. You can also have arrays of structures.

When using arrays to store the results of a query, if the size of the host array (say n) is smaller than the actual number of tuples returned by the query, then only the first n result tuples will be entered into the host array.

---

Indicator Variables

    short indicator_var;
    EXEC SQL SELECT xyz INTO :host_var:indicator_var
        FROM ...;
    /* ... */
    EXEC SQL INSERT INTO R
        VALUES(:host_var INDICATOR :indicator_var, ...);

You can also use indicator variables in the VALUES and SET clause of an INSERT or UPDATE  statement to assign NULL's to input host variables. The values your program can assign to an indicator variable have the following meanings:
 

-1	Oracle will assign a NULL to the column, ignoring the value of the host variable.
>=0	Oracle will assign the value of the host variable to the column.

---

Datatype Equivalencing

The equivalencing can be done on a variable-by-variable basis using the VAR statement. The syntax is:

    EXEC SQL VAR <host_var> IS <type_name> [ (<length>) ];

    char emp_name[21];
    EXEC SQL VAR emp_name IS STRING(21);

You can also equivalence user-defined datatypes to Oracle external datatypes using the TYPE statement. The syntax is:

    EXEC SQL TYPE <user_type> IS <type_name> [ (<length>) ] [REFERENCE];

    typedef unsigned char *my_raw;
    EXEC SQL TYPE my_raw IS VARRAW(4000) REFERENCE;
    my_raw buffer;
    /* ... */
    buffer = malloc(4004);

---

Dynamic SQL

    char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)";
    EXEC SQL PREPARE q FROM :s;
    EXEC SQL EXECUTE q;

    char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)";
    EXEC SQL EXECUTE IMMEDIATE :s;

 If your program exits without calling EXEC SQL COMMIT, all database changes will be discarded.

---

SQLCA

To use SQLCA you need to include the header file sqlca.h using the #include directive. In case you need to include sqlca.h at many places, you need to first undefine the macro SQLCA with #undef SQLCA. The relevant chunk of sqlca.h follows:

#ifndef SQLCA
#define SQLCA 1

struct sqlca {
    /* ub1 */ char sqlcaid[8];
    /* b4 */ long sqlabc;
    /* b4 */ long sqlcode;
    struct {
        /* ub2 */ unsigned short sqlerrml;
        /* ub1 */ char sqlerrmc[70];
    } sqlerrm;
    /* ub1 */ char sqlerrp[8];
    /* b4 */ long sqlerrd[6];
    /* ub1 */ char sqlwarn[8];
    /* ub1 */ char sqlext[8];
};
/* ... */

SQLCA can only accommodate error messages up to 70 characters long in its sqlerrm component. To get the full text of longer (or nested) error messages, you need the sqlglm() function:

    void sqlglm(char *msg_buf, size_t *buf_size, size_t *msg_length);

---

WHENEVER Statement

    EXEC SQL WHENEVER <condition> <action>;

<condition> can be any of the following:

• SQLWARNING - sqlwarn[0] is set because Oracle returned a warning
• SQLERROR - sqlcode is negative because Oracle returned an error
• NOT FOUND - sqlcode is positive because Oracle could not find a row that meets your WHERE condition, or a SELECT INTO or FETCH returned no rows

• CONTINUE - Program will try to continue to run with the next statement if possible
• DO - Program transfers control to an error handling function
• GOTO <label> - Program branches to a labeled statement
• STOP - Program exits with an exit() call, and uncommitted work is rolled back

    EXEC SQL WHENEVER SQLWARNING DO print_warning_msg();
    EXEC SQL WHENEVER NOT FOUND GOTO handle_empty;

/* code to find student name given id */
/* ... */
for (;;) {
    printf("Give student id number : ");
    scanf("%d", &id);
    EXEC SQL WHENEVER NOT FOUND GOTO notfound;
    EXEC SQL SELECT studentname INTO :st_name
             FROM   student
             WHERE  studentid = :id;
    printf("Name of student is %s.\n", st_name);
    continue;
notfound:
    printf("No record exists for id %d!\n", id);
}
/* ... */

EXEC SQL WHENEVER NOT FOUND DO break;

All SQL statements that occur after this line in the file would be affected.  Make sure you use the following line to cancel the effect of WHENEVER when it is no longer needed (such as after your loop):

EXEC SQL WHENEVER NOT FOUND CONTINUE;

---

Demo Programs

Several demo programs are available in /afs/ir/class/cs145/code/proc on the leland system. They are named sample*.pc (for C users) and cppdemo*.pc (for C++ users). ".pc" is the extension for Pro*C code. Do not copy these files manually, since there are a couple of customizations to do. To download and customize the demo programs, follow the instructions below:

1. Make sure that you have run source /afs/ir/class/cs145/all.env
2. In your home directory, run load_samples <db_username> <db_passwd> <sample_dir>, where <sample_dir> is the name of the directory where you wish to put demo programs (e.g., load_samples sally etaoinshrdlu cs145_samples)
3. cd <sample_dir>
4. Run make samples (or make cppsamples for C++) to compile all demo programs

If you happen to make any mistake when entering username or password in Step (2), just run clean_samples <db_username> <db_passwd> <sample_dir> in your home directory, and then repeat Steps (2) to (4).

For Step (4), you can also compile each sample program separately. For example, make sample1 compiles sample1.pc alone. The compilation process actually has two phases:

1. proc iname=sample1.pc

converts the embedded SQL code to corresponding library calls and outputs sample1.c
2. cc <a_number_of_flags_here> sample1.c

generates the executable sample1

The demo programs operate on the following tables:

CREATE TABLE DEPT
    (DEPTNO    NUMBER(2) NOT NULL,
     DNAME     VARCHAR2(14),
     LOC       VARCHAR2(13));

CREATE TABLE EMP
    (EMPNO     NUMBER(4) NOT NULL,
     ENAME     VARCHAR2(10),
     JOB       VARCHAR2(9),
     MGR       NUMBER(4),
     HIREDATE  DATE,
     SAL       NUMBER(7, 2),
     COMM      NUMBER(7, 2),
     DEPTNO    NUMBER(2));

CREATE TABLE PAY1
    (ENAME     VARCHAR2(10),
     SAL       NUMBER(7, 2));

CREATE TABLE PAY2
    (ENAME     VARCHAR2(10),
     SAL       NUMBER(7, 2));

You should take a look at the sample source code before running it. The comments at the top describe what the program does. For example, sample1 takes an employee's EMPNO and retrieve the name, salary, and commission for that employee from the table EMP.

You are supposed to study the sample source code and learn the following:

• How to connect to Oracle from the host program
• How to embed SQL in C/C++
• How to use cursors
• How to use host variables to communicate with the database
• How to use WHENEVER to take different actions on error messages.
• How to use indicator variables to detect NULL's in the output

---

C++ Users

• Code emission by precompiler. To get C++ code, you need to set the option CODE=CPP while executing proc. C users need not worry about this option; the default caters to their needs.

• Parsing capability. The PARSE option of proc may take the following values:

• PARSE=NONE. C preprocessor directives are understood only inside a declare section, and all host variables need to be declared inside a declare section.
• PARSE=PARTIAL. C preprocessor directives are understood; however, all host variables need to be declared inside a declare section.
• PARSE=FULL. C preprocessor directives are understood and host variables can be declared anywhere. This is the default when CODE is anything other than CPP; it is an error to specify PARSE=FULL with CODE=CPP.

So, C++ users must specify PARSE=NONE or PARSE=PARTIAL. They therefore lose the freedom to declare host variables anywhere in the code. Rather, the host variables must be encapsulated in declare sections as follows:

    EXEC SQL BEGIN DECLARE SECTION;
        // declarations...
    EXEC SQL END DECLARE SECTION;

You need to follow this routine for declaring the host and indicator variables at all the places you do so.

• File extension. You need to specify the option CPP_SUFFIX=cc or CPP_SUFFIX=C.

• Location of header files. By default, proc searches for header files like stdio.h in standard locations. However, C++ has its own header files, such as iostream.h, located elsewhere. So you need to use the SYS_INCLUDE option to specify the paths that proc should search for header files.

---

List of Embedded SQL Statements Supported by Pro*C