Stored Procedures with PhpMyAdmin

At first sight, MySQL with PhpMyAdmin looks very cheap in comparison to for example Microsoft SQL Server and the SQL Client. Actually MySQL can be more complex as it looks like. So I learned recently, that MySQL supports stored procedures. The only problem is, that PhpMyAdmin does not support them. You can create it with PhpMyAdmin, but there are no menu to view or edit them.

View stored procedures

PhpMyAdmin has no menu for stored procedures. But you can view it with the help of SQL. There are SQL commands to view all stored procedures and also their code:

• this statement returns a list of all stored procedures that are visible by the current user

  SHOW PROCEDURE STATUS

• you can filter this list for only one database

  SHOW PROCEDURE STATUS WHERE db = 'datenbankname'

• with the like operator you can also filter for strings

  SHOW PROCEDURE STATUS WHERE datenbankname LIKE '%suchstring%'

If you know the name of a stored procedure you can also get the create statement of it. This can be done with the following statement:

SHOW CREATE PROCEDURE NameDerProcedure

A simple stored procedure

Let me first explain our problem: we have a table called books. There we have a primary key (in_id), a column for the EAN code of the book (in_EAN) and a column that shows us if this book is written in german (in_DE). Our import does not contain this information, so we have to get it from another column. The 4. char from the EAN code is this peace of information. If this char is 3, then it is a german book.

To fill this column (in_DE) we have to compute some code. This can be done with update and insert stored procedures. This procedures are automatically fired on every update or insert on this table. Here is the code for the update stored procedure:

DROP PROCEDURE IF EXISTS update_book;

CREATE PROCEDURE update_book(
	IN p_in_EAN	varchar(15),
	IN p_in_id	int(11)
) 
BEGIN
	UPDATE books SET in_DE = if( SUBSTRING( p_in_EAN, 4, 1 ) =3, 1, 0 ) WHERE in_id = p_in_id;
END

If you copy paste this into the SQL textarea of PhpMyAdmin and run it, you will get an error.

The problem with the delimeter

Why do we get an error? The statement looks ok? After some search I found the solution in any forum. SQL statements are normally one line long and they and with a ‚;‘. Our statement is much longer and it has an ‚;‘. But not at the end. So the SQL interpreter only gets the code up to to ‚;‘ and so our END is missing.

The solution is quite simple. In the bottom of the SQL textarea, there is a small input field called delimeter. There you see a ‚;‘ per default. Here we have to define another sign (for example //). The correct statement looks as follows:

DROP PROCEDURE IF EXISTS update_book;
//
CREATE PROCEDURE update_book(
	IN p_in_EAN	varchar(15),
	IN p_in_id	int(11)
) 
BEGIN
	UPDATE books SET in_DE = if( SUBSTRING( p_in_EAN, 4, 1 ) =3, 1, 0 ) WHERE in_id = p_in_id;
END//

And the Insert stored procedure:

DROP PROCEDURE IF EXISTS insert_book;
//
CREATE PROCEDURE insert_book(
	IN p_in_EAN	varchar(15)
) 
BEGIN
INSERT INTO books (in_DE) VALUES ( (if( SUBSTRING( p_in_EAN, 4, 1 ) =3, 1, 0 )));
END//

More information can be found at MySQLTutorial.org finden.

Who else had troubles with stored procedures?