Home > Web Development > Using phpMyAdmin to Create Stored Procedures

Using phpMyAdmin to Create Stored Procedures

February 25th, 2009

I spend a good portion of each day writing stored procedures in MySQL. Here’s a simplified example of a procedure I might write:

DELIMITER $$

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

DELIMITER ;

If you were to copy and paste this code into phpMyAdmin’s SQL editor and execute it, just as it is, you would get the following error:

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DELIMITER’ at line 1

I’ve seen this error posted to numerous forums, but I’ve noticed that there are few places where new users would find the actual solution to this problem. So, here it is.

  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.

Here are some answers to your potential comments:

Q: Why don’t you just copy and paste your code to the command line?
A: If the stored procedure script is exceptionally long, this can be messy.
Q: Why don’t you just use the MySQL Query Browser that comes with the MySQL GUI Tools?
A: When you have a large number of tables in your database, the schema refresh in the MySQL Query Browser is painfully slow. If anyone has a good solution to that problem, please post the answer below in the comments!
Q: What is phpMyAdmin?
A: phpMyAdmin is a web-based interface that lets users manage MySQL databases right on the web. Check it out at: www.phpmyadmin.net.

Nate Smith Web Development , , ,

  1. shravani
    | #1

    it is showing an error
    #1655 – Cannot create stored routine `spFoo`. Check warnings
    when i pasted the above code of stored procedure

  2. shravani
    | #2

    anyone can please help in dis issue

  3. | #3

    If you copied the stored procedure code verbatim, you’ll need to make sure that the delimiter in PHPMyAdmin is set to $$. Check that, and then let us know the result. Thanks for checking out the blog.

  4. | #4

    My web host provides an older version of PHPMyAdmin which doesn’t have this delimiter field. HELP!

  5. | #5

    Does your web hosting provider give you SSH access? If so, you can use SSH to connect to the command line of your web server and use mysql on the command line. You would need to first save the SQL code that creates your stored procedure in a file on your server. Here’s how.

    1. Connect to your web server using SSH. If you’re on Windows, you can do this using a free program called PuTTY. (Google it to download.)

    2. On your web server’s command line, type the following command to create a text file into which you will save your stored procedure’s SQL code:

    nano myStoredProcedure.sql

    You can copy your stored procedure code and paste it into this file. To paste into a PuTTY terminal window, just right-click using the mouse. Hold ctrl and press x to save the file.

    3. To connect to mysql on the command line and run your stored procedure code you would issue a command like the following:

    mysql –username=yourusername –password=yourpassword yourdatabasename < myStoredProcedure.sql

    If successful, this will import your stored procedure code.

    One caution: If your version of phpMyAdmin is too old, it is quite possible that your version of MySQL is too old to accomodate stored procedures.

  6. | #6

    Thanks, got it fixed..

  7. karlosystem
    | #7

    hey thanks you.. saludos desde Lima, Peru. me sirvio de mucho

  8. A.Coder
    | #8

    This really helped me out, thanks very much

  9. shanmugam
    | #9

    it worked for me. i have created stored procedure as you said. but the how can i view stored procedures and how to edit them?

  10. Bistoco
    | #10

    Thanks a lot , really helpful

  11. Salim
    | #11

    saved my day.search half a day for this “delimiter”

  12. Dhivya
    | #12

    Thnks.
    You pupil done a great job.

  13. Larry
    | #13

    @Nate Smith
    If phpmyadmin does not have a delimiter field, how can
    this code be entered via phpmyadmin? No SSH Access…..

  14. andy powell
    | #14

    I too am having this same problem with my webhost. They use an old version of phpMyAdmin (one that the actual developers of phpMyAdmin was old and insecure!) and I don’t believe they offer SSH connections to the db server. I have tried umpteem times using different syntax to get an SP to actually do anything on the db but to no avail./ I can enter a really dumb SP such as:-

    CREATE PROCEDURE t()
    BEGIN
    /*Do absolutely nothing*/
    END;

    and this will be fine! When, however, I try putting some actual commands between the BEGIN / END statements then it bombs out. Any help and guidance greatly appreciated.

  15. | #15

    Andy, this seems to be a common issue for many users. If I find a workaround for older versions of PHPMyAdmin, I’ll post it here. Thanks for visiting.

  16. Sourav
    | #16

    try ihis

    delimiter ;;
    drop procedure if exists test2;;
    create procedure test2()
    begin
    select ‘Hello World’;
    end
    ;;

  17. DvlprWun
    | #17

    This was great. Worked like a charm for me….so, thanks.

  18. siva
    | #18

    @siva
    delimiter ;;
    drop procedure if exists test2;;
    create procedure test2()
    begin
    select ‘Hello World’;
    end
    ;;

  19. Ravi
    | #19

    DROP PROCEDURE IF EXISTS spFoo $$# MySQL returned an empty result set (i.e. zero rows).

    CREATE PROCEDURE spFoo ()
    BEGIN
    SELECT * FROM poll;
    END $$# MySQL returned an empty result set (i.e. zero rows).

    i get this error after run.

  20. Leigh
    | #20

    Could someone tell me how to add comments to a stored procedure? I need these comments to be in the script itself AND viewable in PhpMyAdmin. Thank you!!

  1. No trackbacks yet.