The GET DIAGNOSTICS statement

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