If you are talking about something like the CREATE EXCEPTION statement in Interbase then have a look at the funky CREATE MESSAGE statement in SQL Anywhere.
Here is a bad copy-and-paste from my book (if you don't like the formatting, you know what to do :)...
9.5.2 RAISERROR and CREATE MESSAGE
When compared with the SIGNAL and RESIGNAL statements, the
RAISERROR statement is quite polite; SIGNAL rudely interrupts processing
and immediately transfers control to an exception handler, whereas
RAISERROR simply records some diagnostic information and carries on with
the next statement. Later SQL statements may check this diagnostic information
and take action if it’s been filled in, but that’s entirely optional; in most cases
this information is simply passed back to the client application.
The RAISERROR statement comes in four formats:
<raiserror> ::= RAISERROR <message_number>
| RAISERROR <message_number> <message_text>
| RAISERROR <message_number> <message_text> "," <substitution_list>
| RAISERROR <high_message_number> "," <substitution_list>
<message_number> ::= integer literal in the range 17000 to 99999
| <identifier> of an integer variable in the same range
<message_text> ::= <simple_expression> -- which may contain %nn! placeholders
<simple_expression> ::= see <simple_expression> in Chapter 3, “Selecting”
-- not a subquery and does not start with IF or CASE
<substitution_list> ::= { <substitution_value> "," } <substitution_value>
<substitution_value> ::= <simple_expression>
<high_message_number> ::= integer literal in the range 20000 to 99999
| <identifier> of an integer variable in the same range
The first format of RAISERROR just specifies a message number in the range
17000 to 99999; this number may be an integer literal or a variable, but it can’t
be an expression. Here is an example of a simple RAISERROR statement:
RAISERROR 17000;
Figure 9-1 shows the dialog box that appears when that RAISERROR is executed
in ISQL; the message “RAISERROR executed:” is displayed but no other
message text is included.
If the message number is in the high range of 20000 to 99999, then the
CREATE MESSAGE statement may be used ahead of time to store a text message
that will be automatically displayed by RAISERROR. The CREATE
MESSAGE statement inserts a row in the SQL Anywhere catalog table called
SYSUSERMESSAGES.
<create_message> ::= CREATE MESSAGE <high_message_number> AS <message_literal>
<message_literal> ::= string literal up to 255 bytes in length
Here is an example of a CREATE MESSAGE statement, followed by a
SELECT to show where the message text is stored:
CREATE MESSAGE 20000 AS 'Demonstration message.';
SELECT *
FROM SYSUSERMESSAGES
WHERE error = 20000;
Here is what the resulting row in SYSUSERMESSAGES looks like:
error uid description langid
===== === ======================== ======
20000 1 'Demonstration message.' 0
Tip: Use the SET TEMPORARY OPTION ON_ERROR = 'CONTINUE' statement
to temporarily change the special ISQL ON_ERROR option when using ISQL to
test SQL scripts containing RAISERROR statements. However, don’t make that
change permanent because it may cause real errors to go unnoticed.
Here is an example of a RAISERROR statement that will display the message
text stored in SYSUSERMESSAGES; the special ISQL option ON_ERROR has
been temporarily set to 'CONTINUE' to let ISQL carry on after a RAISERROR
is executed without popping up a dialog box:
SET TEMPORARY OPTION ON_ERROR = 'CONTINUE';
RAISERROR 20000;
Here is what appears in the Messages tab of the ISQL Results pane when the
RAISERROR statement is executed; note that the message text “Demonstration
message.” has automatically appeared:
Line 1, column 1
Could not execute statement.
[Sybase][ODBC Driver][Adaptive Server Anywhere]
RAISERROR executed: Demonstration message.
(Continuing after error)
The second format of the RAISERROR statement explicitly provides the message
text as well as the message number; in this case the specified message text
will override anything stored in the SYSUSERMESSAGES table. Here is an
example using the same message number as above but with different message
text; note that there is no comma between the message number and the message
text:
RAISERROR 20000 'Different message.';
This time, ISQL displays:
RAISERROR executed: Different message.
The third format of the RAISERROR statement uses a message text parameter
containing special numbered placeholders of the form %n!, and the message
text is followed by a comma-separated list of values to be substituted for the
placeholders; the first substitution value replaces the placeholder %1!, the second
value replaces %2!, and so on. Here is an example with two placeholders;
notice that commas must be used after the message text:
RAISERROR 20000 'Diagnostic number = %1!, string = "%2!".', 999, 'AAA';
Here’s what the resulting output looks like in ISQL:
RAISERROR executed: Diagnostic number = 999, string = "AAA".
The fourth format of the RAISERROR statement omits the message text but
includes the list of substitution values; in this case a CREATE MESSAGE statement
must be used to store a message containing the placeholders %n!. Here is
an example of a CREATE MESSAGE to store the same message text that was
used in the previous example, together with a RAISERROR that produces
exactly the same output as above; note that in this format a comma appears after
the message number in the RAISERROR:
CREATE MESSAGE 20000 AS 'Diagnostic number = %1!, string = "%2!".';
RAISERROR 20000, 999, 'AAA';
The message text stored in SYSUSERMESSAGES is limited to 255 bytes.
However, SQL Anywhere will only include 233 characters after the prefix
“RAISERROR executed:” when the message is displayed or returned to an
application program, and that limit applies to the message after any placeholders
have been replaced.
After a RAISERROR statement has been executed, SQL processing continues
normally; the message number is available to other SQL statements in the
built-in connection-level variable @@ERROR and the message text is available
by calling the built-in function ERRORMSG(). BEGIN block exception handlers
won’t get executed; if the SQL code needs to handle errors flagged by the
RAISERROR statement, then the @@ERROR variable must be explicitly
checked.
Tip: Do not change the default setting of the option CONTINUE_AFTER_
RAISERROR; leave it set to 'ON' so that RAISERROR statements inside BEGIN
blocks will behave as described in this section.
Following is an example of two stored procedures and an outer BEGIN block.
The BEGIN block calls p_error2, p_error2 calls p_error1, and then p_error1
executes a RAISERROR statement; along the way the value of @@ERROR is
checked, and various MESSAGE statements display the progress in the database
console window. The RAISERROR statement uses variables for the message
number and the substitution values to show that all the diagnostic information
can be determined at execution time; the syntax even allows an expression to be
used for the message text in a RAISERROR statement.
CREATE PROCEDURE p_error1 ()
BEGIN
DECLARE @error INTEGER;
DECLARE @diagnostic_number INTEGER;
DECLARE @diagnostic_string VARCHAR ( 10 );
MESSAGE 'p_error1 before RAISERROR';
SET @error = 20000;
SET @diagnostic_number = 999;
SET @diagnostic_string = 'AAA';
RAISERROR @error
'Diagnostic number = %1!, string = "%2!".',
@diagnostic_number,
@diagnostic_string;
MESSAGE 'p_error1 after RAISERROR';
EXCEPTION
WHEN OTHERS THEN
MESSAGE STRING ( 'p_error1 exception handler' );
END;
CREATE PROCEDURE p_error2 ()
BEGIN
MESSAGE 'p_error2 before CALL p_error1';
CALL p_error1();
MESSAGE 'p_error2 after CALL p_error1';
IF @@ERROR <> 0 THEN
MESSAGE 'Error detected in p_error2...';
MESSAGE STRING ( ' @@ERROR = ', @@ERROR );
MESSAGE STRING ( ' ERRORMSG() = ', ERRORMSG() );
END IF;
EXCEPTION
WHEN OTHERS THEN
MESSAGE STRING ( 'p_error2 exception handler' );
END;
BEGIN
MESSAGE 'Before CALL p_error2';
330 Chapter 9: Protecting
CALL p_error2();
MESSAGE 'After CALL p_error2';
IF @@ERROR <> 0 THEN
MESSAGE 'Error detected in outer block...';
MESSAGE STRING ( ' @@ERROR = ', @@ERROR );
MESSAGE STRING ( ' ERRORMSG() = ', ERRORMSG() );
END IF;
EXCEPTION
WHEN OTHERS THEN
MESSAGE STRING ( 'Outer block exception handler' );
END;
Here is the output from the example above; note that execution continued normally
after each CALL and the RAISERROR, that the values of @@ERROR
and ERRORMSG() were preserved as execution proceeded, and that none of the
EXCEPTION handlers were executed:
p_error2 before CALL p_error1
p_error1 before RAISERROR
p_error1 after RAISERROR
p_error2 after CALL p_error1
Error detected in p_error2...
@@ERROR = 20000
ERRORMSG() = RAISERROR executed: Diagnostic number = 999, string = "AAA".
After CALL p_error2
Error detected in outer block...
@@ERROR = 20000
ERRORMSG() = RAISERROR executed: Diagnostic number = 999, string = "AAA".
Here is another example of a stored procedure called from a Java application;
the application is the same as the one shown earlier, CatchError.java, but the
stored procedure has been changed to execute a RAISERROR statement instead
of SIGNAL:
CREATE PROCEDURE p1()
RESULT (
key_1 INTEGER,
non_key_1 VARCHAR ( 100 ) )
BEGIN
DECLARE @error INTEGER;
DECLARE @diagnostic_number INTEGER;
DECLARE @diagnostic_string VARCHAR ( 10 );
MESSAGE 'p1 before SELECT';
SELECT key_1,
non_key_1
FROM t1
ORDER BY key_1;
MESSAGE 'p1 after SELECT';
SET @error = 20000;
SET @diagnostic_number = 999;
SET @diagnostic_string = 'AAA';
RAISERROR @error
'Diagnostic number = %1!, string = "%2!".',
@diagnostic_number,
@diagnostic_string;
MESSAGE 'p1after RAISERROR';
END;
The following output is displayed by the Java application shown earlier,
CatchError.java, when the new version of the procedure p1 has been stored in
the database. As before, the result set is followed by the output from the first
catch block, and the same value of SQLState is displayed: HY000 for “driverspecific
error.” This time, however, the SQL ErrorCode has been set to –20000,
which is the negative value of the message number specified in the
RAISERROR statement. Also, the full version of the RAISERROR message is
displayed, including the diagnostic data; this shows that RAISERROR is more
powerful than SIGNAL when it comes to passing information back to a client
application.
key_1 = 1, non_key_1 = 'AAA'
key_1 = 2, non_key_1 = 'BBB'
SQLState: HY000
ErrorCode: -20000
Message: [Sybase][ODBC Driver][Adaptive Server Anywhere]RAISERROR executed:
Diagnostic number = 999, string = "AAA".
Stack trace...
java.sql.SQLException: [Sybase][ODBC Driver][Adaptive Server Anywhere]RAISERROR
executed: Diagnostic number = 999, string = "AAA".
at ianywhere.ml.jdbcodbc.IStatement.getMoreResults(Native Method)
at CatchError.main(CatchError.java:27)
Here is the output that was displayed in the database console window when
CatchError.java called the new procedure p1. Unlike the previous test that used
a SIGNAL statement, execution of this procedure continued after the
RAISERROR statement was executed. However, the getMoreResults() call is
still required to force the statements following the SELECT to be executed at
all.
p1 before SELECT
CatchError.java after display
p1 after SELECT
p1 after RAISERROR
Here is an example of an EXCEPTION handler that can be used inside a
BEGIN block to turn exceptions into RAISERROR messages; this code has the
advantage that the original SQLCODE, SQLSTATE, and ERRORMSG() values
are all preserved inside the error message passed back to the client application:
CREATE PROCEDURE p1()
RESULT (
key_1 INTEGER,
non_key_1 VARCHAR ( 100 ) )
BEGIN
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
DECLARE error_99001 EXCEPTION FOR SQLSTATE '99001';
SIGNAL error_99001;
EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
RAISERROR 99999
'SQLCODE = %1!, SQLSTATE = %2!, ERRORMSG() = %3!',
@sqlcode, @sqlstate, @errormsg;
END;
Here is the output displayed by the Java application CatchError.java when it
calls the procedure shown above; now the original SQLSTATE '99001' is available
to the client application:
SQLState: HY000
ErrorCode: -99999
Message: [Sybase][ODBC Driver][Adaptive Server Anywhere]RAISERROR executed:
SQLCODE = -297, SQLSTATE = 99001, ERRORMSG() = User-defined exception signaled
This technique also works for built-in SQLSTATE exceptions; the error message
returned to the client contains all the original diagnostic information
available at the point of error. Here is the output displayed by the Java application
CatchError.java when the exception handler shown above is used inside a
procedure that raises a data conversion exception when it is called:
SQLState: HY000
ErrorCode: -99999
Message: [Sybase][ODBC Driver][Adaptive Server Anywhere]RAISERROR executed:
SQLCODE = -157, SQLSTATE = 53018, ERRORMSG() = Cannot convert xxx to a timestamp
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.