Using the SIGNAL Statement for Error Handling

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.

Creating Your Own Exceptions with the SIGNAL Statement

So far in this chapter, we have talked about how you can handle errors raised by MySQL as it executes SQL statements within the stored program. In addition to these system-raised exceptions, however, you will surely have to deal with errors that are specific to an application’s domain of requirements and rules. If that rule is violated in your code, you may want to raise your own error and communicate this problem back to the user. The SQL:2003 specification provides the SIGNAL statement for this purpose.

The SIGNAL statement allows you to raise your own error conditions. Unfortunately, at the time of writing, the SIGNAL statement is not implemented within the MySQL stored program language (it is currently scheduled for MySQL 5.2).

You can’t use the SIGNAL statement in MySQL 5.0, but we are going to describe it here, in case you are using a later version of MySQL in which the statement has been implemented. Visit this book’s web site (see the Preface for details) to check on the status of this and other enhancements to the MySQL stored program language.

So let’s say that we are creating a stored procedure to process employee date-of-birth changes, as shown in Example 6-16. Our company never employs people under the age of 16, so we put a check in the stored procedure to ensure that the updated date of birth is more than 16 years ago (the curdate() function returns the current timestamp).

Missing SQL:2003 Features

Example 6-16. Example stored procedure with date-of-birth validation

CREATE PROCEDURE sp_update_employee_dob
    (p_employee_id INT, p_dob DATE, OUT p_status varchar(30))
BEGIN
    IF DATE_SUB(curdate(), INTERVAL 16 YEAR) <p_dob THEN
         SET p_status=’Employee must be 16 years or older’;
    ELSE
         UPDATE employees
           
SET date_of_birth=p_dob
        
WHERE employee_id=p_employee_id;
        
SET p_status=’Ok’;
   
END IF;
END;

This implementation will work, but it has a few disadvantages. The most significant problem is that if the procedure is called from another program, the procedure will return success (at least, it will not raise an error) even if the update was actually rejected. Of course, the calling program could detect this by examining the p_status variable, but there is a good chance that the program will assume that the procedure succeeded since the procedure call itself does not raise an exception.

We have designed the procedure so that it depends on the diligence of the programmer calling the procedure to check the value of the returning status argument. It is all too tempting and easy to assume that everything went fine, since there was no error.

To illustrate, if we try to set an employee’s date of birth to the current date from the MySQL command line, everything seems OK:

  mysql> CALL sp_update_employee_dob(1,now(),@status);
 
Query OK, 0 rows affected (0.01 sec)

It is only if we examine the status variable that we realize that the update did not complete:

  mysql> SELECT @status;

  +————————————+
  | @status                            |
  +————————————+
 
| Employee must be 16 years or older |
 
+————————————+
 
1 row in set (0.00 sec)

This stored procedure would be more robust, and less likely to allow errors to slip by, if it actually raised an error condition when the date of birth was invalid. The ANSI SQL:2003 SIGNAL statement allows you to do this:

SIGNAL takes the following form:

  SIGNAL SQLSTATE sqlstate_code|condition_name [SET MESSAGE_TEXT=string_or_variable];

You can create your own SQLSTATE codes (there are some rules for the numbers you are allowed to use) or use an existing SQLSTATE code or named condition. When MySQL implements SIGNAL , you will probably be allowed to use a MySQL error code (within designated ranges) as well.

When the SIGNAL statement is executed, a database error condition is raised that acts in exactly the same way as an error that might be raised by an invalid SQL statement or a constraint violation. This error could be returned to the calling program or could be trapped by a handler in this or another stored program. If SIGNAL were available to us, we might write the employee date-of-birth birth procedure, as shown in Example 6-17.

Example 6-17. Using the SIGNAL statement (expected to be implemented in MySQL 5.2)

CREATE PROCEDURE sp_update_employee_dob
    (p_employee_id int, p_dob date)
BEGIN
   
