Class 15

Note: midterm next Tues, Nov. 3, practice midterm is online

HW3 due Sunday, solutions posted Monday

Demo: use of makefiles, infinite loop caused by code shown just below, with no “exec sql whenever sqlerror continue;” at report_error in ex511.pc, fixed case (ex511.pc as supplied online) has no such infinite loop.

Also we looked at the generated C code and saw the sqlca struct and the inserted lines of form “if (sqlca.errcode < 0) go to report_error;” after each executable exec sql....

Finally, we executed the Java Ex511.java, including the error cases (bad username, bad tablename) tried in the C case.

More on error handling

What if we write:

main( )

{

exec sql whenever sqlerror goto handle_error;

exec sql create table customers

(cid char[4] not null, cname varchar(13), . . . )

. . .

handle_error:

exec sql drop customers;

exec sql disconnect;

exit(1);

What's the problem? Possible infinite loop because goto action still in force when drop table (which may itself be an error if table was never created). Need to put in:

exec sql whenever sqlerror continue;

Right at beginning of handle_error. Overrides goto action (won't do anything). (Default effect if don't say anything about whenever.)

We also need to take default non-action to perform specific error checking. For example. we might have an alternative in mind in case insufficient disk space when try to create table (e.g. use old table):

exec sql whenever sqlerror goto handle_error;

exec sql create table customers ( . . .)

if (SQLSTATE == -8112) /* if insufficient disk space */

But this won't work, because Whenever statement has placed a test

if(ERROR OCCURS) go to handle_error;

IMMEDIATELY after create table, so it gets there first and the test for sqlca.sqlcode = -8112 never gets entered. So have to write whenever sqlerror continue before testing for specific error.

Book shows (pg 278) how to print out a specific error msg using the print_error function in Oracle, overriding an intrusive Whenever sqlerror statement.

We might also want to look at sqlca.sqlcode or SQLSTATE right after erroneous SQL statement use in case we have an alternative to try for this specific call instead of just printing out an error msg.

Accessing Error Codes from Java

SQLException has two methods: getSQLState() and getErrorCode() to return the SQLSTATE code and the vendor-specific error code (like sqlca.errcode).

(Discuss application environment for naive user: never leave application environment, write errors to log file).

Indicator Variables: Retrieving null values in C

Recall that if discnt in customers has a null value for a row, do not want to retrieve this row with:

select * from customers where discnt <= 10 or discnt >= 10;

But now say we performed this test in program logic:

exec sql select discnt, <other cols> into :cdiscnt, <other vars>

where cid = :custid;

Now we decide to print out these col values in situation:

if(cdiscnt <= 10 || cdiscnt > 10) printf (. . .).

Now look! Host variable cdiscnt is a float var, AND ALL MEANINGFUL BIT COMBINATIONS ARE TAKEN UP WITH REAL VALUES. There's no way that cdisnt will fail the if test above.

Need to add some way to test if the variable cdisnt has been filled in with a null value (then include AND NOT NULL in if test). Can do this with indicator variable, retrieved along with discnt. In Declare section, put:

short int cdiscnt_ind; /* form of declaration */

Then change select:

exec sql select discnt, <other cols> into :cdiscnt:cdiscnt_ind,

<other vars> where cid = :custid;

Now if cdiscnt_ind == -1, value of cdiscnt is really null. Add test that cdiscnt is not null by:

if((cdiscnt <= 10 || cdiscnt > 10) && cdiscnt_ind <> -1)

printf (. . .).

Updating to null value in Java.

Note can also STORE a null value by setting a null indicator (cdiscnt_ind set to -1) and writing

exec sql update customers set discnt = :cdiscnt:cdiscnt_ind where . . .

One other common use for an indicator variable for char column being read in to array, is to notify truncation by value > 0, usually length of column string.

Retrieving null values in Java

In Java, if the getter returns an object, for ex. rset.getString, then it returns null if the column value is null. If it returns a primitive type, such as rset.getInt, then you need to call getInt followed by boolean rset.wasNull() to check if the value was in null in the DB.

Updating to null value in Java.

Simply use stmt.execute(“update t set x=null”) or whatever. This string is good SQL, so it works.

Section 5.3. Descriptions of Embedded SQL Statements.

Select. See Section 5.3, pg 281, Figure 5.3. Can only retrieve 0 or 1 row.

Look at general form: No GROUP BY, UNION or ORDER BY in form . Like Subquery form on pg. 135, add into clause. (Can't use GROUP BY with only one row retrieved.)

See Figure 5.4, pg 282, corresponding (analogous) C types for column type. Conversion done if integer col type, float C var.

Note that in general, variable scan can be used to build up an expression in a search_condition:

select cname into :cust_name where cid = :custid and city = :cname;

But we can't use character string to hold parts of statement requiring parsing:

char cond[ ] = "where cid = 'c003' and city = 'Detroit";

exec sql select cname into :custname where :cond;

NOT LEGAL. The ability to do this is called "Dynamic SQL", covered later in chapter (not in course)

(Java is only dynamic, no problem piecing together strings)

Declare Cursor statement, Fig 5.5, pg. 283. Used when retrieving more than one row in select, so basically an extension of interactive Select.

EXEC SQL DECLARE cursor_name CURSOR FOR

Subquery

[ORDER BY CLAUSE]

[FOR {READ ONLY | UPDATE [OF colname {, colname . . .}]}];

Adds clause: for update of, which we will need need later.

Regular Cursor can only move FORWARD through a set of rows. Can close and reopen cursor to go through a second time. There is also something called a Scrollable Cursor [not in text] which is more flexible:

Two forms of Delete, pg. 283, Searched Delete and Positioned Delete:

exec sql delete from tablename [corr_name]

[where search_condition | where current of cursor_name];

After Searched Delete, we examine sqlca.sqlerrd[2] to determine number of rows affected. New way also exists.

After Positioned delete, cursor will point to empty slot in cursor row sequence, like right after open or when have run through cursor. Works just right to delete everything after look at it in loop:

LOOP

exec sql fetch delcust into :cust_id;

<work on row> -- (but no UI here, since locks are being held)

exec sql delete from customers where current of delcust;

END LOOP

Note that if cursor moved forward to next row after fetch, we would be deleting the wrong row under the cursor using this logic.

Could create cursor to select all customers in Detroit to delete them, or could select all customers and then check if city is Detroit. First is more efficient. PRINCIPLE: do all search_conditions before retrieving.

PRINCIPLE: make the database do the work!

But probably a Searched Delete is most efficient alternative for deleting Detroit customers -- saves needing to switch OS threads during successive fetch calls, accomplish everything in one set-oriented command.

<Also, picture of JDBC program running in a process on another system, connected by TCP connection to the Oracle server process running in dbs2>

In both these cases, row-by-row processing means multiple messages across a TCP connection for each row. Of course a TCP connection between two processes on one system runs much faster than a TCP connection across the Internet, but it still runs slower than a searched delete. In a searched delete, the single delete statement is sent once to the server, and the whole thing runs inside the Oracle server process.

Second paragraph on pg 284: In order for positioned delete to work, the cursor must (1) be already opened and pointing to a real row, (2) be declared for update of some column, (3) FROM clauses of delete must refer to same table as FROM clause of cursor select.

Update statement, pg 285: Searched Update and Positioned Update. Same ideas as with Delete (the count of rows affected is in sqlca.sqlerrd[2]).

Same Update form as earlier Fige 5.7, 5.8: Oracle allows data from other tables to be used in calculating update values. (Even in products where this is not allowed, can get values from other tables in Embedded SQL.)

Insert statement, pg. 286. No positioned insert, because position of newly inserted row determined in other ways: end of table or order by.

Open, Fetch, Close, pg. 286/87. Open evaluates expressions, sets up row list to retrieve from, unchanging even if expression values change.

What happens if selected row is updated to change the selection criteria? If by other program, locked, can't access; if by self, called Sensitivity (pg 319); sensitivity setting is implementation dependent.

Java row-by-row editing: ResultSet can do it

ResultSet has update, delete, even insert capabilities through use of special row position. To use these, you need to create a ResultSet that is updatable, by using the 2-args createStatement as follows.

From Sun tutorial:

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,

                                ResultSet.CONCUR_UPDATABLE);

ResultSet srs = stmt.executeQuery("select COF_Name from COFFEES " +

                "where price = 7.99");

srs.next();

srs.updateString("COF_NAME", "Foldgers");  // companion to getString

srs.updateRow();

What about null values?

null String ref --> null in DB and vice versa, but int or double has no null.

Answer (determined after class) can use

srs.updateNull("PRICE");

Create table, drop table, connect, disconnect. Not create database, because that is not an SQL command. SEE APPENDIX C for more info.