Class 14.
Note new link on the class web page for the Sun JDBC tutorial
--a good way to learn JDBC
Note: you don’t need to learn JDBC if you want to stick to ESQL/C. Conversely, you can specialize in JDBC and ignore ESQL/C details.

The JDBC API is past of standard Java library or “JDK”, so no special jar files are needed to compile sources that use it, but for execution, it needs a “JDBC Driver” for the DB

- we need the Oracle JDBC Driver, ojdbc14.jar for Java 1.4/1.5/1.6 (1.6 is also called Java 6)

This works on UNIX and Windows  (Java portability!)

In reading the Sun tutorial--

- Don’t worry about “ODBC bridge”

- Don’t worry about DB creation

Read it up to the "Milestone".

Second Example in ESQL/C: Selecting multiple rows with a Cursor.  (pg. 270) The ESQL/C Select statement used above can only retrieve a SINGLE value.  To perform a select of multiple rows, need to create a CURSOR.  Then deal with rows one at a time as retrieved. 

ONE-ROW-AT-A-TIME PRINCIPAL (for both C and Java):  it is never appropriate to try to retrieve a set of rows into an array when there is an unknown number of rows (this is usually the case if there are more then one).  Need to retrieve one at a time:  cursor keeps track of where we are in the selected rows to retrieve.

THIS IS A COMMON BEGINNER'S MISTAKE, to try to get all the data FIXED in an array.  Use basic idea of a LOOP in programming.  Find the max of an input sequence of integers.  How long a sequence?  Don't know.  Forget about bringing all the integers into an array.  Inappropriate.

ESQL/C: Declare a cursor, open the cursor, fetch rows from the cursor, close the cursor.  E.g., declare a cursor to retrieve aids of agents who place an order for a given cid, and the total dollars of orders for each such agent.

    exec sql declare agent_dollars cursor for

         select aid, sum(dollars) from orders

         where cid = :cust_id group by aid;

See program in Fig 5.2, pg. 273 for above, and ex512.pc in the esql directory. But this is only a declaration.  First Runtime statement is Open Cursor:

    exec sql open agent_dollars;

VERY IMPORTANT:  when open cursor, variables used are evaluated at that moment and rows to be retrieved are determined.  Changing variables after Open Cursor statement executed will not affect rows selected.

Now fetch rows one after another:

    exec sql fetch agent_dollars into :agent_id, :dollar_sum;

The Into clause now associated with fetch rather than open, to give maximum flexibility.  (Might want to fetch into different variables under certain conditions.)

When Open Cursor, cursor points to position just before first row.  When fetch, get next row and fetch new row values.

If no new row, get runtime “not found” condition and no returned value (comparable with getchar( ) in C, returning a value of EOF at End of File). We will see how to use: whenever not found go to finish; to control execution in this case.

Second Example in Java: handout of Java code Ex512.java, available in the jdbc directory linked from the class web page.

In Java, we get a ResultSet object back from statement.createQuery(“...”); and use it to next() through the rows. Just as in the C case, the ResultSet (say rset) starts out positioned before the first row, and the first next() make the first row the current row, so that calls to rset.getXXX() retrieve the column values for the row.  If no new row, next() simply returns false.

Error Handling: very messy in ESQL/C, messy in JDBC as well!

At the top of ex512.pc, pg 273:

   exec sql include sqlca  (Communication Area).

The sqlca member sqlca.sqlcode gives Error code, including Warnings such as out-of-data information. Although sqlca.sqlcode is being denegrated in favor of a new standard, SQLSTATE, we still use it, and SQLSTATE as well.

Runtime errors often represent conceptual errors in program code, so it's important to print out error message in programs you're debugging. To do this, you only need to insert in program, before any exec sql runtime call:

    exec sql whenever sqlerror goto report_error;

What this does is set the precompiler (esqlc) to process the following source file text, looking for all RUNTIME exec sql statements (not Declare sections, but select, open cursor, fetch, insert, delete, etc.) and place right after them the test:

    if (ERROR HAS OCCURRED) go to report_error;

