The GET DIAGNOSTICS
statement is a good way to show the structure of the diagnostics area because it can copy each value into a variable.
It has a verbose and an error-prone syntax, but it is the only way to analyze the diagnostics area within a stored program. In fact, SHOW WARNINGS
returns a result set to the client but does not allow the SQL code to access it. The HANDLER
block is executed when an exact error or a class of errors occurs, but it does not provide information about the problem. The reader is expected to be familiar with the stored programs. However, some examples of the use of GET DIAGNOSTICS
are useful to demonstrate the contents of the diagnostics area.
First, let's populate the diagnostics area with two errors. For this, we will use the same statements of the previous example:
MariaDB [(none)]> SET sql_mode = 'x'; ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'x' MariaDB [(none)]> RESIGNAL; ERROR 1645 (0K000): RESIGNAL when handler not active
Now, let's use GET DIAGNOSTICS
to copy the number of conditions in the diagnostics area into a variable, and SELECT
to show these values. Consider the following example:
MariaDB [(none)]> GET DIAGNOSTICS @num_errs = NUMBER; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT @num_errs; +-----------+ | @num_errs | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec)
Now, we know that there are two conditions. Let's visualize the SQLSTATE
value, the condition number, and the message of the two conditions, as shown in the following code:
MariaDB [(none)]> GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @error = MESSAGE_TEXT; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT @sqlstate, @errno, @error; +-----------+--------+------------------------------------------------------+ | @sqlstate | @errno | @error | +-----------+--------+------------------------------------------------------+ | 42000 | 1231 | Variable 'sql_mode' can't be set to the value of 'x' | +-----------+--------+------------------------------------------------------+ 1 row in set (0.00 sec)