Embedded SQL/Oracle Tutorial
Cursors
By now, if you have been following the tutorials closely, you should be quite familiar with inserting, updating, and deleting database records. The next step is to create querying functions (i.e., to handle SELECT operations). We have intentionally left querying until last because there often are more steps to perform. Unlike the format of queries we typed into SQL*Plus, embedded SQL requires the use of cursors to successfully output the results of the query.Cursors were invented to satisfy both the SQL and host programming languages. SQL queries handle sets of rows at a time, while C++, for example, handles only one row at a time. When we type the following SQL query into SQL*Plus:
SQL> select driver_sin, count(exam_score)
2 from exam
3 where exam_type = 'L'
4 group by driver_sin;
we get the following output:
DRIVER_SIN COUNT(EXAM_SCORE)In our embedded SQL code, we cannot simply specify:
---------- -----------------
111111111 1
222222222 2
333333333 3
444444444 1
EXEC SQL SELECT driver_sin, count(exam_score)
FROM exam
WHERE exam_type = 'L'
GROUP BY driver_sin;
and expect C++ to output the results of the query. We have to fetch the results of this query into a cursor, and then output the results one at a time using C.
To use a cursor in embedded SQL, we must first declare it. We do this by using the DECLARE keyword, with the following syntax:
EXEC SQL DECLARE <cursor name> CURSOR FOR
SELECT ... FROM ...;
where the SELECT part of the statement specifies the query. Note that the above statement is only a declaration and the SELECT itself has not been executed yet. The declaration must occur before it is used. The scope of a cursor is the entire Pro*C++ program, but cursor statements (DECLARE, OPEN, FETCH, and CLOSE) must occur within the same precompiled unit. Therefore, for the entire program, each <cursor name> must be unique.
Once a cursor is declared, we have to open it in order to execute the query. To do this, we use the OPEN keyword, as follows:
EXEC SQL OPEN <cursor name>;
When we first open a cursor, it points to just before the first row (of the result). To retrieve rows (one at a time) which satisfy the SELECT query, we need to use the FETCH keyword. The syntax of the FETCH statement is:
EXEC SQL FETCH <cursor name> INTO :hostvar1, :hostvar2, ...;
Note that we have to first declare and open the cursor with cursor name before being able to use it in a FETCH statement.
After executing the FETCH statement, the cursor is set to point to the beginning of the next row of the answer set. When all rows have been fetched, sqlcode is set to 100 or 1403. Acknowledging this, we can write simple while loops which continuously fetch and print out tuple values for each row by testing sqlcode for the values 100 and 1403. You will see this in the example given below.
After all rows have been fetched, you can close the cursor with the command:
EXEC SQL CLOSE <cursor name>
A cursor can always be reused, so if you want to reuse your cursor, all you have to do is reopen it. The FETCH statement only moves forward in tables, so you might want to reopen a cursor to revisit and fetch previous rows in a table.
Sample Program
You should know enough about cursors by now to complete any homework involving embedded SQL. Here is the Pro*C++ source code for maintaining the branch relation. In particular, note the subroutine called Show_Branch() which shows information for all branches.#include <iostream.h>
#include <stdlib.h> // needed for atoi()
#include <stdio.h> // needed for gets()
#include <string.h>
#include <unistd.h> // needed for getpassphrase()
#include <iomanip.h> // needed for setw()
#define MAXBUF 50 // maximum length of buffer
char line[MAXBUF]; // buffer to hold stdin
EXEC SQL INCLUDE sqlca; // declarations for error checking
EXEC SQL WHENEVER SQLERROR DO print_error();
EXEC SQL WHENEVER SQLWARNING DO print_warning();
EXEC SQL WHENEVER NOTFOUND DO print_not_found();
void print_error()
{
// display the error message returned by Oracle
cout << "\n!! Unsuccessful operation. Error code: " << sqlca.sqlcode;
cout << "\n Oracle Message: " << sqlca.sqlerrm.sqlerrmc << "\n";
}
void print_warning()
{
// display the warning message returned by Oracle
cout << "\n!! A warning occurred. Error code: " << sqlca.sqlcode;
cout << "\n Oracle Message: " << sqlca.sqlerrm.sqlerrmc << "\n";
}
void print_not_found()
{
// display the "row not found" message returned by Oracle
cout << "\n!! Warning. Row not found. Error code: " << sqlca.sqlcode;
cout << "\n Oracle Message: " << sqlca.sqlerrm.sqlerrmc << "\n";
}
void Connect()
{
// connect to database
EXEC SQL BEGIN DECLARE SECTION;
char userid[64];
char password[64];
char *DBname = "@ug";
EXEC SQL END DECLARE SECTION;
cout << "\nUsername: ";
gets(userid);
strcat(userid, DBname);
strcpy(password, getpassphrase("Password: "));
EXEC SQL CONNECT :userid IDENTIFIED BY :password;
}
void Insert_Branch()
{
// Insert a tuple into the branch relation
EXEC SQL BEGIN DECLARE SECTION;
int bid;
VARCHAR bname[20];
VARCHAR baddr[50];
VARCHAR bcity[20];
int bphone;
short int baddr_ind;
short int bphone_ind;
EXEC SQL END DECLARE SECTION;
cout << "\nBranch ID: ";
gets(line);
bid = atoi(line);
cout << "\nBranch Name: ";
gets(line);
bname.len = strlen(line);
strncpy((char *) bname.arr, line, bname.len);
cout << "\nBranch Address: ";
gets(line);
baddr.len = strlen(line);
strncpy((char *) baddr.arr, line, baddr.len);
cout << "\nBranch City: ";
gets(line);
bcity.len = strlen(line);
strncpy((char *) bcity.arr, line, bcity.len);
cout << "\nBranch Phone: ";
gets(line);
if (strlen(line) != 0)
bphone = atoi(line); // phone number is not null
else
bphone_ind = -1; // phone number is null; set indicator
EXEC SQL INSERT
INTO branch (branch_id, branch_name, branch_addr, branch_city,
branch_phone)
VALUES (:bid, :bname, :baddr:baddr_ind, :bcity, :bphone:bphone_ind);
// The WHENEVER statement will handle the error processing, but
// to show the sequence of error messages, let's add the following.
if (sqlca.sqlcode < 0)
cout << "An error was detected. The details are described above.\n";
EXEC SQL COMMIT WORK;
}
void Delete_Branch()
{
// Delete a tuple from the branch relation, given the branch id
EXEC SQL BEGIN DECLARE SECTION;
int bid;
EXEC SQL END DECLARE SECTION;
cout << "Branch ID: ";
gets(line);
bid = atoi(line);
EXEC SQL DELETE
FROM branch
WHERE branch_id = :bid;
EXEC SQL COMMIT WORK;
}
void Update_Branch()
{
// Update the branch name, given the branch id
EXEC SQL BEGIN DECLARE SECTION;
int bid;
VARCHAR bname[20];
EXEC SQL END DECLARE SECTION;
cout << "Branch ID: ";
gets(line);
bid = atoi(line);
cout << "New Branch Name: ";
gets(line);
bname.len = strlen(line);
strncpy((char *) bname.arr, line, bname.len);
EXEC SQL UPDATE branch
SET branch_name = :bname
WHERE branch_id = :bid;
EXEC SQL COMMIT WORK;
}
void Show_Branch()
{
// Display information about branches
EXEC SQL BEGIN DECLARE SECTION;
int bid;
VARCHAR bname[20];
VARCHAR baddr[50];
VARCHAR bcity[20];
int bphone;
short int baddr_ind;
short int bphone_ind;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE branch_info CURSOR FOR
SELECT * FROM BRANCH;
EXEC SQL OPEN branch_info;
EXEC SQL FETCH branch_info
INTO :bid, :bname, :baddr:baddr_ind, :bcity, :bphone:bphone_ind;
cout << setiosflags(ios::left); // left justify the names to come
cout << setw(10) << "ID" << setw(15) << "NAME" << setw(15) << "ADDRESS"
<< setw(15) << "CITY" << setw(15) << "PHONE" << "\n";
cout << "--------------------------------------------------------------\n";
while (sqlca.sqlcode >= 0 && sqlca.sqlcode != 100 &&
sqlca.sqlcode != 1403)
{
bname.arr[bname.len] = '\0'; // null terminates the VARCHARs
baddr.arr[baddr.len] = '\0';
bcity.arr[bcity.len] = '\0';
// display results; keep the columns aligned reasonably well
cout << setw(10) << bid << setw(15) << bname.arr
<< setw(15) << baddr.arr << setw(15) << bcity.arr << setw(15);
if (bphone_ind != -1) // display phone number, if not null
cout << bphone;
else
cout << " ";
cout << "\n";
EXEC SQL FETCH branch_info
INTO :bid, :bname, :baddr:baddr_ind, :bcity, :bphone:bphone_ind;
}
cout << "The last warning just signifies that the cursor fetched the "
<< "final record\n";
EXEC SQL CLOSE branch_info;
EXEC SQL COMMIT WORK;
}
int main()
{
// simple text interface for above functions
int choice, quit;
Connect(); // connect to Oracle
quit = 0;
while (!quit)
{
cout << "\nPlease choose one of the following: \n";
cout << "1. Insert branch\n";
cout << "2. Delete branch\n";
cout << "3. Update branch\n";
cout << "4. Show branch\n";
cout << "5. Quit\n>> ";
gets(line);
choice = atoi(line);
printf("\n\n");switch (choice)
{
case 1: Insert_Branch();
break;
case 2: Delete_Branch();
break;
case 3: Update_Branch();
break;
case 4: Show_Branch();
break;
case 5: quit = 1;
default: exit(0);
}
}
EXEC SQL COMMIT WORK RELEASE; // Commit and free any locks held.
// Any additional non-SQL/non-Oracle work can go here.
}Compile and run the code. You can now test modifications (insert, update, and delete) to the branch relation without having to start up an SQL*Plus session.
Although our example tested sqlca.sqlcode for the values 100 and 1403 in the Show_Branch() function, we could have used error trapping instead and done something like this:
EXEC SQL WHENEVER NOTFOUND DO BREAK;
while(1) {
. . .
EXEC SQL FETCH branch_info
INTO :bid, :bname, :baddr:baddr_ind, :bcity, :bphone:bphone_ind;
. . .
}
/* restore WHENEVER NOTFOUND to what we had before */
EXEC SQL WHENEVER NOTFOUND DO print_not_found();
Embedded SQL/Oracle Tutorial - Cursors
