Introduction
Exception and error handling is an important feature in any robust enterprise application. A lot of developers misunderstand and not apply the underlying techniques. Therefore, the end user is confused and does not know what to do when an exception occurs. It's responsible of the developer to try to handle the more exceptions as he can in order for the data to be consistent as well as to free the end user to make decision related to the software application. In this article, I try to cover the key principles and the main techniques of exception and error handling with examples in SQL Server, Oracle and Microsoft .NET.
Exception and error handling in Microsoft SQL Server
First of all, it's remarkable to say about the new features of Microsoft SQL Server implementing error handling in an elegant way by using the TRY...CATCH statement.
In SQL Server, we deal with two types of errors: built-in error and user-defined error. Built-in errors are those raised by response to standard SQL Server system's errors. User-defined errors are those used by third-party applications to define customized errors to be used within the application. An error has properties such as an identifier, a severity and a text.
The severity ranges from 1 to 25, and we can find some categorization such as:
- 0-10. Indicates an informational messages.
- 11-16. Database system errors which can be corrected by the user. For example, errors that affect the integrity of the database.
- 17-19. Database system errors which need for the administrator attention.
- 20-25. Fatal errors such as hardware and software crashes.
The messages of these errors are stored in the sys.messages table. You can see the error messages defined in your system using the following query (see Listing 1).
select message_id, severity, is_event_logged, text
from sys.messages
Listing 1.
It's possible to add new user-defined message error to the former table using the sp_addmessage stored procedures. Let's define a message error indicating a prohibition to delete a table (see Listing 2).
use master;
go
exec sp_addmessage 100001,14,N'You need more privelege to delete the table %s';
go
Listing 2.
The first parameter indicates the message identifier whose value is between 50001 and 2147483647. The second parameter indicates the severity level, and the last parameter is the actual message text.
Now, let's supposed that we have detected that a user is trying to delete a very important table of our application, and then we send an error message using the command RAISERROR (see Listing 3).
raiserror(100001, 14, 1, N'admin_schema.important_table');
go
Listing 3.
The RAISERROR command is often used to return error message to business logic components of the enterprise application, for example, when the integrity of some entities is violated.
You can also raise an error message without defining the message (see Listing 4).
raiserror('You need more privelege to delete the table %s', 14, 1, N'admin_schema.important_table');
Listing 4.
Up to this point, we've seen how to define error message. Now, let's see how to handle the errors.
Prior to SQL Server 2005, error handling was done by checking the @@ERROR global variable after each statement was executed, and then using a GOTO statement (the worst of the practices) the logic was interrupted in order to go a centralized error-handling block of code (see Listing 5).
create table test(x int check(x>0));
go
begin tran
insert into test values(1);
if @@ERROR <> 0 goto Error_Handling_Code;
insert into test values(-1);
if @@ERROR <> 0 goto Error_Handling_Code;
insert into test values(2);
if @@ERROR <> 0 goto Error_Handling_Code;
commit tran;
Error_Handling_Code:
print 'Error occurred.';
rollback tran;
go
Listing 5.
Now, the TRY...CATCH statement can be used to capture execution errors in T-SQL code. The syntax is as follows (see Listing 6).
begin try
{sql_statement | statement_block}
end try
begin catch
{sql_statement | statement_block}
end catch
Listing 6.
If an error is encountered within the try block, then we go directly to the catch block without processing other statements in the try block. It's remarkable to notice that warning and informational messages (severity level less than 10) are not captured by the TRY...CATCH statement. You can also nest a TRY...CATCH statement inside other TRY...CATCH statement.
Let's rewrite the code in Listing 5 using the TRY...CATCH statement (see Listing 7).
create table test(x int check(x>0));
begin try
begin tran;
insert into test values(1);
insert into test values(-1);
insert into test values(2);
commit tran;
end try
begin catch
print 'Error ocurred. Message '+ERROR_MESSAGE();
rollback tran;
end catch
Listing 7.
It's remarkable to say that SQL Server by default does not rollback the changes to database objects once an exception occurs. It's your decision as developer to explicitly rollback the changes. Although if you set the XACT_ABORT option to ON, then transactions are rolled back in the event of any runtime error.
Exception and error handling in Oracle database
In Oracle, we can find a good mechanism to work with exceptions. There are two types of exceptions:
-
System exception. This exception is defined by Oracle and raised by the PL/SQL engine when an error is detected.
-
Programmer-defined exception. An exception specific to the application and raised for some events of the application.
In PL/SQL, we can catch and handle the exceptions using the exception section (which is optional) associated to PL/SQL blocks. This section is used to handle any error that occurs during the execution of PL/SQL code inside the block. It's a way to cleanly separate the error processing logic from your business logic. The syntax for the exception section is as follows (see Listing 8). The section can have as many WHEN statements as you like to handle specific exceptions; all other exceptions are handled by the WHEN OTHERS section.
EXCEPTION
WHEN exception_name
THEN
error_handling_code
[WHEN OTHERS default_exception_handling]
Listing 8.
Like the TRY...CATCH statement in T/SQL, the exception section in PL/SQL provides an event-driven model for processing errors, because no matter what exception is raised (an application or user-based), and then the processing of the business logic is halt, and the control is transferred to the exception section. The control does not return to that part of the block once you have finished handling the error.
Before an exception is raised, it must be defined. In Oracle, you can find thousands of built-in exceptions (assigning numbers and messages). The most commonly used exceptions are found in the STANDARD package of PL/SQL.
Because many problems, that you may encounter, are specific to your solution, then you must declare your own exceptions (user-defined exceptions) in the declaration section using the following syntax (see Listing 9).
exception_name EXCEPTION;
Listing 9.
If you want to initiate an exception, you must follow the syntax (see Listing 10).
RAISE exception_name;
Listing 10.
And if you want to handle this specific exception, you must use the WHEN clause as illustrated (Listing 11).
WHEN exception_name THEN
Listing 11.
In addition, developers can raise exceptions using the RAISE_APPLICATION_ERROR built-in procedure and passing an error number (between -20000 and 20999) and an error message. The main advantage of this procedure is to associate an error message with the exception. When this procedure is invoked the changes made to parameters (IN OUT and OUT) are reversed while changes made to database objects (using SQL statement such as INSERT, UPDATE, DELETE) will not be rolled back. You must execute an explicit ROLLBACK statement.
When an exception is caught, then you can use several built-in functions to analyze the errors:
-
SQLCODE. Returns the error code of the most recent exception. If there is no error, then it returns 0.
-
SQLERRM. Returns the error message for a particular error code. If SQLCODE is 0, then it returns "ORA-0000: normal, successful completion". You can see an example (see Listing 12).
-
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This function is new to Oracle 10g and returns a formatted string displaying the stack of the program and line number to the line on which the error is originated. Let's see an example (see Listing 13).
begin
dbms_output.put_line(SQLERRM(-1));
end;
/
Listing 12.
create or replace procedure proc1 is
begin
dbms_output.put_line('This is proc1');
raise no_data_found;
end;
/
create or replace procedure proc2 is
begin
dbms_output.put_line('This is proc2');
proc1;
end;
/
create or replace procedure proc3 is
begin
dbms_output.put_line('This is proc3');
proc2;
exception
when others
then
dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);
end;
/
set serveroutput on;
begin
dbms_output.put_line('Testing back trace');
proc3;
end;
/
Listing 13.
Now let's rewrite the business logic in Listing 7 using the exception handling semantics in PL/SQL (see Listing 14).
create table test(x int check(x>0));
/
begin
insert into test values(1);
insert into test values(-1);
insert into test values(2);
commit tran;
exception
when others
then
dbms_output.put_line('Error on your application logic');
rollback tran;
end;
Listing 14.
Error handling in applications
Sometimes, the developer must deal with exceptions in their applications. Now we're going to show how to catch errors and warning in ADO.NET in order to be shown in our client applications.
Let's see how we can do it. When an exception is raised using RAISERROR, if the severity value is less or equal to 10, then it's generated a warning not an exception. The warnings are caught in the InfoMessage event of the Connection object. If the severity value is greater than 10, then an exception is raised and this exception is caught using the TRY...CATCH...FINALLY mechanisms of C#.
To illustrate these concepts, let's create a Windows form, and add a StatusStrip control to the form. Then inside the StatusStrip control, let's add a ToolStripStatusLabel control to display any warning sent by the database system (in our case, SQL Server) to our application. Finally, let's add a button to invoke a stored procedure in the backend server which sends warning and exceptions. The errors are displayed using a DialogBox.
First of all, let's define a stored procedure which returns a warning (see Listing 15).
create procedure spTestProc
as
begin
raiserror(N'This is a warning', 10, 1);
end;
Listing 15.
Now, let's define the C# code of client application (see Listing 16).
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace ExceptionHandlingWinForms
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void statusStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection objConnection = new SqlConnection("Data Source=localhost;Initial Catalog=TestDB;Integrated Security=True"))
{
objConnection.InfoMessage += new SqlInfoMessageEventHandler(objConnection_InfoMessage);
try
{
SqlCommand objCmd = new SqlCommand();
objCmd.Connection = objConnection;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "spTestProc";
objConnection.Open();
objCmd.ExecuteNonQuery();
}
catch (SqlException objEx)
{
System.Windows.Forms.MessageBox.Show(objEx.Message, "SQL Server error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
objConnection.Close();
}
}
}
void objConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
this.m_tsLWarning.Text = "Warning: "+e.Message;
}
}
}
Listing 16.
When you execute the application and press the button, the result is as shown (Figure 1).
![exception1.gif]()
Figure 1.
Let's change the code of the stored procedure in order to return an exception (see Listing 17).
alter procedure spTestProc
as
begin
raiserror(N'This is an exception', 11, 1);
end;
Listing 17.
Now, let's execute the application again and see the results (as shown in Figure 2).
![exception2.gif]()
Figure 2.
You can apply the same business logic to Oracle client application. Let's define a procedure in PL/SQL which returns an application error message to the application (see Listing 18).
create or replace procedure spTestProc
as
begin
raise_application_error(-20010,'Sending an application error');
end;
/
Listing 18.
When you run your application, then you will receive and handle this error using TRY...CATCH...FINALLY mechanisms.
Conclusion.
In this article, I explained the principles of exception and error handling in enterprise application. In the first part, I talked about defining exception and handling this exception in relational database systems such as SQL Server and Oracle. The second part, I explained how you can catch warnings and exceptions, not handled in the server-side code, which must be handled by the application developer on the client side.