The precompiler is dumb. It doesn’t compile your C, it just follows its rules about processing the text, so it will put nonsense goto’s in your program in some cases.

A well-formed goto in C goes to a label like report_error: in the same function. See K&R, pp. 65-66. So if your function ends and another one begins in the source text, the preprocessor will put goto’s in the second function that are trying to goto a label in the first function, unless the source text has a second exec sql whenever .... This will not compile. Here’s the setup:          

    func1( )    {

         exec sql whenever sqlerror goto handle_error;

         ...        <OK goto’s generated here>

        handle_error: do something

    }

    func2( )    {

    .  .  .      <NO GOOD goto’s generated here> Can’t “goto handle_error” in func1!

    }

The whenever statement is handled by the precompiler, which puts in tests after all runtime calls and doesn't care about what function it's in (it doesn't even KNOW).

One fix to above problem: put another exec sql whenever sqlerror goto <label in func2>.

Another fix: put a label “handle_error” in func2. These labels are scoped to their functions, so it’s OK to reuse a label name in different functions.

There are also a number of other conditions that a Whenever statement can test for and actions it can take.  General form of Whenever statement:

    exec sql whenever <condition> <action>

Conditions. (pg. 272-273)

    o sqlerror     Tests if Error Occurred

    o not found Tests if no data affected by Fetch, Select, Update, etc.

    o sqlwarning             Tests if Unusual result occurred (maybe not error)

Actions

    o stop           Disconnect, commit, terminate execution of program (not great)

    o continue   Do nothing, default action

    o goto label Go to labeled statement in program

(NEXT IS AVAILABLE IN ORACLE ONLY)

    o do func_call            Call named function; but doesn’t help to divert subsequent execution, so not that useful.

Continues doing this this until overridden by a whenever with a different action for the same condition:

    whenever sqlerror continue;     /* now will do nothing */

But note in example above, there must be a label  handle_error in all these functions.  A call to a handle_error function would save a lot of code.

Question.  What happens here, where only two whenever statements are listed explicitly?

    main( )

    {

         exec sql whenever sqlerror stop;

         .  .  .

         goto label1;  /* for this discussion only: don’t use goto in hw code! */

         .  .  .

         exec sql whenever sqlerror continue;

label1: exec sql update agents set percent = percent+1;

If we arrive at label1 by means of the goto statement, which whenever statement will be in force for condition sqlerror?  E.g., what if mispell columname percent? 

Answer is:  continue. All that matters is what is most recent in the source text, not in the execution. In fact “exec sql whenever...” has no runtime effect at all!

Look at Java code: error handling is done by exceptions. Specifically, every SQL error causes a SQLException to be thrown, and this is a checked exception. That means that it needs to show in method signatures if the method can throw that exception, for example, in Ex512.java, we see that getAgentDollars can throw SQLException.

This method has a try {  } but no catch, so any generated SQLException will propagate up to the caller. Why is it set up this way?

Because a SQLException is almost always a “fatal error” such as no such table, or no such column, or no such row.  Our app code in getAgentDollars is much simpler if we don’t bother to handle SQLException. Let the main code deal with the problem, since it’s fatal anyway.

Of course occasionally we do need to locally handle a SQLException. For example, if we want to create a table if it isn’t there already. We just go ahead and try to create it, and if it’s already there, handle the resulting SQLException.

So why bother with try {  } at all if there’s no catch?

Because finally { } is useful here: code in here is executed no matter what, i.e., if there’s an exception or not.  We can close the Statement object. This doesn’t actually matter for this tiny program, because everything is cleaned up at program exit. But in a big program, it’s good to clean up SQL objects as you go.

But what about Java garbage collection—doesn’t that clean up for us? Yes, for ordinary memory objects. But SQL objects involve additional system resources that need explicit cleaning up, or at least get cleaned up faster if we do the close().