Writing Pro*C Program

Pro*c is writing a C program with embedded SQL statements.

Types of Embedded SQL statements

Embedded SQL means placing the SQL statements inside the program . As we house the statements inside the C program , so C program is additionally referred to as the host program.

The Pro*C provides the power to embed the SQL statements inside the program.

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.

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

** NOTE – 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.

Executable statements

  • Executable statements are the SQL statements that allow you to control the info within the Oracle database. These statements call the Oracle runtime library.
  • It also allows your program to attach to the Oracle database, to define the query, to control the info , and process the transactions. These statements are written where C executable statements are often placed.

Declarative statements

  • They are used to declare host variables.
  • Directives or declarative statements are the SQL statements that neither call the Oracle runtime libraries nor operate the Oracle data.
  • It’s wont to declare the Oracle objects, SQL objects. These statements are often written where the C variables are often declared.

Syntax for Embedded SQL statements

  • In the C program , all the embedded SQL statements must start with EXEC SQL, and will end with the semicolon ;
  • We can write the SQL statement anywhere within the program but with one restriction that declarative statements shouldn’t come after the executable statements.

Sample Pro*C Program

NOTE – Pro*C compiler doesn’t recognize #define preprocessor directives. So, we should not use them in embedded sql statements.

Code :

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

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.

Declaring variables

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 the key to the communication between the host program and the database.

You can declare host variables according to C syntax, as you declare regular C variables. The host variable declarations can be placed wherever C variable declarations can be placed. (C++ users need to use a declare section; see the section on C++ Users.)

The C datatypes that can be used with Oracle include:

  • char
  • char[n]
  • int
  • short
  • long
  • float
  • double
  • VARCHAR[n] – This is a psuedo-type recognized by the Pro*C precompiler. It is used to represent blank-padded, variable-length strings. Pro*C precompiler will convert it into a structure with a 2-byte length field and a n-byte character array.

A host variable reference must be prefixed with a colon “:” in SQL statements, but should not be prefixed with a colon in C statements.

int marks;  
EXEC SQL select marks INTO :marks from student where student_id=6;  
printf("The marks of the student is : %d", marks);

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 😡 FROM ...;

The result of this SELECT statement will be written into *x, not x.

Header files for embedded SQL statements

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.

EXEC SQL INCLUDE SQLCA.H;

Example program

Simple Pro*C code connecting to Database and getting some data.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[30];
VARCHAR pwd[30];
typedef struct {
    char name[30];
    char text[2000];
}fnd_msg_t;

fnd_msg_t msg;
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLCA.H;

void connect_db(char *user, char *passwd)
{
    const char* dbname = getenv("TWO_TASK");
    strcpy(uid.arr, user);
    uid.len =strlen(uid.arr);
    strcpy(pwd.arr, passwd);
    pwd.len = strlen(pwd.arr);

    EXEC SQL WHENEVER SQLERROR GOTO errexit;
    EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;

    printf("Connected to %s\n", dbname);

    return;

errexit:
   printf("Connection failed");
   return;
}

int read_data()
{
    EXEC SQL DECLARE msg_cur CURSOR FOR
       SELECT MESSAGE_NAME, MESSAGE_TEXT FROM FND_NEW_MESSAGES
       where MESSAGE_NAME like 'A%'
       AND rownum < 10
       ORDER BY MESSAGE_NAME;
       // SELECT MESSAGE_NAME, MESSAGE_TEXT FROM FND_NEW_MESSAGES 
       //       ORDER BY MESSAGE_NAME;

    EXEC SQL OPEN msg_cur;

    printf("MESSAGE_NAME                \n");
    printf("=========================================================== \n");
    EXEC SQL WHENEVER NOT FOUND DO break;
    for (;;)
    {
        EXEC SQL FETCH msg_cur INTO :msg.name;
        printf("%s\n", msg.name);
    }

    EXEC SQL CLOSE msg_cur;
    return(1);

errexit:
    fprintf(stdout,"Read error is\n");
    fprintf(stdout,"%s\n",sqlca.sqlerrm.sqlerrmc);
    return 0;
}


int main(int argc, char *argv[])
{
   connect_db(argv[1], argv[2]);

   read_data();

   return 0;
}