Compiling
Oracle provides PROC compiler, which takes a C program with embedded SQL statements and converts the embedded SQL statements to Oracle Runtime Library (also supplied by Oracle).
The compiled C program is a pure C code and can be compiled with C compiler. Pro*C supports Microsoft Visual C++ 6.0 compiler.
The following is the overall process related to Pro*C.
The following is the structure of PRECOMP directory.
What is Pro*C
Pro*c is writing a C program with embedded SQL statements. It is possible to access Oracle database from C program using Pro*C. It allows you to use the power and flexibility of C language for data processing and SQL to retrieve and manipulate data of Oracle database.
Why Pro*C?
The following are some of the reasons why use have to use PreCompiler such as Pro*C.
The above list is by no means complete. It is only to give you some idea about what can be done and not a comprehensive list of all possibilities.
What You Need?
What does one need to know and have to use Pro*C.? First, we have to install Pro*C software. At the time of installing Oracle, make sure you select Pro*C components.
You can check whether your installation of Oracle has Pro*C components installed by examining PRECOMP directory of Oracle home – say C:\ORACLE8i\PRECOMP.
| PUBLIC | Contains the required header (.h) files. |
| LIB | Contains the libraries used by language linker. |
| DEMO | Contains demo program. |
| ADMIN | Contains configuration files. |
| DOC | Documentation related to Precompilers. |
Depending on the language you want to use load appropriate compiler. The languages supported by Precompiler is C/C++, COBOL , FORTRAN, and ADA.
As we are talking about Pro*C, we need to install C/C++ compiler that is supported by Oracle. On Windows, Oracle supports Visual C++ 6.0 compiler. So make sure you have installed Visual C++ 6.0.
So far knowledge is concerned, you must be aware of C of course and SQL and if required PL/SQL.
The following is a simple Pro*C program to connect to Oracle using SCOTT username and password TIGER.
Write the following code as save the file with .PC extension. The example is saved with the name SAMPLE.PC.
All embedded commands are included into C program using the prefix EXEC SQL. That means Pro*C compiler is converting all lines that start with EXEC SQL into appropriate C code.
Declaring variables that are to be used with SQL statements is to be done inside BEGIN DECLARE SECTION and END DECLARE SECTION. This section is used to
declare host variables.
Host variables are declared between BEGIN DECLARE SECTION and END DECLARE SECTION and used in embedded sql with prefix : (colon).
A host variables can be associated with Indicator Variable that indicates the value or condition of the host variable.
In the sample program we created two host variables - uid and pwd. Each of type VARCHAR, which in internally converted to a structure with two members - arr and len.
The statement INCLUDE SQLCA.H is used to include a structure called SQLCA, which stands for SQL Communication Area. This is used by Oracle to provide information about the most recently executed command. We will see more on this in later articles.
In order to use a host variable of VARCHAR type, we need to copy the string to arr and length of the string to len members.
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.
Statements given after lable errexit will be executed when connection is not successful. It displays an error message and terminates main().
The following steps assume that SAMPLE.PC is in C:\SRIKANTH\PROC directory and Oracle is installed into C:\ORACLE8I directory.
Also make sure BIN directory of Oracle and BIN directory of Visual C++ are in system PATH.
Simple Pro*C Program
The best way to understand any kind of programming is to start writing a program. So let us see how a simple Pro*C program is written, compiled and run.
#include <stdio.h>
#include <string.h>
#include <sqlda.h>
#include <sqlcpr.h>
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 */
The following few sections will explain the above program.
First we need to include some .h files. Some of them are belonging to C and others such as SQLDA.H SQLCPR.H are required for embedded sql. These files are present in PUBLIC directory of PRECOMP directory.
Embedded Commands
These commands are the commands that Pro*C compiler takes care of. They are meant for Pro*C compiler. They are either converted to some calls to runtime library of Oracle or provide required information to Pro*C compiler. So these command are of two types.
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[30];
VARCHAR pwd[30];
EXEC SQL END DECLARE SECTION;
Host Variables
Host variables are variables of the host language that are used with SQL command in embedded sql. Host variables are used to communicate with Oracle. Host variables are used to send values from C to Oracle and also to receive values from Oracle.
EXEC SQL INCLUDE SQLCA.H;
Using Host Variables
Then we use host variables - uid and pwd. Each of these variables contains an array of char and an int variable. Internally uid is converted to a variable of structure.
strcpy(uid.arr,"SCOTT");
uid.len =strlen(uid.arr);
strcpy(pwd.arr,"TIGER");
pwd.len = strlen(pwd.arr);
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.
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;
Compiling
After the sample program is saved under the name SAMPLE.PC. Take the following steps to compile and run the program.
PROC SAMPLE.C
CL /IC:\ORACLE8I\PRECOMP\PUBLIC SAMPLE.C /LINK C:\ORACLE8i\PRECOMP\LIB\MSVC\ORASQL8.LIB
/I is used to specify where compiler can find include files.
/LINK is used to pass information to linker. In this case we passwed name of library to be used by the linker.
C:\SRIKANTh\PROC\>SAMPLE
So that's all you have to do to create a simple Pro*C program. We will see more about Pro*C in future articles.
P.Srikanth.