This program connects to ORACLE, declares and opens a cursor, fetches the names, salaries, and commissions of all sales people, displays the results, then closes the cursor.
The following shows a query that uses an explicit cursor, selecting data into a host structure:
#include <stdio.h>
#include <sqlca.h>
#define UNAME_LEN 20 #define PWD_LEN 40
/* * Use the precompiler typedef'ing capability to create * null-terminated strings for the authentication host * variables. (This isn't really necessary--plain char *'s * does work as well. This is just for illustration.) */ typedef char asciiz[PWD_LEN];
EXEC SQL TYPE asciiz IS STRING(PWD_LEN) REFERENCE; asciiz username; asciiz password;
/* Connect to ORACLE. */ strcpy(username, "SCOTT"); strcpy(password, "TIGER");
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("nConnected to ORACLE as user: %sn", username);
/* Declare the cursor. All static SQL explicit cursors * contain SELECT commands. 'salespeople' is a SQL identifier, * not a (C) host variable. */ EXEC SQL DECLARE salespeople CURSOR FOR SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%';
/* Open the cursor. */ EXEC SQL OPEN salespeople;
/* Get ready to print results. */ printf("nnThe company's salespeople are--nn"); printf("Salesperson Salary Commissionn"); printf("----------- ------ ----------n");
/* Loop, fetching all salesperson's statistics. * Cause the program to break the loop when no more * data can be retrieved on the cursor. */ EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) { EXEC SQL FETCH salespeople INTO :emp_rec_ptr; printf("%-11s%9.2f%13.2fn", emp_rec_ptr->emp_name, emp_rec_ptr->salary, emp_rec_ptr->commission); }
/* Close the cursor. */ EXEC SQL CLOSE salespeople;
printf("nArrivederci.nn");
EXEC SQL COMMIT WORK RELEASE; exit(0); }
void sql_error(msg) char *msg; { char err_msg[512]; int buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("n%sn", msg);
/* Call sqlglm() to get the complete text of the * error message. */ buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); printf("%.*sn", msg_len, err_msg);