jGuru: Handling SQLExceptions and SQLWarnings

Print-friendly Version

by

[Help | API Docs | Short Course| Exercises]

As sharp eyed programmers, Duane and Chrissie have noticed that EURun.java, the program built in Using executeUpdate(), sometimes reports a rows processed count that doesn't match expectations. Rather than waiting for someone to start yelling, they decide to improve the error reporting and include complete exception and warning handling.

To test the code, they create an EURun2Errors.properties file as follows:

#PropertiesResourceBundle for EURun2 Properties
#Testing for invalid entries
#Uncomment these one at a time to see each error condition
#1=DELETE FROM JJJJTee WHERE Entry = 97
#1=INSERT INTO JJJJTee VALUES (25, 'Rosa', 'Petite', 'Blue')
#1=UPDATE JJJJTee SET TColor = 'Black' WHERE TColor = 'Appetite'
#1=DROP TABLE IDontExist
#1=UPDATE JJJJTee SET TSize = 'Small Doppelganger' WHERE TSize = 'Small'
#1=UPDATE JJJJTee SET TSize = 'Small ' WHERE TSize = 'Small'
#1=DROP TSBLE BadSQL )

with each entry constructed to cause an expected "soft" or "hard" error condition. EURun2 will have the same requirements as the original and is invoked as java EURun EURun2Errors.

EURun2 contain two new methods:

  public void handleSQLExceptions( SQLException SQLe,
                                   String       s,
                                   String       sSQL )

and

  public void handleSQLWarnings( SQLWarning SQLw,
                                 String        s,
                                 String     sSQL )

In addition, the existing:

  public void reportSQLError( SQLException SQLe,
                              String       s )

will be renamed and take an additional argument:

  public void reportSQLExceptions( SQLException SQLe,
                                   String       s,
                                   String       sSQL )

This level of error checking is appropriate for production quality database modification programs. For query results in displays and reports, just checking for first level SQLExceptions is generally sufficient, although it is not much more work to include exception chaining.

Prerequisites

• Using executeUpdate()

Skeleton Code

• ConnectU.properties
• EURun2Errors.properties
• EURun2.java

Tasks

1. In doUpdate(), add code to get any Connection warnings, using the sqlw variable for the statement;

         stmt = con.createStatement();
   

   If warnings were retrieved, invoke handleSQLWarnings() method; Clear the warnings.

2. In doUpdate(), in the first catch( SQLException SQLe) block, add code to invoke handleSQLExceptions().

3. In doUpdate(), after the iProcessed = stmt.executeUpdate( sUpdate ) statement, add code to report if no rows were processed by an SQL statement - use iProcessed, otherwise increment a rows processed total - iProcessedCount.

4. In doUpdate(), immediately after the previous code, add code to get and handle any Statement warnings, using the sqlw variable.

5. In the following catch( SQLException SQLe) block, add code to invoke handleSQLExceptions().

6. Add code to report total rows processed in the finally block.

7. Add code in handleSQLExceptions() to invoke reportSQLExceptions() for each exception retrieved, using while( SQLe != null ) and SQLe = SQLe.getNextException(). After the first invocation, only SQLe and nulls should be passed to reportSQLExceptions().

8. Add code in handleSQLWarnings() to invoke reportSQLExceptions() for each warning retrieved, using while( SQLw != null ) and SQLw = SQLw.getNextWarning(). After the first invocation, only SQLw and nulls should be passed to reportSQLExceptions().

9. Insert code in reportSQLExceptions() for complete reporting. If sSQL was not null, get and report the native SQL; If s was not null, report the program-sent text; Get the SQLState and report all available SQLException information; If the SQLState is "01004", report all DataTruncation information.

Where help exists, the task numbers above are linked to the step-by-step help page.

Solution Source

• ConnectU.properties
• EURun2Errors.properties
• EURun2.java

Demonstration

When this program has been compiled and run, in addition to executing and reporting results for any properly constructed DDL or DML statement, it will provide complete reporting of SQLExceptions, SQLWarnings, and any statement that affected zero rows. Note that zero returns for DDL statements does not indicate a problem, but the program does not have knowledge of the type of statement being executed. Exercise for the reader?

The statements in the provided EURun2Errors.properties file cause intentional problems and should be uncommented and run one at a time. For example output, see Sample Error Test Outcomes.

Next Exercise

Exercises

Short Course

Copyright 1996-2000 jGuru.com. All Rights Reserved.