Progress
SQL-92
Guide and Reference


Handling Errors

Progress SQL-92 stored procedures use standard Java try catch constructs to process exceptions.

Any errors in SQL statement execution result in the creation of a DhSQLException class object. When Progress SQL detects an error in an SQL statement, it throws an exception. The stored procedure should use try/catch constructs to process such exceptions. The getDiagnostics method of the DhSQLException class object provides a mechanism to retrieve different details of the error.

The getDiagnostics method takes a single argument whose value specifies which error message detail it returns. See Table 5–4 for explanations of the getDiagnostics error handling options.

Table 5–4: getDiagnostics Error Handling Options 
Argument Value
Returns
RETURNED_SQLSTATE
The SQLSTATE returned by execution of the previous SQL statement
MESSAGE_TEXT
The condition indicated by RETURNED_SQLSTATE
CLASS_ORIGIN
Not currently used. Always returned null
SUBCLASS_ORIGIN
Not currently used. Always returned null

The error messages and the associated SQLSTATE and Progress SQL-92 error code values are documented in "Progress SQL-92 Reference Information."

EXAMPLE

This example shows an excerpt from a stored procedure that uses DhSQLException.getDiagnostics:

try
{
     SQLIStatement insert_cust = new SQLIStatement (
     "INSERT INTO customer VALUES (1,2) ");
}
catch (DhSQLException e)
{
     errstate = e.getDiagnostics (RETURNED_SQLSTATE) ;
     errmesg  = e.getDiagnostics (MESSAGE_TEXT) ;
          .
          .
          .
} 

Stored procedures can also throw their own exceptions by instantiating a DhSQLException object and throwing the object when the procedure detects an error in execution. The conditions under which the procedure throws the exception object are completely dependent on the procedure.

EXAMPLE

This example illustrates using the DhSQLException constructor to create an exception object called excep. It then throws the excep object under all conditions:

CREATE PROCEDURE sp1_02()
BEGIN
// raising exception 
     DhSQLException excep =
        new DhSQLException(666,new String("Entered the tst02 procedure"));
     if (true)
     throw excep;
END 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095