#84 MySQL Driver unable to handle "DELIMITER" changes

[Kevin Benton]

When an SQL file contains a DELIMITER change, ESE doesn't sense it and react appropriately. For example, when defining a stored function, procedure or trigger (routine), it is very common to need to use DELIMITER so that the interpreter knows when to stop defining the stored routine.

DELIMITER $$
CREATE PROCEDURE foo ...
BEGIN
-- some code goes here
END $$
DELIMITER ;

In code I've written that parses files then sends them through to the system like this, I've had to pre-parse the file to divide statements up by delimiter while stripping the delimiter changes out of the file because mysqld doesn't understand delimiter changes (that's just the mysql command-line client handling it for us).

Today, I ran a few hundred lines of code from ESE that wouldn't execute code successfully with delimiter changes embedded in the code that the MySQL command-line client handled flawlessly.

[Claude N. Warren, Jr.]

This defect is blocking my usage of the tool on my current project. If anyone has any idea where in the code to look for this issue, I would be willing to take a look at fixing it.

[Heiko Hilbert]

SQL Explorer is an SQL plugin which supports all database for which a JDBC driver is available. Thats why there is only a small support for database client specific commands (like delimiter).
Nevertheless, delimiter command is supported but it is (currently) case sensitive. As workaround you can change your scripts from DELIMITER to delimiter or wait for the next service release of SQL Explorer (comming soon).

[Heiko Hilbert]

One Tip: There is a undocumented file extension .fls for file list. If you have to run multiple SQL files with your stored function defintion code you can create a file with this extension and add the files to it (one file name per line). With the associated File List Editor you can run these files as one batch. if the file name is not absolute it is resolved relative to the location of the .fls file.

[danielj]

Hm, changing DELIMITER to lowercase helped... somewhat. Still it seems only to be valid for the very next command? Something like

delimiter $$
SELECT "foo" $$
SELECT "bar" $$
delimiter ;

will complain about an SQL error near SELECT "bar" $$; having just a single statement will work though.

[Heiko Hilbert]

Yes, that's a problem. It works if you use it in that way:
delimiter $$
SELECT "foo"
$$
SELECT "bar"
$$
delimiter ;

Reason: If the delimiter ist not a single character SE assumes that it should be place at the beginning of a line like the 'go' delimiter in sybase.

Alternative:
delimiter |
SELECT "foo" |
SELECT "bar" |
delimiter ;

I will check the code to correct this.

My working code looks like this:
delimiter //
drop procedure IF EXISTS someProc
//
create procedure someProc(param1 char(8), param2 smallint)
begin
...
end;
//

delimiter ;

[chris]

This still doesn't work for me (even with copy-pasting the examples in this ticket).

I always get: "You have an error in your SQL syntax (...) near 'delimiter'"

It only happens on the line changing the delimiter back to ; though.

I.e. this works: delimiter //
this crashes: delimiter ;

Other than that, nice tool, btw. :)