Handling Stored Program Errors in the Calling Application

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.

6.6.1. 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 the mysql extension.

 

mysqli

Commencing with PHP 5, a new interfacemysqliwas 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.

The mysqli and 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 the mysqli interface.

In Example 6-25, a simple stored procedureone without OUT parameters or result setsis executed on line 8. If the method call returns failure, we can examine various properties of the database connection object ($dbh in this example). $dbh->errno contains the MySQL error code, $dbh->error contains the error message, and $dbh->sqlstate contains the SQLSTATE code.

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

1 $dbh = new mysqli($hostname, $username, $password, $database);
2 /* check connection */
3 if (mysqli_connect_errno( )) {
4 printf("Connect failed: %s
", mysqli_connect_error( ));
5 exit( );
6 }
7
8 if ($dbh->query("call error_test_proc(1)")) /*execute stored procedure*/
9 {
10 printf("Stored procedure execution succeeded");
11 }
12 else // Stored procedure failed - show error
13 {
14 printf("

Stored procedure error: MySQL error %d (SQLSTATE %s) %s ", 15 $dbh->errno,$dbh->sqlstate,$dbh->error); 16 }

6.6.2. 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 objectssuch as database and statement handlesinclude 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 the SQLSTATE variable. However, the SQLSTATE variable 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 $dbh as either $dbh::err or $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
");
8 }
9 else
10 {
11 printf("Error executing stored procedure: MySQL error %d (SQLSTATE %s)
 %s
",
12 $dbh->err,$dbh->state,$dbh->errstr);
13 }

6.6.3. Java/JDBC

MySQL provides a Java JDBC 3.0 driverMySQL Connector/Jthat 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 a try block. If any of these statements causes a SQLException error, then the catch handler will be invoked to handle the error.

The catch handler has access to a SQLException object 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-standard SQLSTATE code

 

getMessage( )

Returns the full text of the error message

Example 6-27 shows an example of invoking a simple stored procedure that involves no OUT parameters or result sets. On line 8 we create a statement object, and on line 9 we use the execute method of that object to execute the stored procedure. If an error occurs, the catch block on line 11 is invoked, and the relevant methods of the SQLException object 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 }

6.6.4. 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 the try block, 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 the except block on line 11. The MySQLdb.Error object (aliased here as e) contains two elements: element 0 is the MySQL error code, and element 1 is the MySQL error message.

6.6.5. C# .NET

MySQL provides an ADO.NET connectorMySQL Connector/Netthat 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 a try block. If an error occurs for any of these statements, execution switches to the catch block, 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 a MySqlException (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 ;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 }

catch blocks have access to a MySQLException object; this object includes Message and Number properties, which contain the MySQL error message and error number, respectively.

6.6.6. 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 a MySqlException (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 ;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 = myConnection
18 myCommand.ExecuteNonQuery( )
19
20 Catch MyException As MySqlException
21 Console.WriteLine("Stored procedure error: MySQL code: " & _
22 MyException.Number & " " & _
22 MyException.Message)
23 End Try

Catch blocks have access to a MySQLException object; this object includes Message and Number properties, which contain the MySQL error message and error number, respectively.