domingo, 1 de junio de 2014

Raise (Signal) MySql Exception

It's true that we can do a lot of data validation on the front-end. But, in some cases, it happens that is more convenient to do it at the server side. No only because it helps us to enforce data integrity, but because changes in stored procedures, functions, triggers, etc doesn't involve recompile and redistribute the application executable file.

It happens then that in some cases we need to raise an exception from a stored procedure as a way to let the front-end know that the process was not executed correctly. ¿How to do it in MySQL?. The basic sentence is very simple:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error Message';

In the following video we have a brief example (video is in spanish but you can get an idea just by watching):



This works from MySQL 5.5 and above. It basically assigns an error code ('45000' in this case) and a message as a text for the exception. There are predefined error codes (check this list) but because we want to raise an excepcion (or SIGNAL in MySQL) is for our app/database only then its better to use one code that doesn't exists in that list.

If we wanted to be much more specific about the information we want to send as part of the exception, we can set some other values.

My first search about this topic lead me to a StackOverFlow post and from the a search by jumping from link to link.

¡Greetings!

No hay comentarios.: