Using SQL Commands in Pro*C

In this articles, we will see how to use DML commands in Pro*C. Though Pro*C supports both static and dynamic SQL, in this article we will concentrate on only static SQL commands.

In the previous article Getting Started With Pro*C we have seen how to connect to Oracle through Pro*C. Now we will see how to use basic SQL commands such as UPDATE. We will also see how to get information regarding the status of the most recently executed command using SQLCA and the role played by host variables in sending data to Oracle from Pro*C.

Sample Program

Executing SELECT command

WHENEVER statement

Executing UPDATE Command

Getting Information using SQLCA

Sample Program

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


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);

Executing UPDATE Command

Executing UPDATE command is similar to SELECT command except that SELECT copies data into host variable and UPDATE takes data from host variables. 

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

   goto normalexit;
After successfully executing UPDATE command, the program should be terminated with COMMIT. So, goto statement of C is used to transfer control to normalexit label so that program is terminated after executing COMMIT WORK RELEASE command.

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 as discussed in the previous article Getting Started With Pro*C 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

We will see more about handling NULL values and handling multiple rows using cursors in future article.

P.Srikanth