Contents

Overview
SQL statements recognized by Pro*C
Pro*C syntax

SQL
Preprocessor directives
Statement Labels

Host Variables

Variables
Indicator variables
Pointers
Structs
Arrays
Equivalencing between C types and Oracle types

Error Handling
C++ users
Demo Programs

Embedded SQL is a method to combine the computing power a high level language like C/C++ and the database manipulation capabilities of SQL. It allows you to execute any SQL statement from an application program.

The embedded SQL programs are compiled in two steps

1. The ProC precompiler recognizes the SQL statements from your embedded SQL code and replaces them with the 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
   
   
2. Now you can use a regular compiler cc/c++ to compile your code (You cannot use gcc/g++)

All the SQL statements need to start with EXEC SQL and end with a semicolon. You can place the SQL statements anywhere in the code, with the restriction that the declarative statements do not come after the executable statements (a C syntax violation)

The SQL statements allowed by ProC are (in alphabetic order)

1. ALTER
2. ALTER SESSION
3. ALTER SYSTEM
4. ANALYZE
5. AUDIT
6. COMMENT
7. COMMIT
8. CREATE
9. DELETE
10. DROP
11. EXPLAIN PLAN
12. GRANT
13. INSERT
14. LOCK
15. NOAUDIT
16. RENAME
17. REVOKE
18. ROLLBACK
19. SAVEPOINT
20. SELECT
21. SET ROLE
22. SET TRANSACTION
23. TABLE
24. TRUNCATE
25. UPDATE

All SQL stetements are embedded as

EXEC SQL .... ;

Example

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

The preprocessor directives that can be used with ProC are

1. #include
2. #if...

You cannot use macros with ProC The following code is invalid

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

Statement labels

You can connect C labels with SQL as in

EXEC SQL WHENEVER SQLERROR GOTO error_in_SQL;
...
error_in_SQL:
      /* Do something */

We will come to what WHENEVER means a little later.

Host variables are the key to communication between the host program and Oracle. A host variable can be any arbitrary C expression that resolves to an lvalue.

You declare host variables according to the rules of C, as you declare the regular C variables. The C datatypes that can be used with Oracle are

1. char
2. char[n]
3. int
4. short
5. long
6. float
7. double
8. VARCHAR[n] - This is a psuedo-type recognized by the ProC precompiler. It is used to represent blank-padded, variable length strings. ProC will convert it into a struct with a 2-byte length field and a n-byte character array.

You cannot use register storage-class specifier for the host variables.

Referencing host variables in SQL

A host variable must be prefixed with a colon (:) in SQL statements but must not be prefixed with a colon in C statements. The example above illustrated the use of host variables.

Restriction : A host variable needs to resolve to an address, so function calls and numeric expressions cannot be used for host variables. The following code is invalid:

int get_dept();
...
main()
{
   int x;
   ...
   EXEC SQL INSERT INTO emp (empno, ename, deptno) 
            VALUES (:x * x, 'BIG SHOT', :get_dept());
       /* All 3 values are invalid as host variables,
          the first two are expressions without l-values and
          the third is a function call */
   ...   
}

Pointers

You define pointer variables following the normal C practice, and in SQL statements prefix them with a colon as in

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

You can associate every host variable with an optional indicator variable. An indicator variable must be defined as a 2-byte int (using the type short) and, in SQL statements, must be prefixed by a colon and must immediately follow its host variable. Or you may use the keyword INDICATOR in between the host variable and indicator variable. Examples are

1. EXEC SQL SELECT xyz INTO :host_var :corresponding_indicator_var FROM ...
2. EXEC SQL SELECT xyz INTO :host_var INDICATOR :corresponding_indicator_var FROM ...

Using indicator variables

You use indicator variables in the VALUES and SET clause of INSERT or UPDATE  statements to assign nulls to input host variables and in the INTO clause of SELECT statements to detect nulls or truncated values in output host variables.

• Input
  The values the program can assign to an indicator variable have the following meanings:
• -1    Oracle will assign a null to the column
• >=0  Oracle will assign the value of the host variable to the column.
• Output
• -1     The column value is null
• 0         Oracle assigned an intact column value to the host variable
• >0      Oracle assigned a truncated column value to the host variable.
• -2      Oracle assigned a truncated column variable to the host variable , but the original column value could not be determined.

Structures can be used as host variables as in the following example

typedef struct {
   char name[21]; /* one greater than the column length */
   int SSN;
} Emp;
...

Emp       bigshot;
EXEC SQL INSERT INTO emp (ename, eSSN)
         VALUES (:bigshot) ;

Arrays of host variables can be used as in the following

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

which will insert all the 50 tuples in one go.

Arrays can only be single dimensional. The example char name[50][11] would seem to contradict that rule. However  ProC considers name a one dimensional array of strings rather than a two dimensional array of characters. Also you can have an array of structs.

When using arrays to store the results of a query, in case the size of the array is smaller than the number of tuples outputted by the query, the first N tuples will be entered in the host array.

At precompile time, a default Oracle external datatype is assigned to each host variable. Datatype equivalencing allows you to override this default equivalencing and lets you control the way Oracle interprets the inputs and formats the output data.The equivalencing can be done on a variable-by-variable basis.

• VAR : The VAR statement is used to override the equivalencing of host variables.
  The syntax is

  EXEC SQL VAR host_variable IS type_name [  (length)  ] ;
  
  

