DELIMITER

User, date Message
Written by kalvaro
3 years ago
Category: Running SQL scripts
457 posts since Thu, 29 Nov 07 		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?
Written by ansgar
3 years ago
4026 posts since Fri, 07 Apr 06 		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>.
Written by kalvaro
3 years ago
457 posts since Thu, 29 Nov 07 		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?
Written by ansgar
3 years ago
4026 posts since Fri, 07 Apr 06 		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.
Written by kalvaro
3 years ago
457 posts since Thu, 29 Nov 07 		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
Written by ansgar
3 years ago
4026 posts since Fri, 07 Apr 06 		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.
Written by kalvaro
3 years ago
457 posts since Thu, 29 Nov 07 		Now it works like a charm. Thank you!
 

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