Programming in Pro*C/C++

Introduction
The Oracle pre-compiler is a programming tool that allows you to incorporate SQL statements directly into your program. It takes your source code as input and then translates the embedded SQL statements into Oracle runtime library calls, generating a modified source program that you can then compile, link, and execute in the usual way. This process enables you to write ‘human readable’ SQL within your code, which Oracle can then translate into low level API calls to interact with the database.
The pre-compilers are available for a number of programming languages (e.g. C/C++ and COBOL) and are supported on the majority of today’s platforms (e.g. UNIX and NT). This guide focuses primarily on the C/C++ implementation of the Oracle pre-compiler and provides numerous examples and tips for your development. Please refer to the Oracle installation guide for how to install Pro*C as it can vary from platform to platform.
By running the Pro*C pre-processor against your source code prior to compilation, SQL can be written using a number of different Pro*C 'methods'. These determine how your final application essentially interacts with the database and directly affects the efficiency, flexibility and nature of your SQL. The first section of this guide provides a high-level overview of two such approaches: ‘Embedded SQL’ and dynamic SQL as these are generally the most common forms of Pro*C you will encounter.

Pro*C Syntax
All SQL statements need to start with EXEC SQL and end with a semicolon ";". You can place the SQL statements anywhere within a C/C++ block, with the restriction that the declarative statements do not come after the executable statements.
Example:
{
int a;
/* ... */
EXEC SQL SELECT salary INTO :a
FROM Employee
WHERE SSN=216574419;
/* ... */
printf("The salary is %d\n", a);
/* ... */
}

Preprocessor Directives
The C/C++ preprocessor directives that work with Pro*C are #include and #if. Pro*C does not recognize #define. For example, the following code is invalid:
#define THE_SSN 216574419
/* ... */
EXEC SQL SELECT salary INTO :a
FROM Employee
WHERE SSN = THE_SSN; /* INVALID */

Statement Labels
You can connect C/C++ labels with SQL as in:
EXEC SQL WHENEVER SQLERROR GOTO error_in_SQL;
/* ... */
error_in_SQL:
/* do error handling */
We will come to what WHENEVER means later in the section on Error Handling

Database Types and the Pro*C/C++ Equivalent
There are many combinations for Oracle database types (e.g. CHAR) and their Pro*C/C++ equivalents. For a definitive list you should refer to the Oracle manual, however the following table summarises the most frequently used options from my experience. In general you also need to check which platform your application is going to run on, as the later versions of Oracle for Solaris for example, support the long long C/C++ datatypes (18 digits).

CHAR(1) char variable;
CHAR(N) char variable[N+1];
VARCHAR2(N) char variable[N+1];
NUMBER(N) long variable; Where N<10>10
NUMBER(N,M) char variable[N+1];
DATE char variable[21];

Legal and Illegal usage of host variables
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 */
/* ... */
}

Other Important constructs in Pro*C/C++
Pointers
You can define pointers using the regular C syntax, and use them in embedded SQL statements. As usual, prefix them with a colon:
int *x;
/* ... */
EXEC SQL SELECT xyz INTO :x FROM ...;
The result of this SELECT statement will be written into *x, not x.

Structures
Structures can be used as host variables, as illustrated in the following example:
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
Host arrays can be used in the following way:
int emp_number[50];
char name[50][11];
/* ... */
EXEC SQL INSERT INTO emp(emp_number, name)
VALUES (:emp_number, :emp_name);

Quick recap with an example
#include
#include
#include
#include
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[30];
VARCHAR pwd[30];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA.H;
void main()
{
strcpy(uid.arr,"SCOTT");
uid.len =strlen(uid.arr);
strcpy(pwd.arr,"TIGER");
pwd.len = strlen(pwd.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf("Connected to Oracle8i using Scott/Tiger\n");
EXEC SQL COMMIT WORK RELEASE;
return;
errexit: printf("Connection failed");
return;
}
/* end of main */

Error handling
WHENEVER statement is used to specify what action to be taken when there is an error in the subsequent statements. EXEC SQL WHENEVER SQLERROR GOTO errexit;
The above statement specifies if there is an error in any of the subsequent statements then control should goto a label called errexit.
There are other forms of WHENEVER statement that we will examine in later articles.
The next statement used CONNECT command to connect to Oracle. It uses host variables - uid and pwd to supply username and password. Once connection is successful then it displays a message and then COMMIT WORK RELEASE will release the resources and log off. EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; printf("Connected to Oracle8i using Scott/Tiger\n"); EXEC SQL COMMIT WORK RELEASE; return;
Statements given after lable errexit will be executed when connection is not successful. It displays an error message and terminates main(). errexit: printf("Connection failed"); return;

Dynamic SQL
While embedded SQL is fine for fixed applications, sometimes it is important for a program to dynamically create entire SQL statements. With dynamic SQL, a statement stored in a string variable can be issued. PREPARE turns a character string into a SQL statement, and EXECUTE executes that statement. Consider the following example.
char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)";
EXEC SQL PREPARE q FROM :s;
EXEC SQL EXECUTE q;
Alternatively, PREPARE and EXECUTE may be combined into one statement:
char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)";
EXEC SQL EXECUTE IMMEDIATE :s;

WHENEVER Statement
This statement allows you to do automatic error checking and handling. The syntax is:
EXEC SQL WHENEVER ;
Oracle automatically checks SQLCA for , and if such condition is detected, your program will automatically perform .
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
can be any of the following:
CONTINUE - Program will try to continue to run with the next statement if possible
DO - Program transfers control to an error handling function
GOTO - Program branches to a labeled statement
STOP - Program exits with an exit() call, and uncommitted work is rolled back
Some examples of the WHENEVER statement:
EXEC SQL WHENEVER SQLWARNING DO print_warning_msg();
EXEC SQL WHENEVER NOT FOUND GOTO handle_empty;
Here is a more concrete example:
/* 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);
}
/* ... */
Note that the WHENEVER statement does not follow regular C scoping rules. Scoping is valid for the entire program. For example, if you have the following statement somewhere in your program (such as before a loop):
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;

C++ Users
To get the precompiler to generate appropriate C++ code, you need to be aware of the following issues:
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.

For more of sample Pro*C/C++ programs and more detailed explanation of select, update and other queries, do request them in the commnets section. Cheers..!!