[ Home ] [ Up ] [ Relational Databases ] [ Structured Query Language (SQL) ] [ What is JDBC? ] [ Connecting to Databases ] [ SQL Errors and Warnings ] [ Accessing Databases ] [ Creating and Populating a Database ] [ Transactions ] [ Prepared and Callable Statements ] [ Using Metadata ]

When you use SQL in a program, you need to be able to tell when something goes wrong -- that is when an error occurs.

SQLCODE

The SQL-89 standard defined a special status parameter, SQLCODE, which you declare in your program as a variable (in C, of type long). When you execute SQL statements, you specify (explicitly or implicitly, depending on how you are invoking the SQL statements) that SQLCODE is to be used to contain the return status code. In the SQL standard, the only defined values for SQLCODE were:

• 0 -- successful completion
• +100 -- no data (meaning that the statement did not incur errors, but found no [more] rows on which to operate)
• < 0 -- error occurred

The specific negative values were left up to each SQL vendor's implementation (when the SQL-89 standard was written, the vendors already had their own sets of incompatible values for SQLCODE, so the standard could not make their implementations obsolete, nor could it favor one vendor over others).

SQLSTATE

SQL-92 tried to fix this situation, but could not do away with SQLCODE, nor could it define new SQLCODE values, for fear of making existing applications obsolete. So it deprecated SQLCODE, and introduced a second status parameter, SQLSTATE, which has many predefined values, plus lots of room for vendor-specific values. The rules about the contents of SQLState are as follows:

• It is a 5-character string
• May only include upper case characters (A-Z) and digits (0-9)
• The first two characters are called the class code.
• The last three characters are called the subclass code.
• Any class code that starts with the characters A-H or 0-4 indicates an SQLSTATE value defined by the SQL standard, or by other standards related to the SQL standard. For those class codes, any subclass code starting with the same character is also defined by the standard.
• Any class code that starts with the characters I-Z or 5-9 is implementor-defined, and all subclass codes (except for 000) are also implementor-defined.
• Subclass code 000 always mean no subclass code defined.

There are many defined values for SQLSTATE (see a SQL text or a vendor manual for details), but some interesting ones are:

• 00000 -- successful completion
• 01xxx -- warning values

The SQL Diagnostics Area

However, just getting a single status value (SQLCODE or SQLSTATE, or both) back from the execution of a SQL statement doesn't give you much information. For one thing, it is possible for more than one error to occur during the execution of a SQL statement, and you'd like to be able to obtain all the necessary information about the errors that occurred..

The SQL standard introduced the concept of a SQL diagnostics area. The diagnostics area is structured as follows:

• A header area, followed by
• One or more detail areas (an array)

The header area contains:

The detail area contains the RETURNED_SQLSTATE for the SQL exception, plus much more information, such as the name of the constraint that was violated (if the error was a constraint violation), catalog name, schema name, table, name, column name, etc. For more details, refer to a SQL text or vendor manual.

The SQL standard invented some SQL syntax to allow access to the information in the SQL diagnostics area:

GET DIAGNOSTICS target = item [ , target = item ]...

which obtains information from the header area, and:

GET DIAGNOSTICS EXCEPTION number target = item [ , target = item ]...

which obtains information from a detail area.

Each item is the name of a field (such as ROW_COUNT) in the diagnostics area header or specified detail area.

SQL Warnings

When you perform a JDBC operationt, and a SQL error occurs, you can assume that the SQL operation did not execute correctly. In addition to this, it is possible for an operation to succeed, but indicate a warning condition.

Back in SQL-89 days, when SQLCODE ruled, the concept of a SQL warning translated into a positive value in SQLCODE. One such positive value was 100, which indicated no [more] data. When you are executing in a loop, reading data from a database, you better not ignore the no more data warning, unless you want to end up in an endless loop! So note that warnings are not to be completely ignored...

SQLSTATE values starting with a class code of 01 are warnings.

SQLExceptions

In JDBC, when an operation produces an SQL error, an SQLException is thrown. SQLException extends from Exception, and thus is a checked exception, so you typically will have to deal with it via a try/catch block.

There are two interesting methods in class SQLException:

public String getSQLState()

which returns the value of SQLSTATE, and:

public int getErrorCode()

which returns the vendor-specific error code.

Just as a SQL error can represent several error conditions, a SQLException can also represent several SQL Exceptions. From the thrown SQLException, you can find the next related SQLException using the method:

public SQLException getNextException()

and so on. The SQLExceptions are chained, one to the next.

So, when you catch a SQLException, the code ought to look something like:

        try
        {
           // ...
        }
        catch(SQLException ex)
        {
            System.err.println("\n--- SQLException caught ---\n");
            for ( ;ex != null; ex = ex.getNextException())
            {
                System.err.println("Message:   " + ex.getMessage());
                System.err.println("SQLState:  " + ex.getSQLState());
                System.err.println("ErrorCode: " + ex.getErrorCode());
                ex.printStackTrace(System.err);
                System.err.println();
            }
            
	    // ...
        }

SQLWarnings

When a JDBC operation succeeds, but with some conditional state, then a SQLWarning is associated with the execution, but is not thrown. SQLWarning is a class that extends from SQLException.

If SQLWarnings are not thrown, how do you determine if any warnings occurred, and if so where do you find them?
Answer: You have to explicitly test for them!

Connection, ResultSet and Statement each has a method getWarnings(), which returns the first in a set of SQLWarnings. (Like SQLExceptions, multiple SQLWarnings can be chained together.) If there are no SQLWarnings, getWarnings() returns null.

So, when you perform some JDBC operation that can produce warnings, you need to write code something like:

       m_con = getConnection();
       SQLWarning warn = m_con.getWarnings();
       if (warn != null)
       {
           printWarnings(warn);
           // ...
       }

    // ...

    private static void printWarnings(SQLWarning warn)
    {
        System.err.println("\n--- SQLWarning ---\n");
        for ( ;warn != null; warn = warn.getNextWarning())
        {
            System.err.println("Message:   " + warn.getMessage());
            System.err.println("SQLState:  " + warn.getSQLState());
            System.err.println("ErrorCode: " + warn.getErrorCode());
            warn.printStackTrace(System.err);
            System.err.println();
        }
    }

Data Truncation

SQL warnings are not terribly common. By far the most common of SQL warnings are data truncation warnings, when JDBC or SQL unexpected truncates a data value.

There is a special class, DataTruncation (a subclass of SQLWarning) to represent this.

When JDBC unexpectedly truncates a data value, it:

• reports a DataTruncation warning (on reads)
• throws a DataTruncation exception (on writes).

The SQLSTATE value for all DataTruncation warnings is "01004".

To help you determine the source of the problem, DataTruncation includes the following methods:

• public int getIndex()

Get the index of the column or parameter that was truncated.
This may be -1 if the column or parameter index is unknown, in which case the "parameter" and "read" fields should be ignored.

• public boolean getParameter()

Returns true if the value was a parameter; false if it was a column value.

• public boolean getRead()

Returns true if the value was truncated when read from the database; false if the data was truncated on a write.

• public int getDataSize()

Get the number of bytes of data that should have been transferred. This number may be approximate if data conversions were being performed. The value may be "-1" if the size is unknown.

• public int getTransferSize()

Get the number of bytes of data actually transferred. The value may be "-1" if the size is unknown.