[Eclipsesql-bugs] [ eclipsesql-Bugs-3064605 ] MySQL Driver unable to handle "DELIMITER" changes

[SourceForge.net <no...@so...>]

Bugs item #3064605, was opened at 2010-09-12 06:35
Message generated for change (Comment added) made by hhilbert
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=725495&aid=3064605&group_id=132863

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Interface (example)
Group: v1.0 (example)
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Kevin Benton (s1kevin)
Assigned to: Heiko Hilbert (hhilbert)
Summary: MySQL Driver unable to handle "DELIMITER" changes

Initial Comment:
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.


----------------------------------------------------------------------

>Comment By: Heiko Hilbert (hhilbert)
Date: 2011-02-02 13:02

Message:
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 ;


----------------------------------------------------------------------

Comment By: danielj (danieljaenecke)
Date: 2011-02-02 10:40

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


----------------------------------------------------------------------

Comment By: Heiko Hilbert (hhilbert)
Date: 2011-01-18 15:17

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

----------------------------------------------------------------------

Comment By: Heiko Hilbert (hhilbert)
Date: 2011-01-18 15:10

Message:
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).

----------------------------------------------------------------------

Comment By: Claude N. Warren, Jr. (claudenw)
Date: 2011-01-17 20:07

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

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=725495&aid=3064605&group_id=132863