distal-attribute
distal-attribute
distal-attribute
distal-attribute

DELIMITER

kalvaro posted 5 years ago in Running SQL scripts
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 posted 5 years ago
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 posted 5 years ago
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 posted 5 years ago
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 posted 5 years ago
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

ansgar posted 5 years ago
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 posted 5 years ago
Now it works like a charm. Thank you!

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