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

DELIMITER

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

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