A new feature just got merged into mysql-trunk, the GET DIAGNOSTICS statement.
Many people have been asking for this for a very long time, so it is worth mentioning it.
mysql> select version(); +--------------+ | version() | +--------------+ | 5.6.4-m6-log | +--------------+ 1 row in set (0.00 sec) mysql> drop table test.no_such_table; ERROR 1051 (42S02): Unknown table 'test.no_such_table'
Why is it important ? In short, it allows to programmatically (i.e., in SQL) inspect what happened in a SQL exception.
mysql> get diagnostics condition 1 @p1 = MESSAGE_TEXT, @p2 = RETURNED_SQLSTATE, @p3 = MYSQL_ERRNO, @p4 = SCHEMA_NAME, @p5 = TABLE_NAME; Query OK, 0 rows affected (0.00 sec)
Exception handling code can then inspect attributes of the exception raised, to find out which error in particular occurred. Most critical items are fully implemented.
mysql> select @p1, @p2, @p3; +------------------------------------+-------+------+ | @p1 | @p2 | @p3 | +------------------------------------+-------+------+ | Unknown table 'test.no_such_table' | 42S02 | 1051 | +------------------------------------+-------+------+ 1 row in set (0.00 sec)
Unfortunately, others are not fully supported yet.
mysql> select @p4, @p5; +------+------+ | @p4 | @p5 | +------+------+ | | | +------+------+ 1 row in set (0.00 sec)
The SCHEMA_NAME and TABLE_NAME condition items should be 'test' and 'no_such_table' respectively, indicating which table caused the DROP TABLE statement to fail.
The GET DIAGNOSTICS statement is implemented, and returns the values found in the server diagnostics area, but what is missing now is to revise the implementation of every statement (such as DROP TABLE) to populate every attribute of the diagnostics area when raising an error.
While it may not look so impressive at first glance, implementing the GET DIAGNOSTICS statement is actually a huge step forward: now with SIGNAL and RESIGNAL, GET DIAGNOSTICS allows an application to implement proper SQL exception handling.
-- Marc Alff
PlanetMySQL Voting: Vote UP / Vote DOWN