Srikanth Technologies

Getting Started With Pro*C

In this blog 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
Update Salary
Executing SELECT command
WHENEVER statement
Executing UPDATE Command
Getting Information using SQLCA

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.

Program to Update Salary

Just like how you execute an UPDATE command to change the data of the table in other tools, even in Pro*C is the same UPDATE command is executed. The only difference is, here we use host variables to send and receive the data. In fact, even other DML commands can be executed exactly in the same manner as UPDATE.

The following sample program takes employee number and new salary and changes SAL column of EMP table to the given value. It takes employee number from user and displays the name and salary of the employee. Then it takes new salary from user and changes the current salary to the new salary.

/* program to change the salary of an employee */

#include <stdio.h>
#include <string.h>
#include <sqlda.h>
#include <sqlcpr.h>

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[80];
VARCHAR pwd[20];
int empno;
int sal;

VARCHAR ename[30];
int newsal;

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;

   /* take employee number from user */
   printf("Enter employee number : ");
   scanf("%d",&empno);

   /* get the details of the employee */
   EXEC SQL WHENEVER NOTFOUND GOTO noemp;
   EXEC SQL select ename, sal into :ename, :sal from emp where empno = :empno;
   
   /* display employee name and salary */
   ename.arr [ ename.len ] = '\0';

   printf("Name : %s  Salary : %d\n", ename.arr, sal);

   printf("Enter new salary : ");
   scanf("%d", &newsal);


   /* update employee record */
   EXEC SQL update emp set sal = :newsal where empno  = :empno;
   
   printf("Updation Successful");

   goto normalexit;
noemp:
   printf("Sorry. Invalid employee number. Quitting...");
normalexit:
   EXEC SQL COMMIT WORK RELEASE;
   return;
errexit:
   printf("Error: %70s", sqlca.sqlerrm.sqlerrmc);
}

First we connect to Oracle using SCOTT/TIGER. Then we prompt user to enter the number of employee whose salary is to be updated.

/* take employee number from user */
printf("Enter employee number : ");
scanf("%d",&empno);

Executing SELECT command

To take employee number, we use a host variable - empno. It is to be a host variables as it is used in SELECT command later to retrieve the details of the employee.

Then we user SELECT command to retrieve the name and salary of the employee using ENAME and SAL columns of EMP table. It uses two more host variables - ename and sal, to copy the data retrieved using SELECT with INTO clause.

EXEC SQL WHENEVER NOT FOUND GOTO noemp;
EXEC SQL select ename, sal into :ename, :sal from emp where empno = :empno;

WHENEVER statement

WHENEVER command with NOTFOUND option is used to specify that control should goto label noemp if SELECT command doesn't find a row. This is to handle the cases where the employee number entered by user is not valid. The following code at label noemp will display a message and then procedure further to terminate the program.

noemp:
   printf("Sorry. Invalid employee number. Quitting...");
The complete syntax of WHENVER command is as follows:

 WHENEVER  {NOT FOUND|SQLERROR|SQLWARNING} {CONTINUE|GOTO label|STOP|DO routine}
The following table gives details of each option.

NOT FOUND When no row is found by SELECT
SQLERROR When an error resulted by previous command
SQLWARNING When a warning is signaled by Oracle
CONTINUE Indicates that the program should continue with the next statement.
GOTO label Program should branch to the statement named by label.
STOP The execution of the program should stop
DO routine Invokes the specified routine

When SELECT command is successful the data is copied into host variables - ename and sal. Host variable of type VARCHAR is internally declared as a structure that contains two members - arr and len. Oracle copies the length of the string into len member of the structure and data into arr. As the string is not null terminated and terminating string with null character is required in C, we place null character into string at len position.

/* display employee name and salary */
ename.arr [ ename.len ] = '\0';
printf("Name : %s  Salary : %d\n", ename.arr, sal);
printf("Enter new salary : ");
scanf("%d", &newsal);

Getting Information using SQLCA

SQLCA - SQL Commnunication Area is a structure used to provide extra information about the most recently executed SQL command.

The statement INCLUDE SQLCA inserts the declaration of structure and the declaration of variable - sqlca, which is of type struct sqlca. This structure variable can be used to get information about previous execution.

The following table lists some of the important members of this structure.

sqlcode Error code
sqlerrm.sqlerrmc Text of error message
sqlerrm.sqlerrml Length of the error message
sqlerrd[2] Number of rows processed

Output

Compile and run the program and you must see the following output.

Enter employee number : 7369
Name : SMITH  Salary : 800
Enter new salary : 900
Updation Successful. No. of rows updated: 1