• TYPE : This is used to equivalence user-defined datatypes (the ones that you define using typedef) to Oracle external datatypes. The syntax is

  EXEC SQL TYPE user_type ID type_name [ (length) ] [REFERENCE] ;
  
  

REFERENCE clause

SQLCA - SQL Communications Area is used to detect errors and status changes in your program. This structure contains components that are filled in at runtime after the SQL statement is processed by Oracle.

To use it you need to include the file sqlca.h , using the #include command. In case you need to include the sqlca at many places you need to undefine the macro SQLCA by using #undef SQLCA

Oracle updates the sqlca after every executable SQL statement. By checking the return codes stored in the sqlca your program can find the status in two ways

• explicit checking of sqlca components
• implicit checking using WHENEVER

Header file

The relevant chunk of the header file 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];
};
...

The fields in sqlca have the following meaning

Text of error messages

The sqlca can accomodate error messages upto 70 characters long. To get the full text of longer (or nested) error messages, you need the sqlglm function.

void sqlglm(char *message, size_t *buffer_size, size_t *message_length)

where:

The maximum length of an Oracle error message is 512 bytes including the error code, nested messages, and message inserts as table and column names.

WHENEVER statement

This statement allows you to do automatic checking and error handling. The syntax is

EXEC SQL WHENEVER <condition> <action> ;

Conditions

• SQLWARNING - sqlwarn[0] is set because Oracle returned a warning
• SQLERROR - Oracle returned an error.
• NOT FOUND - Oracle could not find a row that matches your query

Actions

• CONTINUE - Program will try to continue to run with the next statement if possible.
• DO - Program transfers control to an error handling function
• GOTO lab - Branch to the label lab
• STOP - Program stops and uncommitted work is rolled back.

Examples

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

Use example

/* Code to find student name given his id */
int 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 studentrec
            WHERE studentid = :id ;
   printf("Name of student is %s\n", st_name);
   continue;
notfound:
   printf("No record exists for id %d\n", id);
} 

C++ users

To get the precompiler to generate appropriate C++ code you need to be aware of the following considerations

• 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 takes the following values with the following meanings
• 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
  All preprocessor directives are understood, however all host variables need to be declared inside a declare section.
• PARSE=FULL
  The precompiler C parser runs on your code, preprocessor directives are understood and host variables can be declared anywhere (This is the default when CODE is anything other than CPP, however it is an error to specify PARSE=FULL with CODE=CPP)

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

EXEC SQL BEGIN DECLARE SECTION;
// All the 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
  ProC searches for header files like stdio.h in standard locations. However C++ has its own header files, such as iostream.h, situated elsewhere. So you need to use the SYS_INCLUDE option to specify the directory paths that ProC will search for header files. For example
  SYS_INCLUDE=(/usr/lang/SC2.1.1/include)
  

The demo programs are available in the directory /usr/class/cs145/code/proc named sample*.pc (for C users) and cppdemo*.pc (for C++ users). pc is an extension for pro*C code. Please don't copy the files manually , since there are a couple of customizations to do. So, to download the sample programs and customize them, just:

1. Make sure that you have run source cora.env
2. Run load_samples <db_username> <db_passwd> <sample_dir>, (e.g., load_samples scott@cs tiger cs145_samples)
3. Run cd <sample_dir>
4. Run make samples (or make cppsamples for C++) to compile all the samples.
5. Run the samples.
6. Code your own embedded SQL programs, compile and run them.

Step (1) will create a new directory as specified in <sample_dir> and copy the sample files to the directory. It will also change the user name and passwd in the samples to be yours, so that you don't have to type in the oracle username and password everytime when running a sample. However, sample1 and cppdemo1 do provide an interface for user to input the username and password, in case you'd like to learn how to do it.

If you happen to make any mistake with the entered username or passwd in Step (1), just run clean_samples <db_username> <db_passwd> <sample_dir> to uninstall the sample files and redo Step (1).

For step (4), you can also compile each sample seperately. For example, make sample1 compiles sample1.pc seperately. What it does is to generate executables for sample1 using the Makefile. The compilation proceeds in 2 phases:

1. proc iname=sample1.pc
   which will convert the embedded sql code to the corresponding library calls.
2. cc (A number of flags) sample1.c
   which will generate the executable sample1

To compile your own files, just change a few variables in Makefile -- add your program name foo to variable SAMPLES and the source code name foo.pc to variable SAMPLE_SRC. Then, do make foo after foo.pc is ready. foo.pc will be precompiled to foo.c and then compiled to foo, the executable. C++ users will need to add their program name to CPPSAMPLES instead of SAMPLES, and source filename to CPPSAMPLE_SRC instead of SAMPLE_SRC.

The sample programs operate on the following database:

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)          )

These tables are created automatically after you run load_samples in Step (1). A few tuples are inserted. You may like to browse the tables before running the samples on them. You can also play with them as you like (e.g., adding or changes tuples).

You should take a look at a sample's source code before running it. The comments at the top of the source code tell what the sample program does. For example, what sample1 does is to take an employees EMPNO and get the name, salary and commision for that employee from the table EMP.

You are supposed to look at the source code of the samples and learn the following.

• How to connect to Oracle.
• Embedding of SQL in the C program
• Interaction between host language and SQL using host variables
• Use of WHENEVER to take a different action on error messages
• Use of indicator variables to detect nulls in the output.

Then, you can use these techniques to code your own PDA interface program. and have fun!