Getting Started With Pro*C

In this articles we will understand what is Pro*C and how to use it to access Oracle database. The main aim of this articles is to show you how to start working with Pro*C. This is mainly aimed at students who either do not know what is Pro*C or who do not know how to use Pro*C.

What is Pro*C?

Why Pro*C?

What You Need?

Sample Pro*C Program

Embedded Commands

Host Variables

Error Handling

Compiling

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.

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.

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.

The following is the structure of PRECOMP directory.

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.

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.

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.


#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.

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.


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.

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.


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.

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.


   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.

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;


Compiling

After the sample program is saved under the name SAMPLE.PC. Take the following steps to compile and run the program.

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.

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.