Store procedures in phpMyAdmin

Question

I must add stored procedures to MySQL database.

The problem is that the hosting offers phpMyAdmin to manage the database.

I searched on the Internet and the idea is to run the MySQL native statement that creates the procedures, but as the code of procedures often may have ;, we have to change the delimiter in MySQL.

phpMyAdmin does not have this option. Has anyone tried to create stored procedures manually setting the anchor, or something that worked?

Answer 1

There is a way, see this link: http://blog.nth-design.com/2009/02/25/creating-sp-in-phpmyadmin/

Quote from that link
1.Open phpMyadmin.
2.Select a database to work with.
3.Open the SQL tab.
4.Select all of the SQL statements between the DELIMITER statements in your stored procedure script. Do not include the DELIMITER statements! Here’s what my example script should look like:

DROP PROCEDURE IF EXISTS spFoo $$
CREATE PROCEDURE spFoo ()
BEGIN
    SELECT 'Foo' FROM DUAL;
END $$

5.In the delimiter field, just below the SQL editor’s text area, enter $$ as your delimiter.

Answer 2

Why everybody tries to use a visual tool as console?!? There is an easyer way:

Go to your database, and look for "More"

Then this screen will show. If you have created any stored procedures, they will be listed

To add new one click "Add routine"

Answer 3

I had trouble using the 'Routines' feature in PHPMyadmin cos it kept giving me false negatives so i did it via the 'SQL' tab instead.

CREATE PROCEDURE GetUserPwd(email VARCHAR(320), pass VARCHAR(128))
BEGIN
DECLARE userid INT(3) DEFAULT 0;
DECLARE password_equal INT(3) DEFAULT 0;
DECLARE output VARCHAR(30);
SELECT id INTO userid FROM members WHERE user_email = email;
IF userid != 0 THEN
SELECT user_pass = pass INTO password_equal FROM members WHERE id = userid;
IF password_equal = 0 THEN
SET output = 'not exist';
ELSE
SET output = 'exist';
END IF;
END IF;
SELECT output;
END

In the 'Delimiter' text box, type in '$$'. save.

after that , go to your 'Routines' tab and click on 'execute' and enter your inputs when prompted.

Answer 4

create procedure sp_helpme begin select * from my_table; end //

Delimiters in MySQL.

You have to change ';' to '//' on delimeter box in phpmyadmin. After executing successfully revert back the delimeter.

Answer 5

Try to create/edit stored procedures and other MySQL objects with visual object editors in dbForge Studio for MySQL. The express edition is free.

---

1. Connectivity question - what to do if there is no direct connection to MySQL server? Possible ways: HTTP tunneling - may be used to connect to the MySQL server, it is a method of connecting to the server through HTTP/HTTPS protocol, or using secure connection (SSH/SSL network protocol).
2. The DELIMITER client command is supported by suggested tool. So, this command can be used in the scripts. Also, stored procedures and other objects may be created and modified in visual editors.

Answer 6

You can set a delimiter manually using delimiter $$, where $$ is your chosen delimiter. This works on my shared hosting with phpMyAdmin. You should remember to set it back to ; when you are finished.