Getting Started With Pro*C

What is Pro*C

• First write a C program with embedded SQL commands to perform database operations. Save this file with the extension .PC.
• Compile this program using PRO*C Compiler – PROC – provided by Oracle. The output of Pro*C compiler is a pure C program with all embedded SQL commands replaced with calls to C function in Oracle runtime library. It generates a .C file.
• Compile the .C file generated by Pro*C compiler using C compiler that is supported by Pro*C. On Microsoft platform, Visual C++ 6.0 compiler is supported.
• The compiler generates .EXE file by linking C program with Libraries provided by Oracle.
• Run .EXE file.

Why Pro*C?

• To use the power and flexibility of a highlevel langauge.
• To provide the required user interface
• To customize applications, which may not be possible with available tools. For example, there may be a report that cannot be generated using any of the available report writers.In such cases it is possible to use C to generate that report by taking data from Oracle database.
• To access Oracle data on one side and a particular hardware on the other side.

What You Need?

Simple Pro*C Program

#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 */

Embedded Commands

• Executable - they are executed by Oracle to perform some operation
• Declarative - they are used to declare host variables etc.

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[30];
VARCHAR pwd[30];
EXEC SQL END DECLARE SECTION;

Host Variables

EXEC SQL INCLUDE SQLCA.H;
    

Using Host Variables

strcpy(uid.arr,"SCOTT");
uid.len =strlen(uid.arr);
strcpy(pwd.arr,"TIGER");
pwd.len = strlen(pwd.arr);
   

Error handling

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;

Compiling

• Make C:\SRIKANTH\PROC directory the current directory.
• Use the following command to compile .PC to create .C.

  PROC  SAMPLE.C
  

• Once SAMPLE.C file is created in the current directory then compile it using command line compiler of VC++ as follows to create SAMPLE.EXE.

  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.

• Once SAMPLE.EXE is created, run it from command line as follows.

  C:\SRIKANTh\PROC\>SAMPLE <enter>
  Connected to Oracle8i using Scott/Tiger
  

Program to Update 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);
}

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

Executing SELECT command

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

WHENEVER statement

noemp:
   printf("Sorry. Invalid employee number. Quitting...");

 WHENEVER  {NOT FOUND|SQLERROR|SQLWARNING} {CONTINUE|GOTO label|STOP|DO routine}

/* 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

Output

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