DECLARE employee_is_too_young CONDITION FOR SQLSTATE ’99001′;

    IF DATE_SUB(curdate(), INTERVAL 16 YEAR) <P_DOB THEN
         SIGNAL employee_is_too_young
            SET MESSAGE_TEST=’Employee must be 16 years or older’;
    ELSE
        UPDATE employees
           SET date_of_birth=p_dob
         WHERE employee_id=p_employee_id;
    END IF;
END;

If we ran this new procedure from the MySQL command line (when MySQL implements SIGNAL ), we would expect the following output:

  mysql> CALL sp_update_employee(1,now());
  ERROR 90001 (99001): Employee must be 16 years or older

Using SIGNAL , we could make it completely obvious to the user or calling program that the stored program execution failed.

{mospagebreak title=Emulating the SIGNAL Statement}

The absence of the SIGNAL statement makes some stored program logic awkward, and in some cases demands that calling applications examine OUT variables, rather than SQL return codes, to check the results of some operations.

There is, however, a way to force an error to occur and pass some diagnostic information back to the calling application. You can, in other words, emulate SIGNAL in MySQL 5.0, but we warn you: this solution is not pretty!

Where we would otherwise want to use the SIGNAL statement to return an error to the calling application, we can instead issue a SQL statement that will fail—and fail in such a way that we can embed our error message within the standard error message.

Missing SQL:2003 Features

The best way to do this is to issue a SQL statement that attempts to reference a nonexistent table or column. The name of the nonexistent column or table can include the error message itself, which will be useful because the name of the column or table is included in the error message.

Example 6-18 shows how we can do this. We try to select a nonexistent column name from a table and we make the nonexistent column name comprise our error message. Note that in order for a string to be interpreted as a column name, it must be enclosed by backquotes (these are the quote characters normally found on your keyboard to the left of the 1 key).

Example 6-18. Using a nonexistent column name to force an error to the calling program

CREATE PROCEDURE sp_update_employee_dob2
    (p_employee_id INT, p_dob DATE)
BEGIN

    IF datediff(curdate(),p_dob)<(16*365) THEN
         UPDATE `Error: employee_is_too_young; Employee must be 16 years or older`
            SET x=1;
    ELSE
        UPDATE employees
           SET date_of_birth=p_dob
        WHERE employee_id=p_dob;
    END IF;
END;

If we try to run the stored procedure from the MySQL command line, passing in an invalid date of birth, we get a somewhat informative error message:

  MySQL> CALL sp_update_employee_dob2(2,now());

  ERROR 1054 (42S22): Unknown column ‘Error: employee_is_too_young; Employee must be 16
  years or older’ in ‘field list’

The error code is somewhat garbled, and the error code is not in itself accurate, but at least we have managed to signal to the calling application that the procedure did not execute successfully and we have at least provided some helpful information.

We can somewhat improve the reliability of our error handling—and also prepare for a future in which the SIGNAL statement is implemented—by creating a generic procedure to implement our SIGNAL workaround. Example 6-19 shows a procedure that accepts an error message and then constructs dynamic SQL that includes that message within an invalid table name error.

Example 6-19. Standard procedure to emulate SIGNAL

CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
BEGIN
  
SET @sql=CONCAT(‘UPDATE `’,
           
in_errortext,
           
‘` SET x=1′);
   
PREPARE my_signal_stmt FROM @sql;
  
EXECUTE my_signal_stmt;
  
DEALLOCATE PREPARE my_signal_stmt;
END$$

We could now implement our employee date-of-birth update routine to call this routine, as shown in Example 6-20.

Example 6-20. Using our SIGNAL emulation procedure to raise an error

CREATE PROCEDURE sp_update_employee_dob2(p_employee_id INT, p_dob DATE)

BEGIN

    IF datediff(curdate(),p_dob)<(16*365) THEN
         CALL my_signal(‘Error: employee_is_too_young; Employee must be 16
                         years or older’);
    ELSE
        UPDATE employees
           SET date_of_birth=p_dob
        WHERE employee_id=p_employee_id;
    END IF;
END$$

