The MySQL "DELIMITER" keyword isn't working

Question

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...

The `DELIMITER` keyword is client-specific, and a function of the MySQL command line client, not part of the actual MySQL language API. If you need to change the delimiter for DbVizualizer, there may be another method built into that application. — Michael Berkowski, Jul 02 '12 at 20:20
See the post I've explained myself a little better, I can send you the full script if you require, but that would be enough o solve my problem. — Pedro 'Xympa' Nascimento, Jul 02 '12 at 20:30
@Pedro'Xympa'Nascimento: I tried you code and it works for me. — juergen d, Jul 02 '12 at 20:33
I just tried to mysqli_query that and it doesn't create the stored procedure. — Pedro 'Xympa' Nascimento, Jul 02 '12 at 20:35
@juergend DBVisualizer has both statement delimiters and SQL Block delimiters, are there any default SQL Block delimiters I can use?Besides the BEGIN-END ones that need the statement delimiter afterwards. The default ones the dbv are --/ to start and / to end, basically if I surround the create procedure with those it works, but not on php. — Pedro 'Xympa' Nascimento, Jul 02 '12 at 21:12

score 2 · Answer 1 · answered Jul 02 '12 at 20:39

2

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 ;

answered Jul 02 '12 at 20:39

gui_s3

138
7

2

Doesn't work on PHP either, as soon as it hits the delimiter keyword it throws an error and stops the script, it's funny because if I change the delimiter option in phpmyadmin it works, but I'm sure they don't use mysqli... – Pedro 'Xympa' Nascimento Jul 02 '12 at 20:45
I've managed to get this to work through shell_exec, but it creates an empty procedure as if it disregarded anything between BEGIN and END. – Pedro 'Xympa' Nascimento Jul 03 '12 at 00:25

score 0 · Answer 2 · answered Mar 17 '15 at 19:13

0

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

answered Mar 17 '15 at 19:13

Allen

478
9
21

score 0 · Answer 3 · answered Jan 22 '19 at 14:45

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 ;");

score 0 · Answer 4 · answered Apr 19 '22 at 00:45

0

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;

answered Apr 19 '22 at 00:45

Brogrammer

31
6

The MySQL "DELIMITER" keyword isn't working

4 Answers4

Linked