Eclipse DTP still doesn't handle delimiter statements

Question

This

use sample_db;

CREATE TRIGGER bar_in
BEFORE INSERT ON bar
FOR EACH ROW
BEGIN
     DECLARE foo INT;

END;

fails with

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 '' at line 5

How to solve this syntax error (MySQL server is 5.5)?

is it possible that triggers are disabled or some such?

EDIT: This is a problem with Eclipse DTP existing since 2009, bugzilla

@Robert Rozas: 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 'BEGIN SET foo = ""' at line 3 — Jakub Bochenski, Apr 02 '14 at 14:41

Racil Hilan · Accepted Answer · 2014-04-03T16:06:11.073

1

I believe the error was related to the delimiter. Try this:

DELIMITER $$

CREATE TRIGGER bar_in
BEFORE INSERT ON bar
FOR EACH ROW
BEGIN
     DECLARE foo INT;
END $$ 

DELIMITER ;

edited Apr 03 '14 at 16:06

answered Apr 02 '14 at 13:02

Racil Hilan

This results in "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 $$" – Jakub Bochenski Apr 02 '14 at 14:38
@JakubBochenski Where are you running the SQL? Are you running it in phpMyAdmin? If so, what version? – Racil Hilan Apr 02 '14 at 15:16
I'm using eclipse sql workbench, will try in command line l8r; regular DDLs like CREATE TABLE work fine – Jakub Bochenski Apr 02 '14 at 16:00
@JakubBochenski `CREATE TABLE` is different as it does not need to change the delimiter, so it will run. I don't know about eclipse sql workbench, but check if it supports the `DELIMITER` command. If not, then I'm afraid you cannot create triggers and stored procedures that way. – Racil Hilan Apr 02 '14 at 16:27
1

@JakubBochenski Oh, I didn't pay attention to the missing `FOR EACH ROW` line. Please add it. I updated my answer and tested it successfully in MySQL Query Browser. – Racil Hilan Apr 03 '14 at 16:07
good catch, looking at the same code for too long makes you blind; OTOH the mysql syntax error reporting could be friendlier – Jakub Bochenski Apr 03 '14 at 16:45

1 Answers1