Home arrow MySQL arrow Page 4 - Using the SIGNAL Statement for Error Handling

Handling Stored Program Errors in the Calling Application - MySQL

In this conclusion to a three-part series, you will learn about the SIGNAL statement and its uses for creating your own error statements. It is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Using the SIGNAL Statement for Error Handling
  2. Emulating the SIGNAL Statement
  3. Putting It All Together
  4. Handling Stored Program Errors in the Calling Application
  5. Conclusion
By: O'Reilly Media
Rating: starstarstarstarstar / 4
September 13, 2007

print this article



Throughout this chapter, we’ve often talked about “returning the error to the calling application.” In our examples we have used the MySQL command-line client to represent the calling application since this client is common to all environments and readers, and it allows you (and us) to quickly test out the stored program.

In production environments, however, the calling application will not be the MySQL command-line program, but possibly a web-based application using PHP, Perl, Java, Python, or .NET (C# or Visual Basic) to interface with the MySQL stored program. In Chapters 12 through 17, we look in detail at how to invoke stored programs from a variety of languages. We also cover various techniques for retrieving status and error messages from these languages. However, since we’re on the topic of error handling, let’s briefly look at how we can process errors generated by a stored program called from each of these languages.


PHP provides a variety of ways of interacting with MySQL. There are four major interfaces available:

PEAR (PHP Extension and Application Repository)

The PEAR repository includes a standard, database-independent module called PEAR DB that can be used to interface with almost any relational database.


PHP includes a MySQL-specific interface inventively called themysql extension.


Commencing with PHP 5, a new interface—mysqli—was introduced (according to the developer, the “i” stands for “any one of: improved, interface, ingenious, incompatible, or incomplete”). This interface provides better support for new features of MySQL.

PDO (PHP Data Objects)

PDO, a new interface with PHP 5.1, provides a PHP 5N compatible, object-oriented, database-independent interface.

Themysqliand PDO interfaces provide the best support for MySQL stored programs and other new features of MySQL 5.0.

In Chapter 13, we show in detail how to use stored programs with each of the major PHP interfaces and provide examples of both procedural and nonprocedural styles. For now, let’s look at a simple example showing how to process errors using the object-oriented variant of themysqliinterface.

In Example 6-25, a simple stored procedure—one withoutOUTparameters or result sets—is executed on line 8. If the method call returns failure, we can examine various properties of the database connection object ($dbhin this example).$dbh->errnocontains the MySQL error code, $dbh->errorcontains the error message, and$dbh-> sqlstatecontains theSQLSTATEcode.

Example 6-25. Error handling in the PHP 5 mysqli interface

1$dbh = new mysqli($hostname, $username, $password, $database);
2/* check connection */
3if (mysqli_connect_errno()) {
4   printf("Connect failed: %s\n", mysqli_connect_error());
5   exit();
8if ($dbh->query("call error_test_proc(1)")) /*execute stored
10 printf("Stored procedure execution succeeded");
12else // Stored procedure failed - show error
14   printf("<p>Stored procedure error: MySQL error %d (SQLSTATE
%s)\n %s\n",
15            $dbh->errno,$dbh->sqlstate,$dbh->error);


The Perl DBI interface provides a consistent interface to various relational databases. The error-handling techniques for Perl are very similar to those of PHP.

DBI objects—such as database and statement handles—include the following properties:

Contains the database-specific return code (in our
      case, the MySQL error code).

Contains the full message text.

   Contains theSQLSTATEvariable. However, the
  SQLSTATEvariable usually includes only a generic 
   success or failure code.

Each of these items can be referenced as a method or a property, so, for instance, you can reference the last MySQL error code for the connect handle$dbhas either$dbh::error$dbh->err.

Example 6-26 shows a simple Perl code fragment that executes a stored procedure and checks the error status. On line 5 we execute a simple stored procedure (one without parameters or result sets). If the stored procedure call fails, we interrogate the error methods from the database handle.

Example 6-26. Error handling in Perl DBI

1 $dbh = DBI->connect("DBI:mysql:$database:$host:$port",
2              "$user", "$password",
3             { PrintError => 0}) || die $DBI::errstr;
5 if ($dbh->do("call error_test_proc(1)"))
6 {
7    printf("Stored procedure execution succeeded\n");
8 }
9 else
11    printf("Error executing stored procedure: MySQL error %d
(SQLSTATE %s)\n %s\n",
12              $dbh->err,$dbh->state,$dbh->errstr);


MySQL provides a Java JDBC 3.0 driver—MySQL Connector/J—that allows Java programs to interact with a MySQL server.

Like most modern object-oriented languages, Java uses structured exception handling to allow for flexible and efficient interception and handling of runtime errors. Rather than check the error status of every database call, we enclose our JDBC statements within atryblock. If any of these statements causes aSQLExceptionerror, then thecatchhandler will be invoked to handle the error.

Thecatchhandler has access to aSQLExceptionobject that provides various methods and properties for diagnosing and interpreting the error. Of most interest to us are these three methods:

   Returns the MySQL-specific error code

   Returns the ANSI-standardSQLSTATEcode

   Returns the full text of the error message

Example 6-27 shows an example of invoking a simple stored procedure that involves noOUTparameters or result sets. On line 8 we create a statement object, and on line 9 we use theexecutemethod of that object to execute the stored procedure. If an error occurs, thecatch block on line 11 is invoked, and the relevant methods of theSQLExceptionobject are used to display the details of the error.

Example 6-27. Stored procedure error handling in Java/JDBC

1 try {
2    Class.forName("com.mysql.jdbc.Driver").newInstance();
4    String ConnectionString="jdbc:mysql://" + hostname + "/" +
database + "?user="+
5          username + "&password=" + password;
6    System.out.println(ConnectionString);7    Connection conn
= DriverManager.getConnection(ConnectionString);
8    Statement stmt=conn.createStatement();9    stmt.execute
("call error_test_proc(1)");
11 catch(SQLException SQLEx) {
12    System.out.println("MySQL error: "+SQLEx.getErrorCode()+
13           " SQLSTATE:" +SQLEx.getSQLState());
14     System.out.println(SQLEx.getMessage());


Python can connect to MySQL using the MySQLdb extension. This extension generates Python exceptions if any MySQL errors are raised during execution. We enclose our calls to MySQL in a try block and catch any errors in an except block.

Example 6-28 shows how we can connect to MySQL and execute a stored procedure in Python. Line 1 commences thetryblock, which contains our calls to MySQL. On line 2 we connect to MySQL. On line 7 we create a cursor (SQL statement handle), and on line 8 we execute a stored procedure call.

Example 6-28. Stored procedure error handling in Python

1    try:
2           conn = MySQLdb.connect (host = 'localhost',
3                        user = 'root',
4                        passwd = 'secret',5                        db = 'prod',
6                         port=3306)
7           cursor1=conn.cursor()
8          cursor1.execute("CALL error_test_proc()")
9          cursor1.close()
11   except MySQLdb.Error, e:
12          print "Mysql Error %d: %s" % (e.args[0], e.args

If any of these calls generates a MySQL error condition, we jump to theexceptblock on line 11. TheMySQLdb.Errorobject (aliased here ase) contains two elements: element 0 is the MySQL error code, and element 1 is the MySQL error message.


MySQL provides an ADO.NET connector—MySQL Connector/Net—that allows any .NET-compatible language to interact with a MySQL server.

In this chapter we provide a short example of handling stored procedure errors from a C# program. More details are provided in Chapter 17.

As in Java, C# provides an exception-handling model that relieves the developer of the necessity of checking for error conditions after every statement execution. Instead, commands to be executed are included within atryblock. If an error occurs for any of these statements, execution switches to thecatchblock, in which appropriate error handling can be implemented.

Example 6-29 shows an example of error handling for a simple stored procedure (one without output parameters or result sets) in C#. A statement object for the stored procedure is created on line 15, and the statement is executed on line 17. If aMySqlException(essentially any MySQL error) occurs, the error handler defined on line 19 is invoked.

Example 6-29. Error handling in C#/ADO.NET

1  MySqlConnection myConnection;
2 myConnection = new MySqlConnection();
3 myConnection.ConnectionString =
4                              ";user
5  try {
6     myConnection.Open();
7 }
8 catch (MySqlException MyException)      {
9     Console.WriteLine("Connection error: MySQL code:
10                     +" "+ MyException.Message);
11  }
13  try {
15    MySqlCommand myCommand = new MySqlCommand("call
",16          myConnection);
17          myCommand.ExecuteNonQuery(); 18  }
19  catch (MySqlException MyException)    {
20     Console.WriteLine("Stored procedure error: MySQL code: "
+ MyException.Number 21                    + " " +
22 }

catchblocks have access to aMySQLExceptionobject; this object includesMessageandNumber properties, which contain the MySQL error message and error number, respectively.

Visual Basic .NET

The process for handling stored program errors in Visual Basic .NET (VB.NET) is practically identical to that of C#.

Example 6-30 shows an example of error handling for a simple stored procedure (one without output parameters or result sets) in VB.NET. A statement object for the stored procedure is created on lines 16 and 17, and the statement is executed on line 18. If aMySqlException(essentially any MySQL error) occurs, the error handler defined in lines 20-24 is invoked.

Example 6-30. Stored procedure error handling in VB.NET

1 Dim myConnectionString As String = "Database=" & myDatabase & _
2      " ;Data Source=" & myHost & _
3      ";User Id=" & myUserId & ";Password=" & myPassword
5 Dim myConnection As New MySqlConnection(myConnectionString)
7 Try
8     myConnection.Open()
9  Catch MyException As MySqlException
10      Console.WriteLine("Connection error: MySQL code: " &
MyException.Number & _
11                     " " + MyException.Message)
12  End Try
14  Try
16     Dim myCommand As New MySqlCommand("call
17     myCommand.Connection = myConnection18     myCommand.ExecuteNonQuery()
20  Catch MyException As MySqlException
21       Console.WriteLine("Stored procedure error: MySQL code:
" & _
22               MyException.Number & " " & _
23               MyException.Message)
24  End Try

Catch blocks have access to aMySQLExceptionobject; this object includesMessageandNumber properties, which contain the MySQL error message and error number, respectively.

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: