Search This Blog

Wednesday, August 19, 2009

phpmyadmin and stored procedure

As far as i know, we are not able to view, edit stored procedure using phpmyadmin but we are able to drop/create stored procedure using phpmyadmin.

I try to create a mysql stored procedure using phpmyadmin and hit error below

Error

SQL query:

DELIMITER;

MySQL said:

#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




my original code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `xxxweb`.`pp_pspbatch` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pp_pspbatch`(
IN pBatchName VARCHAR(255),
IN pCountryID INT
)
BEGIN

xxxxx
xxxxx
xxxxx

END $$
DELIMITER ;

try below steps, it help to solve my problem
1. Go to any SQL tab in phpMyAdmin.
2. Directly below the SQL text area you'll see a text input labeled "Delimiter." In that field, enter "$$".





3. Modify the script become like below

DROP PROCEDURE IF EXISTS `xxxweb`.`pp_pspbatch` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pp_pspbatch`(
IN pBatchName VARCHAR(255),
IN pCountryID INT
)
BEGIN

xxxxx
xxxxx
xxxxx

END $$

and run it. It works.

No comments: