Embedded SQL Pro C
 
 

Embedded SQL (Pro*C)


Programs written in Pro*C and which include SQL and/or PL/SQL statements are precompiled into regular C programs using a precompiler that typically comes with the database management software (precompiler package). In order to make SQL and PL/SQL statements in a Proc*C program (having the su
x .pc) recognizable by the precompiler, they are always preceded by the keywords EXEC SQL and end with a semicolon \;". The Pro*C precompiler replaces such statements with appropriate calls to functions implemented in the SQL runtime library. The resulting C program then can be compiled and linked using a normal C compiler like any other C program. The linker includes the appropriate Oracle speci c libraries.

As it is the case for PL/SQL blocks, also the rst part of a Pro*C program has a declare section. In a Pro*C program, in a declare section so-called host variables are speci ed. Host variables are the key to the communication between the host program and the database. Declarations of host variables can be placed wherever normal C variable declarations can be placed. Host variables are declared according to the C syntax. Host variables can be of the following data types:
char <Name> single character
char <Name>[n] array ofn characters
int integer
float floating point
VARCHAR<Name>[n] variable length strings
VARCHAR2 is converted by the Pro*C precompiler into a structure with an n-byte characterarray and a 2-bytes length eld. The declaration of host variables occurs in a declare section having the following pattern:

EXEC SQL BEGIN DECLARE SECTION
<Declaration of host variables>
/* e.g., VARCHAR userid[20]; */
/* e.g., char test ok; */
EXEC SQL END DECLARE SECTION

In a Pro*C program at most one such a declare section is allowed. The declaration of cursors and exceptions occurs outside of such a declare section for host variables. In a Pro*C program host variables referenced in SQL and PL/SQL statements must be pre xed with a colon ":".Note that it is not possible to use C function calls and most of the pointer expressions as host variable references.

In addition to host language variables that are needed to pass data between the database and C program (and vice versa), one needs to provide some status variables containing program runtime information. The variables are used to pass status information concerning the database access to the application program so that certain events can be handled in the program properly. The structure containing the status variables is called SQL Communication Area or SQLCA,
for short, and has to be included after the declare section usingthe statement
EXEC SQL INCLUDE SQLCA.H
In the variables de ned in this structure, information about error messages as well as program status information is maintained:

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];
};

Components of this structure can be accessed and veri ed during runtime, and appropriate handling routines (e.g., exception handling) can be executed to ensure a correct behavior of the application program. If at the end of the program the variable sqlcode contains a 0, then theexecution of the program has been successful, otherwise an error occurred.

There are two ways to check the status of your program after executable SQL statements which may result in an error or warning: (1) either by explicitly checking respective components of the SQLCA structure, or (2) by doing automatic error checking and handling using the WHENEVER statement. The complete syntax of this statement is
EXEC SQL WHENEVER <condition> <action>;
By using this command, the program then automatically checks the SQLCA for <condition> and executes the given <action>. <condition> can be one of the following:

  • SQLERROR: sqlcode has a negative value, that is, an error occurred
  • SQLWARNING: In this case sqlwarn[0] is set due to a warning
  • NOT FOUND: sqlcode has a positive value, meaning that no row was found that satis es the where condition, or a select into or fetch statement returned no rows <action> can be
  • STOP: the program exits with an exit() call, and all SQL statements that have not been committed so far are rolled back
  • CONTINUE: if possible, the program tries to continue with the statement following the error resulting statement
  • DO <function>: the program transfers processing to an error handling function named <function>
  • GOTO <label>: program execution branches to a labeled statement (see example)

At the beginning of Pro*C program, more precisely, the execution of embedded SQL or PL/SQL statements, one has to connect to the database using a valid Oracle account and password. Connecting to the database occurs trough the embedded SQL statement
EXEC SQL CONNECT :<Account> IDENTIFIED BY :<Password>.
Both <Account> and <Password> are host variables of the type VARCHAR and must be speci ed and handled respectively. <Account> and <Password> can be speci ed in the Pro*C program, but can also be entered at program runtime using, e.g., the C function scanf.

Before a program is terminated by the c exit function and if no error occurred, database modi cations through embedded insert, update, and delete statements must be committed. This is done by using the embedded SQL statement
EXEC SQL COMMIT WORK RELEASE;
If a program error occurred and previous non-committed database modi cations need to be undone, the embedded SQL statement
EXEC SQL ROLLBACK WORK RELEASE;
has to be speci ed in the respective error handling routine of the Pro*C program.

The following Pro*C program connects to the database using the database account scott/tiger. The database contains information about employees and departments (see the previous exam- ples used in this tutorial). The user has to enter a salary which then is used to retrieve all employees (from the relation EMP) who earn more than the given minimum salary. Retrieving and processing individual result tuples occurs through using a PL/SQL cursor in a C while-loop.

  /* Declarations */
  #include <stdio.h>
  #include <string.h>
  #include <stdlib.h>

  /* Declare section for host variables */
  EXEC SQL BEGIN DECLARE SECTION;
     VARCHAR userid[20];
     VARCHAR passwd[20];
     int empno;
     VARCHAR ename[15];
     float sal;
     float min_sal;
  EXEC SQL END DECLARE SECTION;

  /* Load SQL Communication Area */
  EXEC SQL INCLUDE SQLCA.H;

  main()  /* Main program */
  {  int retval;
     /* Catch errors */
     EXEC SQL WHENEVER SQLERROR GOTO error;

     /* Connect to Oracle as SCOTT/TIGER; both are host variables      */
     /* of type VARCHAR; Account and Password are specified explicitly */
     strcpy(userid.arr,"SCOTT");    /* userid.arr := "SCOTT" */
     userid.len=strlen(userid.arr); /* uid.len := 5 */
     strcpy(passwd.arr,"TIGER");    /* passwd.arr := "TIGER" */
     passwd.len=strlen(passwd.arr); /* passwd.len := 5 */

     EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;

     printf("Connected to ORACLE as: %s\n\n", userid.arr);

     /* Enter minimum salary by user */
     printf("Please enter minimum salary > ");
     retval = scanf("%f", &min_sal);

     if(retval != 1) {
        printf("Input error!!\n");
        EXEC SQL ROLLBACK WORK RELEASE;
        /* Disconnect from ORACLE */
        exit(2); /* Exit program */
     }

     /* Declare cursor; cannot occur in declare section! */
     EXEC SQL DECLARE EMP_CUR CURSOR FOR
     SELECT EMPNO,ENAME,SAL FROM EMP
        WHERE SAL>=:min_sal;

     /* Print Table header, run cursor through result set */
     printf("Empployee-ID      Employee-Name       Salary \n");
     printf("---------------   -----------------   -------\n");
     EXEC SQL OPEN EMP_CUR;
     EXEC SQL FETCH EMP_CUR INTO :empno, :ename, :sal; /* Fetch 1.tuple */
     while(sqlca.sqlcode==0) { /* are there more tuples ? */
        ename.arr[ename.len] = '\0';    /* "End of String" */
        printf("%15d   %-17s   %7.2f\n",empno,ename.arr,sal);
        EXEC SQL FETCH EMP_CUR INTO :empno, :ename, :sal; /* get next tuple */
     }
     EXEC SQL CLOSE EMP_CUR;

     /* Disconnect from database and terminate program */
     EXEC SQL COMMIT WORK RELEASE;
     printf("\nDisconnected from ORACLE\n");
     exit(0);

     /* Error Handling: Print error message */
     error: printf("\nError: %.70s \n",sqlca.sqlerrm.sqlerrmc);
     EXEC SQL ROLLBACK WORK RELEASE;
     exit(1);
     }