DELIMITER

kalvaro's profile image kalvaro posted 14 years ago in Running SQL scripts Permalink
I'm trying out some procedures I found at http://artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and I'm having difficulties to run them from within HeidiSQL because the DELIMITER keyword triggers a syntax error.

Is it because it's a specific command from the mysql command line tool and not part of MySQL Server's SQL language? Is there a workaround to run scripts that mix different delimiters?
ansgar's profile image ansgar posted 14 years ago Permalink
What do you actually try to run? I have no problems using the DELIMITER command here:
delimiter go
select 1

Note that there must NOT be a semicolon after DELIMITER <something>.
kalvaro's profile image kalvaro posted 14 years ago Permalink
I was trying Listing 7. It's weird, the error comes and go if I remove other lines. In particular, adding a blank line between DELIMITER go and CREATE PROCEDURE makes the error go away:

DROP PROCEDURE IF EXISTS famsubtree;
DELIMITER go
CREATE PROCEDURE famsubtree( root INT )
BEGIN
DROP TABLE IF EXISTS famsubtree;
CREATE TABLE famsubtree
SELECT childID, parentID, 0 AS level
FROM familytree
WHERE parentID = root;
ALTER TABLE famsubtree ADD PRIMARY KEY(childID,parentID);
REPEAT
INSERT IGNORE INTO famsubtree
SELECT f.childID, f.parentID, s.level+1
FROM familytree AS f
JOIN famsubtree AS s ON f.parentID = s.childID;
UNTIL Row_Count() = 0 END REPEAT;
END ;
go
DELIMITER ;


I copied the code from the web site using the clipboard. Can it be a mixed line-ending issue?
ansgar's profile image ansgar posted 14 years ago Permalink
Think that's fixed now. Looked like the detected end offset of the DELIMITER command was just one char too far to the left. Wonder why I didn't see that while I was testing that before.
kalvaro's profile image kalvaro posted 14 years ago Permalink
Well, there's a lot of stuff to test I guess. I hadn't noticed it either. Thanks for the quick response.

BTW, I've not been able to test it; r3383 starts crashing as soon as I start it :-?

date/time         : 2010-06-10, 09:18:36, 591ms
computer name     : SOFT14
user name         : ALVARO.GONZALEZ <admin>
registered owner  : *********************
operating system  : Windows XP Service Pack 3 build 2600
system language   : Spanish
system up time    : 8 days 21 hours
program up time   : 334 milliseconds
processors        : 2x Intel(R) Pentium(R) 4 CPU 3.00GHz
physical memory   : 181/991 MB (free/total)
free disk space   : (C:) 4,79 GB (E:) 14,60 GB
display mode      : 1280x1024, 32 bit
process id        : $92c
allocated memory  : 8,88 MB
executable        : heidisql.exe
exec. date/time   : 2010-06-10 09:11
version           : 5.1.0.3383
compiled with     : Delphi 2010
madExcept version : 3.0k
callstack crc     : $29323d54, $a1435a01, $a1435a01
exception number  : 1
exception class   : EAccessViolation
exception message : Access violation at address 006DDD36 in module 'heidisql.exe'. Read of address 00000034.
main thread ($1298):
006ddd36 heidisql.exe Main     8628  +6 TMainForm.GridResult
006c7fae heidisql.exe Main     2410  +3 TMainForm.UpdatePreviewPanel
006c7f74 heidisql.exe Main     2390  +7 TMainForm.actDataPreviewExecute
006c318a heidisql.exe Main     1310 +96 TMainForm.FormCreate
0056ae25 heidisql.exe Forms             TCustomForm.DoCreate
0056a9e5 heidisql.exe Forms             TCustomForm.Create
005752f6 heidisql.exe Forms             TApplication.CreateForm
00792704 heidisql.exe heidisql   63 +14 initialization
Code modification/commit from ansgar.becker, 14 years ago, revision 5.1.0.3384
Fix sporadic AV reported in forum: http://www.heidisql.com/forum.php?t=5911#p5925
ansgar's profile image ansgar posted 14 years ago Permalink
Oh sorry, that AV should be fixed in r3384 now.

Testing should be easy:
DELIMITER foo
SELECT 1;
SELECT 2;
DELIMITER ;

... plus some variations with spaces after delimiter clause.
kalvaro's profile image kalvaro posted 14 years ago Permalink
Now it works like a charm. Thank you!

Please login to leave a reply, or register at first.