how to retrieve sql exceptions

Question Posted / sudheer

2 Answers
6186 Views
Bosch, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to retrieve sql exceptions..

Answer / pradeep

SQLJ/JDBC Exception-Handling Requirements
Because SQLJ executable statements result in JDBC calls
through sqlj.runtime, and JDBC requires SQL exceptions to
be caught or thrown, SQLJ also requires SQL exceptions to
be caught or thrown in any block containing SQLJ executable
statements. Your source code will generate errors during
compilation if you do not include appropriate exception-
handling.

Handling SQL exceptions requires the java.sql.SQLException
class. You can either import it as follows, or fully
qualify its name whenever you need it:

import java.sql.SQLException; // or optionally
java.sql.*

Example: Exception Handling
This example demonstrates the kind of basic exception-
handling that is required of SQLJ applications, with a main
method with a try/catch block, and another method which is
called from main and throws exceptions back to main when
they are encountered.

/* Import SQLExceptions class. The SQLException comes from
JDBC. Executable #sql clauses result in calls to JDBC,
so methods
containing executable #sql clauses must either catch or
throw
SQLException.
*/
import java.sql.SQLException ;
import oracle.sqlj.runtime.Oracle;

// iterator for the select

#sql iterator MyIter (String ITEM_NAME);

class TestInstallSQLJ
{
//Main method
public static void main (String args[])
{
try {
/* if you're using a non-Oracle JDBC Driver, add a
call here to
DriverManager.registerDriver() to register your
Driver
*/

// set the default connection to the URL, user, and
password
// specified in your connect.properties file
Oracle.connect
(TestInstallSQLJ.class, "connect.properties");

TestInstallSQLJ ti = new TestInstallSQLJ();
ti.runExample();
} catch (SQLException e) {
System.err.println("Error running the example: " + e);
}

} //End of method main

//Method that runs the example
void runExample() throws SQLException
{
//Issue SQL command to clear the SALES table
#sql { DELETE FROM SALES };
#sql { INSERT INTO SALES(ITEM_NAME) VALUES ('Hello,
SQLJ!')};

MyIter iter;
#sql iter = { SELECT ITEM_NAME FROM SALES };

while (iter.next()) {
System.out.println(iter.ITEM_NAME());
}
}
}

Processing Exceptions
This section discusses ways to process and interpret
exceptions in your SQLJ application. During runtime,
exceptions may come from any of the following:

SQLJ runtime

JDBC driver

RDBMS

Printing Error Text
The example in the previous section showed how to catch SQL
exceptions and output the error messages, which is repeated
again here:

...
try {
...
} catch (SQLException e) {
System.err.println("Error running the example: " +
e);
}
...

This will print the error text from the SQLException
object.

For error messages from the SQLJ runtime, see "Runtime
Messages". You can also retrieve SQL state information, as
described below.

For error messages from the Oracle JDBC driver, see the
Oracle8i JDBC Developer's Guide and Reference.

For error messages from the Oracle RDBMS, which will be
preceded by the prefix ORA-xxxxx, where xxxxx is a five-
digit error code, see the Oracle8i Error Messages
reference.

Retrieving SQL States and Error Codes
The java.sql.SQLException class and subclasses include the
methods getSQLState(), getErrorCode(), and getMessage().
Depending on where the exception came from and how error
conditions are implemented there, these methods may provide
additional information as follows.

For exceptions from the Oracle SQLJ runtime:

getSQLState() returns a five-digit string containing the
SQL state.

getErrorCode() returns 0 (no meaningful information).

getMessage() returns an error message (with no prefix).

For exceptions from the Oracle JDBC drivers:

getSQLState() returns null (no meaningful information).

getErrorCode() returns 0 (no meaningful information).

getMessage() returns an error message (with no prefix).

For exceptions from the RDBMS:

getSQLState() returns an empty string (no meaningful
information).

getErrorCode() returns the Oracle error code, which is the
xxxxx portion of the ORA-xxxxx prefix. (For example, this
would return 942 for the message prefixed by ORA-00942.)

getMessage() returns an error message, including the ORA-
xxxxx prefix.

For example, you can use the following error processing.
This prints the error message as in the preceding example,
but also checks the SQL state.

...
try {
...
} catch (SQLException e) {
System.err.println("Error running the example: " +
e);
String sqlState = e.getSQLState();
System.err.println("SQL state = " + sqlState);
}
...

SQL states and error messages for SQLJ runtime errors are
documented in "Runtime Messages".

Using SQLException Subclasses
For more specific error-checking, use any available and
appropriate subclasses of the java.sql.SQLException class.

SQLJ provides one such subclass, the
sqlj.runtime.NullException class, which you can catch in
situations where a null value might be returned into a Java
primitive variable. (Java primitives cannot handle nulls.)

When you use a SQLException subclass, catch the subclass
exception first, before catching a SQLException, as in the
following example:

...
try {
...
} catch (SQLNullException ne) {
System.err.println("Null value encountered: " + ne); }
catch (SQLException e) {
System.err.println("Error running the example: " +
e); }
...

This is because a subclass exception can also be caught as
a SQLException. If you catch SQLException first, then
execution would not drop through for any special processing
you want to use for the subclass exception

Is This Answer Correct ?

0 Yes

0 No

how to retrieve sql exceptions..

Answer / mukundvishy

Hashtable mapTable = new Hashtable();
Connection sqlCon = null ;
PreparedStatement pStmnt = null ;
ResultSet rSet = null;
try
{
// getConnection() method below should get connection from
pool etc.
sqlCon = getConnection();
pStmnt = sqlCon.prepareStatement( "SELECT * from tab" );
rSet = pStmnt.executeQuery();
while( rSet.next() )
{
mapTable.put( rSet.getString(1), rSet.getString(2));
}

}
catch( SQLException ex )
{
ex.printStackTrace();
}
finally
{
// The below method should close all resources peacefully
closeResources( sqlCon, pStmnt, rSet );
}

Is This Answer Correct ?

0 Yes

0 No

Post New Answer