'The MySQL "DELIMITER" keyword isn't working

Ok so, I've been ripping my hairs ou on this one, why doesn't this work?

DELIMITER |

CREATE PROCEDURE Decrypt_pw()
    READS SQL DATA
BEGIN
  SELECT 'Hey Select';
END|

It's so basic and I'm pretty sure I'm using the correct syntax, what am I missing?

Error:

21:14:07  [DELIMITER - 0 row(s), 0.000 secs]  [Error Code: 1064, SQL State: 42000]  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 |

CREATE PROCEDURE Decrypt_pw()
    READS SQL DATA
BEGIN
  SELECT 'He' at line 1
 21:14:07  [END| - 0 row(s), 0.000 secs]  [Error Code: 1064, SQL State: 42000]  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 'END|' at line 1

I'm using DbVisualizer, latest version, could this problem be with the software itself?

Perhaps I should explain myself better, the passwords are encrypted in my database (no need to worry), and this allows me to decrypt them, this is for a personal project I'm working on.

I was trying to develop a script that would allow me to run it and set up the necessary databases, tables, etc for that to work, and I require some SPs which must also be created, I'm trying to create an SP through a mysqli_query, is that even possible?

Basically it's for a "setup script" of a php application.

UPDATE: Seems that this is supposed to work, however I can't use objects due to the guys at HostGator -.- not allowing for objects in PHP.

I Have pretty much given up on mysqli since it's just not going to work I'm trying with shell_exec, I'm creating the procedure but when I check the ddl it's empty, it's creating empty procedures but at least it's doing something...

mysqlstored-proceduresdelimiterproceduresdbvisualizer


Solution 1:[1]

it is probaly a software version problem... i tried your code and it works just fine for me... try this

DELIMITER //
    CREATE PROCEDURE Decrypt_pw()
            READS SQL DATA
      BEGIN
      SELECT 'Hey Select';
      END //
    DELIMITER ;

Solution 2:[2]

At least as of 9.1, DBVisualizer doesn't support the DELIMITER keyword. Here's the way they do it: link.

Solution 3:[3]

Definitely Not an elegant work-around ... but it works.
All the usual caveats about not shelling out, yada yada yada.

// here's the core stored procedure code
$stored = <<<EOT
CREATE PROCEDURE Decrypt_pw()
    READS SQL DATA
BEGIN
  SELECT * FROM whatever;
END #
EOT;

// first, shell out to change the delimiter using mysql command-line
shell_exec('mysql -u user -ppassword -e "DELIMITER #");

// assuming $pdo is a valid PDO connection 
// send the command to create the stored procedure:
$pdo->exec($stored);

// now shell out again to change the delimiter back
shell_exec('mysql -u user -ppassword -e "DELIMITER ;");

Solution 4:[4]

Try putting space between 'DELIMITER' and '|'. It worked for me.

DELIMITER |  --here

CREATE TRIGGER my_trigger BEFORE INSERT
ON employee
FOR EACH ROW BEGIN
INSERT INTO trigger_test VALUES('added new employee');
END |

DELIMITER;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 gui_s3
Solution 2 Allen
Solution 3 dougB
Solution 4 Raj Belwal

Related Questions