Not only does this routine result in cleaner code that is easier to maintain, but when MySQL does implement SIGNAL , we will only need to update our code in a single procedure.

{mospagebreak title=Putting It All Together} 

We have now covered in detail the error-handling features of MySQL. We’ll finish up this discussion by offering an example that puts all of these features together. We will take a simple stored procedure that contains no exception handling and apply the concepts from this chapter to ensure that it will not raise any unhandled exceptions for all problems that we can reasonably anticipate.

The example stored procedure creates a new departments row. It takes the names of the new department, the manager of the department, and the depar

tment’s location. It retrieves the appropriate employee_id from the employees table using the manager’s name. Example 6-21 shows the version of the stored procedure without exception handling.

Example 6-21. Stored procedure without error handling

CREATE PROCEDURE sp_add_department
      (p_department_name        VARCHAR(30),
       p_manager_surname        VARCHAR(30),
       p_manager_firstname      VARCHAR(30),
      
p_location               VARCHAR(30) ,
       out p_sqlcode            INT,
       out p_status_message     VARCHAR(100)) 
  
MODIFIES SQL DATA
BEGIN

    DECLARE l_manager_id        INT;
    DECLARE csr_mgr_id cursor for
         SELECT employee_id
           FROM employees
         WHERE surname=UPPER(p_manager_surname)
           AND firstname=UPPER(p_manager_firstname);

    OPEN csr_mgr_id;
    FETCH csr_mgr_id INTO l_manager_id;

    INSERT INTO departments (department_name,manager_id,location)
    VALUES(UPPER(p_department_name),l_manager_id,UPPER
(p_location));

    CLOSE csr_mgr_id;
END$$

This program reflects the typical development process for many of us: we concen trate on implementing the required functionality (the “positive”) and generally pay little attention to (or more likely, want to avoid thinking about) what could possibly go wrong. The end result is a stored program that contains no error handling.

So either before you write the program (ideally) or after the first iteration is done, you should sit down and list out all the errors that might be raised by MySQL when the program is run.

Here are several of the failure points of this stored procedure:

  • If the manager’s name is incorrect, we will fail to find a matching manager in the employees table. We will then attempt to insert a NULL value for the MANAGER_ID column, which will violate its NOT NULL constraint.
  • If the location argument does not match a location in the locations table, the foreign key constraint between the two tables will be violated.
  • If we specify a department_name that already exists, we will violate the unique constraint on the department_name .

The code in Example 6-22 demonstrates these failure scenarios.

Example 6-22. Some of the errors generated by a stored procedure without error handling

mysql> CALL sp_add_department
    (‘Optimizer Research’,'Yan’,'Bianca’,'Berkshire’,
@p_sqlcode,@p_status_message)

ERROR 1062 (23000): Duplicate entry ‘OPTIMIZER RESEARCH’
for key 2

mysql> CALL sp_add_department
    (‘Optimizer Research’,'Yan’,'Binca’,'Berkshire’,
@p_sqlcode,@p_status_message);

ERROR 1048 (23000): Column ‘MANAGER_ID’ cannot be null

mysql> CALL sp_add_department(‘Advanced
Research’,'Yan’,'Bianca’,'Bercshire’,@p_
sqlcode,@p_status_message)

ERROR 1216 (23000): Cannot add or update a child row: a
foreign key constraint fails

The good news is that MySQL detects these problems and will not allow bad data to be placed into the table. If this stored procedure will be called only by the host language, such as PHP or Java, we could declare ourselves done. If, on the other hand, this program might be called from another MySQL stored program, then we need to handle the errors and return status information so that the calling stored program can take appropriate action. Example 6-23 shows a version of the stored procedure that handles all the errors shown in Example 6-22.

Example 6-23. Stored procedure with error handling

