CODEWITHSUNDEEP
mysql
Luc
Luc

Reputation: 2805

DELIMITER in mysql does not work

The below code works correctly when I use command line (mysql.exe) to execute, but incorrect when I use SQL Query in phpmyadmin or using ScriptRunner (Java) to execute.

The error: Error Syntax execute DELIMITER $$

How can I solve this problem?

-- Procedures
-- 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `upload_photo`(
IN owner_id int, IN photo_name text, IN photo_url text, IN thumbnail text
)
begin
declare inserted_id int unsigned default 0;
INSERT INTO photo(`owner_id`, `photo_name`, `photo_url`, `thumbnail`) VALUES(owner_id, photo_name, photo_url, thumbnail);
 set inserted_id = last_insert_id();
 select * from photo where photo_id = inserted_id;
end$$

-- 
DELIMITER ;

Upvotes: 0

Views: 2841

Answers (3)

lousuan
lousuan

Reputation: 79

I found a solution here and it works for me (i'm using ScriptRunner):

just use -- @DELIMITER $$ instead of DELIMITER $$

Upvotes: 1

Volt
Volt

Reputation: 99

PhpMyAdmin uses mysqli which doesn't support delimiter. See if delimiter is needed at all. I am running a script with multiple rows and my MySQL runs all of them with semicolons as delimiters.

Upvotes: 1

geg
geg

Reputation: 4785

I just ran into a similar issue where my DELIMITER statement was breaking when run via Flyway despite working when I ran it through my sql client.

The issue was either indentation or hidden special characters because formatting the code in the IDE fixed the issue.

Upvotes: 0

Related Questions