Handling Stored Program Errors in the Calling Application - MySQL

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

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.

mysql

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

mysqli

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();
6}
7
8if ($dbh->query("call error_test_proc(1)")) /*execute stored
procedure*/
9{
10 printf("Stored procedure execution succeeded");
11}
12else // Stored procedure failed - show error
13{
14   printf("<p>Stored procedure error: MySQL error %d (SQLSTATE
%s)\n %s\n",
15            $dbh->errno,$dbh->sqlstate,$dbh->error);
16}

Perl

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:

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

Errstr
   Contains the full message text.

State
   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;
4
5 if ($dbh->do("call error_test_proc(1)"))
6 {
7    printf("Stored procedure execution succeeded\n");
8 }
9 else
10{
11    printf("Error executing stored procedure: MySQL error %d
(SQLSTATE %s)\n %s\n",
12              $dbh->err,$dbh->state,$dbh->errstr);
13}

Java/JDBC

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:

getErrorCode()
   Returns the MySQL-specific error code

getSQLState()
   Returns the ANSI-standardSQLSTATEcode

getMessage()
   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();
3
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)");
10}
11 catch(SQLException SQLEx) {
12    System.out.println("MySQL error: "+SQLEx.getErrorCode()+
13           " SQLSTATE:" +SQLEx.getSQLState());
14     System.out.println(SQLEx.getMessage());
15}

Python

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()
10
11   except MySQLdb.Error, e:
12          print "Mysql Error %d: %s" % (e.args[0], e.args
[1])

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.

C# .NET

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 =
"database="+database+";server="+server+
4                              ";user
id="+user+";Password="+password;
5  try {
6     myConnection.Open();
7 }
8 catch (MySqlException MyException)      {
9     Console.WriteLine("Connection error: MySQL code:
"+MyException.Number
10                     +" "+ MyException.Message);
11  }
12
13  try {
14
15    MySqlCommand myCommand = new MySqlCommand("call
error_test_proc(1)",16          myConnection);
17          myCommand.ExecuteNonQuery(); 18  }
19  catch (MySqlException MyException)    {
20     Console.WriteLine("Stored procedure error: MySQL code: "
+ MyException.Number 21                    + " " +
MyException.Message);
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
4
5 Dim myConnection As New MySqlConnection(myConnectionString)
6
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
13
14  Try
15
16     Dim myCommand As New MySqlCommand("call
error_test_proc(1)")
17     myCommand.Connection = myConnection18     myCommand.ExecuteNonQuery()
19
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.