1 CREATE PROCEDURE sp_add_department 2
2     (p_department_name       VARCHAR(30),
3      p_manager_surname       VARCHAR(30),
4      p_manager_firstname     VARCHAR(30),
5      p_location              VARCHAR(30),
6      OUT p_sqlcode           INT,
7      OUT p_status_message    VARCHAR(100))
8 BEGIN
9
10 /* START Declare Conditions * /
1 1
12  DECLARE duplicate_key CONDITION FOR 1062 ;
13  DECLARE foreign_key_violated CONDITION FOR 1216;
1 4
15 /* END Declare Conditions * /
1 6
17 /* START Declare variables and cursors * / 1 8
19    DECLARE l_manager_id     INT ;
2 0
21      DECLARE csr_mgr_id CURSOR FO R
22      SELECT employee_id
23        FROM employees
24      WHERE surname=UPPER(p_manager_surname)
25         AND firstname=UPPER(p_manager_firstname);
2 6
27  /* END Declare variables and cursors * / 2 8
29   /* START Declare Exception Handlers * /
3 0
31   DECLARE CONTINUE HANDLER FOR duplicate_ke y
32       BEGIN
33          SET p_sqlcode=1052;
34          SET p_status_message=’Duplicate key error’;
35       END;
3 6
37     DECLARE CONTINUE HANDLER FOR foreign_key_violate d
38     BEGIN
39       SET p_sqlcode=1216;
40       SET p_status_message=’Foreign key violated’;
41     END;
4 2
43      DECLARE CONTINUE HANDLER FOR not FOUN D
44       BEGIN
45        SET p_sqlcode=1329;
46         SET p_status_message=’No record found’;
47       END;
4 8
49   /* END Declare Exception Handlers * /
5 0
51   /* START Execution * /
5 2
53    SET p_sqlcode=0 ;
54    OPEN csr_mgr_id;
55    FETCH csr_mgr_id INTO l_manager_id;
5 6
57    IF p_sqlcode<>0 THEN      /* Failed to get manager id* /
58      SET p_status_message=CONCAT(p_status_message,’ when
fetching manager id’);
59    ELSE
60                          /* Got manager id, we can try and
insert */
61      INSERT INTO departments
(department_name,manager_id,location)
62      VALUES(UPPER(p_department_name),l_manager_id,UPPER
(p_location));
63      IF p_sqlcode<>0 THEN/* Failed to insert new department */
64        SET p_status_message=CONCAT(p_status_message,
65                             ‘ when inserting new department’);
66      END IF;
67     END IF;
6 8
69    CLOSE csr_mgr_id ;
7 0
71 / * END Execution * /
7 2
73 EN D

Let’s go through Example 6-23 and review the error-handling code we have added.

Line(s)

Significance

12 and 13

Create condition declarations for duplicate key (1062) and foreign key (1216) errors. As we noted earlier, these declarations are not strictly necessary, but they improve the readability of the condition handlers we will declare later.

31-48

Define handlers for each of the exceptions we think might occur. The condition names match those we defined in lines 10 and 11. We didn’t have to create a NOTFOUNDcondition, since this is a predefined condition name. Each handler sets an appropriate value for the output status variables p_sqlcodeand p_status_message.

Line(s) Significance
57 On this line we check the value of the p_sqlcode variable following our fetch from the cursor that retrieves the manager’s employee_id . If p_sqlcode is not 0, then we know that one of our exception handlers has fired. We add some context information to the message—identifying the statement we were executing—and avoid attempting to execute the insert into the departments table.
53 Check the value of the p_sqlcode variable following our insert operation. Again, if the value is nonzero, we know that an error has occurred, and we add some context information to the error message. At line 53, we don’t know what error has occurred—it could be either the foreign key or the unique index constraint. The handler itself controls the error message returned to the user, and so we could add handling for more error conditions by adding additional handlers without having to amend this section of code.

Running the stored procedure from the MySQL command line shows us that all the exceptions are now correctly handled. Example 6-24 shows the output generated by various invalid inputs.

Example 6-24. Output from stored procedure with exception handling

mysql> CALL sp_add_department2(‘Optimizer
Research’,'Yan’,'Bianca’,'Berkshire’,
@p_sqlcode,@p_status_message)

Query OK, 0 rows affected (0.17 sec)

