Steve Tillman's Home Page

About

Courses

Schedule

Vitae

Sports

Membership

Login

325 notes-5a

C++ is very similar to java, especially if you do not use most of the object features. They will not be necessary for your extra credit program. SQL commands are embedded in precompiler statements. Each precompiler statement must begin with the flag EXEC SQL. C and C++ programs end the commands with a semicolon (;) as usual.

Every C++ program must do the following three things:

1. Declare an SQL communications area and provide for error handling.
2. Declare the special host variables that oracle uses to interact with the host program.
3. Establish a connection between the program and oracle.

The first is done with the EXEC SQL INCLUDE statement (line 15). (You could also use the C++ directive #include <sqlca.h>.) This causes a file of data declaratives to be embedded into your program. Oracle uses these areas to communicate the result of each system call (SQL statement) to your program. A number is sent to SQLCODE (which can be looked up in the codes and errors manual) and a message is sent to SQLERRMC. SQLWARN contains warnings of various nonfatal conditions.

Hand out SQLCA page, with C structure and description from the manual.

You can do error checking by either explicitly checking for certain conditions, or by having a general purpose WHENEVER clause (see lines 24 and 40).

EXEC SQL WHENEVER <condition> <action>

The conditions can be SQLWARNING, SQL ERROR, or NOT FOUND (which is similar to "end of file"). Of course you would not be working with a "file" in the strict sense of the word. It would mean the end of the current collection of data.

The action can be CONTINUE, DO routine call>, GO TO label-name, or STOP. The routine call would be either function_name() or break. The argument list for function_name must be empty.

The scope of a WHENEVER is physical not logical. It applies to all statements up until the next WHENEVER of the same type, or the end of the program. That is why routine sqlerror() has a new WHENEVER to avoid the possibility of an infinite loop.

Host variables are variables in your host language program which are used to interface directly with the Oracle database. They are defined in the same manner that variables are usually defined, but between the two statements

	EXEC SQL BEGIN DECLARE SECTION and
	EXEC SQL END DECLARE SECTION

A host variable is primarily used within an SQL statement to send information to or receive information from the oracle database. If a host variable is to correspond to a column variable in the database, its type must be compatible with the type of the column variable. That leads to a potential problem because oracle data types are not completely compatible with C++ data types.

The data type VARCHAR needs to be explained, since it does not exist in C++. We have seen that oracle has a variable length character string data type called VARCHAR2, along with a fixed length data type called CHAR. C++ has a variable type, char. For whatever reason, oracle will send information to a char variable correctly, but will not always receive information from a char variable unless all character positions are filled. I have gotten into the habit of describing all character fields in oracle as VARCHAR2. If it is necessary for them to correspond to a host language variable, declaring it as VARCHAR in C++ will work, but just using char might work as well. I am not totally sure why char works only some of the time. You may have problems with trailing blanks in comparing items sent to the database. VARCHAR can be used as shown in the following example:

	VARCHAR  item[20];

struct {
	unsigned short len;
	unsigned char arr[20];
	} item;

len holds the current length of the array stored in arr. To get information from an ordinary character string to the structure you could do the following:

                strcpy((char *)item.arr, ordinary);
                item.len = strlen(item.arr);

Since an unsigned character array is not precisely the same as a character array, it is necessary to cast the receiving field of the the strcpy statement.

Host variables may be used within SQL statements anytime a constant would be appropriate. Within such statements they must be preceded by a colon (:). Within the "normal" part of the program they can be used just as any other variable of the same type, subject to the changes for VARCHAR given above. However, if a VARCHAR variable is used within an SQL statement the variable name alone is used, and not any of the expanded parts created by the precompiler. See lines 20 - 23, and 26 - 27. There are numerous other restrictions on host variables, most of them somewhat obscure, and you can read about them in the online documentation if you run into a problem.

The connection to oracle is made with the EXEC SQL CONNECT statement. Be sure your user name and password get appropriate values before the connect is made. This would be your oracle user name and password. Because of a quirk in the way the system was implemented it is necessary to give the username in the following format: username@database_name

A major consideration in interfacing SQL with a third generation host language is that the SQL SELECT statement generally returns multiple rows, while the host language processing will deal with one row (record) at a time. We saw that java deals with this problem via a ResultSet object. An older language such as C++ does not have this construct. To solve this problem Oracle uses what is referred to as a cursor.
Hand out first fetch program and output.

A cursor is defined by a DECLARE statement which names it and associates it with a query. The DECLARE statement (lines 29 - 33) does not execute the query. It is descriptive only, and it must physically precede all SQL statements which reference the cursor. The query is executed by an OPEN statement. Once the statement is executed, all the rows which meet the search condition of the query form what is called the active set of the cursor. The active set can be thought of as if it were a sequential file. Rows are retrieved one at a time from the active set by means of FETCH statements. FETCH operates on the active set in a similar manner to the way READ in a third generation language operates on a sequential file. It starts at the first row, and for each execution of it, it returns the next row until there is no next row. When a FETCH statement is executed after the last row has been returned, the value of 1403 is transferred to the item SQLCODE. You can either check this directly or use SQL WHENEVER NOT FOUND as was done in the C++ example (line 37). Finally, when the processing of the active set is completed, it is a good idea to use a CLOSE statement on the cursor.

The syntax for declaring a cursor is as follows:

	EXEC SQL DECLARE cursor_name CURSOR FOR S-F-W block;

Note that char was used as the type for the username and password rather than VARCHAR. This worked fine in this instance. The problem that would occur would be if data is received from the database, and then sent back to the database. For whatever reason, when data is transferred from the database into a char variable, trailing unused spaces are filled with blanks. This could cause a problem in trying for an equality match.

The defining condition in the previous C++ example used a constant value for the warehouse number. Host variables are permitted in that context.
Hand out fetchp1b.pc
This is similar to the last program, except the warehouse number is placed in a host variable (see lines 15, 30, 31, and 36).
Extra credit mini project

For the previous set of notes, click on 325 notes-5

For the next set of notes click on 325 notes-6