Home » Pro*C/C++ » An Example For Connecting, Looping, Querying and Displaying records in Pro*C/C++

An Example For Connecting, Looping, Querying and Displaying records in Pro*C/C++

The following example program connects to Oracle, then loops, prompting the user for an employee number. It queries the database for the employee's name, salary, and commission, displays the information, and then continues the loop. The information is returned to a host structure. There is also a parallel indicator structure to signal whether any of the output values SELECTed might be NULL.
Precompile example programs using the precompiler option MODE=ORACLE.

/*


* example1.pc
*
* Prompts the user for an employee number,
* then queries the emp table for the employee's
* name, salary and commission. Uses indicator
* variables (in an indicator struct) to determine
* if the commission is NULL.
*
*/

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


/* Define constants for VARCHAR lengths. */
#define UNAME_LEN 20
#define PWD_LEN 40

/* Declare variables. No declare section is needed if MODE=ORACLE.*/
VARCHAR username[UNAME_LEN];
/* VARCHAR is an Oracle-supplied struct */
varchar password[PWD_LEN];
/* varchar can be in lower case also. */
/*
Define a host structure for the output values of a SELECT statement.
*/
struct {
VARCHAR emp_name[UNAME_LEN];
float salary;
float commission;
} emprec;
/*
Define an indicator struct to correspond to the host output struct. */
struct
{
short emp_name_ind;
short sal_ind;
short comm_ind;
} emprec_ind;

/* Input host variable. */
int emp_number;
int total_queried;
/* Include the SQL Communications Area.
You can use #include or EXEC SQL INCLUDE. */
#include <sqlca.h>

/* Declare error handling function. */
void sql_error();

main()
{
char temp_char[32];

/* Connect to ORACLE--
* Copy the username into the VARCHAR.
*/
strncpy((char *) username.arr, "SCOTT", UNAME_LEN);
/* Set the length component of the VARCHAR. */
username.len = strlen((char *) username.arr);
/* Copy the password. */
strncpy((char *) password.arr, "TIGER", PWD_LEN);
password.len = strlen((char *) password.arr);
/* Register sql_error() as the error handler. */
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--n");

/* Connect to ORACLE. Program will call sql_error()
* if an error occurs when connecting to the default database.
*/
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("nConnected to ORACLE as user: %sn", username.arr);
/* Loop, selecting individual employee's results */
total_queried = 0;
for (;;)
{
/* Break out of the inner loop when a
* 1403 ("No data found") condition occurs.
*/
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
emp_number = 0;
printf("nEnter employee number (0 to quit): ");
gets(temp_char);
emp_number = atoi(temp_char);
if (emp_number == 0)
break;
EXEC SQL SELECT ename, sal, NVL(comm, 0)
INTO :emprec INDICATOR :emprec_ind
FROM EMP
WHERE EMPNO = :emp_number;
/* Print data. */
printf("nnEmployeetSalaryttCommissionn");
printf("--------t------tt----------n");
/* Null-terminate the output string data. */
emprec.emp_name.arr[emprec.emp_name.len] = '';
printf("%-8st%6.2ftt",
emprec.emp_name.arr, emprec.salary);
if (emprec_ind.comm_ind == -1)
printf("NULLn");
else
printf("%6.2fn", emprec.commission);

total_queried++;
} /* end inner for (;;) */
if (emp_number == 0) break;
printf("nNot a valid employee number - try again.n");
} /* end outer for(;;) */

printf("nnTotal rows returned was %d.n", total_queried);
printf("nG'day.nnn");

/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
void sql_error(msg)
char *msg;
{
char err_msg[128];
int buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("n%sn", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
if (msg_len > buf_len)
msg_len = buf_len;
printf("%.*sn", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(1);
}