mysql> SELECT @p_sqlcode,@p_status_message

+————+—————————–+ | @p_sqlcode | @p_status_message           |
+————+—————————–+ | 1052       | Duplicate key error when inserting new
department                   |
+————+—————————–+ 1 row in set (0.00
sec)

mysql> CALL sp_add_department2(‘Optimizer
Research’,'Yan’,'Binca’,'Berkshire’,
@p_sqlcode,@p_status_message)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p_sqlcode,@p_status_message

+————+—————————–+ | @p_sqlcode | @p_status_message           |
+————+—————————–+ | 1329       | No
record found when fetching manager id                                 | +————+————–
—————+ 1 row in set (0.00 sec)

mysql> call sp_add_department2(‘Advanced
Research’,'Yan’,'Bianca’,'Bercshire’,
@p_sqlcode,@p_status_message)

Query OK, 0 rows affected (0.12 sec)

mysql> SELECT @p_sqlcode,@p_status_message

+————+—————————–+ | @p_sqlcode |
@p_status_message           |
+————+—————————–+ | 1216       |
Foreign key violated when inserting new
department                   | +————+——————–
———+ 1 row in set (0.00 sec)

{mospagebreak title=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.

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 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.

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 procedure—one without OUT parameters or result sets—is 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: %sn", 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("<p>Stored procedure error: MySQL error %d (SQLSTATE
%s)n %sn",
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 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 succeededn");
8  }
9  else
10 {
11     printf("Error executing stored procedure: MySQL error %d
(SQLSTATE %s)n %sn",
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 han dling 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 }

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.

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 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
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   }
1 2
13  try {
1 4
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.

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 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 = myConnection 18      myCommand.ExecuteNonQuery()
1 9
20  Catch MyException As MySqlExceptio n
21        Console.WriteLine("Stored procedure error: MySQL code:
" & _
22               MyException.Number & " " & _
23               MyException.Message)
24   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.

{mospagebreak title=Conclusion}

In this chapter we examined the MySQL error handlers that allow you to catch error conditions and take appropriate corrective actions. Without error handlers, your stored programs will abort whenever they encounter SQL errors, returning control to the calling program. While this might be acceptable for some simple stored programs, it is more likely that you will want to trap and handle errors within the stored program environment, especially if you plan to call one stored program from another. In addition, you need to declare handlers for cursor loops so that an error is not thrown when the last row is retrieved from the cursor.

Handlers can be constructed to catch all errors, although this is currently not best practice in MySQL, since you do not have access to an error code variable that would allow you to differentiate between possible error conditions or to report an appropriate diagnostic to the calling program. Instead, you should declare individual handlers for error conditions that can reasonably be anticipated. When an unexpected error occurs, it is best to let the stored program abort so that the calling program has access to the error codes and messages.

Handlers can be constructed that catch either ANSI-standard SQLSTATE codes or MySQL-specific error codes. Using the SQLSTATE codes leads to more portable code, but because specific SQLSTATE codes are not available for all MySQL error conditions, you should feel free to construct handlers against MySQL-specific error conditions.

To improve the readability of your code, you will normally want to declare named conditions against the error codes you are handling, so that the intention of your handlers is clear. It is far easier to understand a handler that traps DUPLICATE_KEY_VALUE than one that checks for MySQL error code 1062.

At the time of writing, some critical SQL:2003 error-handling functionality has yet to be implemented in MySQL, most notably the ability to directly access the SQLSTATE or SQLSTATE variables, as well as the ability to raise an error condition using the SIGNAL statement. In the absence of a SQLSTATE or SQLCODE variable, it is good practice for you to define handlers against all error conditions that can reasonably be anticipated that populate a SQLCODE -like variable that you can use within your program code to detect errors and take appropriate action. We expect MySQL to add these “missing” features in version 5.2—you should check to see if they have been implemented in the time since this book was written (see the book’s web site for details). Note also that it is currently possible to provide a workaround (though a somewhat awkward one) for the missing SIGNAL statement if you find that it is absolutely necessary in your programs.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan