[This topic is migrated from our old forums. The original author name has been removed]
In MySQL when you define a trigger or procedure, you usually have to change the delimiter temporarily, because statements within the trigger/procedure must be terminated with ';'. If I were running a script through the MySQL command-line interface, I would do something like this:
DELIMITER |
CREATE TRIGGER triggername AFTER INSERT ON tablename
FOR EACH ROW
BEGIN
;
;
...
END |
DELIMITER ;
It would be great if running this in the DbVisualizer SQL Commander would work the same way. SQL Commander does not handle 'DELIMITER' currently.
Right now the only way I have figured out to create triggers in the SQL Commander window is to go into the prefs and change the delimiters, then run the statement, then change the prefs back. This is a pain, and a better way to do this would be great.
Thanks
Nathan
Nathan,
Just enclose the complete block in "--/" and "/" and you will not need to change the delimiters.
--/
CREATE TRIGGER triggername AFTER INSERT ON tablename
FOR EACH ROW
BEGIN
;
;
...
END
/
Regards
Roger
a
anonymous
said
over 16 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Support 'DELIMITER' in scripts
Thanks! That will work.
Nathan
a
anonymous
said
over 15 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Support 'DELIMITER' in scripts
Bump!
I was going to post a bug report regarding this. I was developing a trigger in a text editor and cutting and pasting into DbV. Of course it failed.
I tried this trick with MySQL and it also failed (dbv 6.5.4).
--/
select 'hello';
select 'world';
/
20:04:35 [SELECT - 0 row(s), 0.000 secs] [Error Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 'world'' at line 2
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
Is this --/ ... / technique standard?, i.e. would it work with the MySQL command line?
Any chance of DbV supporting change of delimiters? Of course I don't know how standard the use of 'delimiter' is.
Ed
Edited by: Ed on 07-Apr-2009 20:33
Roger Bjärevall
said
over 15 years ago
[This reply is migrated from our old forums.]
Re: Support 'DELIMITER' in scripts
Ed,
> {quote:title=Ed wrote:}{quote}
> I was going to post a bug report regarding this. I was developing a trigger in a text editor and cutting and pasting into DbV. Of course it failed.
Why? Did you enclose the create trigger SQL in --/ and /?
--/
create trigger ...
/
> I tried this trick with MySQL and it also failed (dbv 6.5.4).
>
> --/
> select 'hello';
> select 'world';
> /
--/ and / instructs the SQL Commander to not parse anything in between into separate statements. Simply it means "send the complete block" to the server for execution.
> 20:04:35 [SELECT - 0 row(s), 0.000 secs] [Error Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 'world'' at line 2
> ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
>
> Is this --/ ... / technique standard?, i.e. would it work with the MySQL command line?
>
> Any chance of DbV supporting change of delimiters? Of course I don't know how standard the use of 'delimiter' is.
None of --/.../ and DELIMITER is standard. We have on the future list to add support for changing delimiter dynamically.
Regards
Roger
a
anonymous
said
over 15 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Support 'DELIMITER' in scripts
Using --/ and / is helpful, but it would be way, way better to be able to use DELIMITER, so my scripts run in DbVisualizer and the mysql client without having to be modified. So here's my vote (again) for supporting DELIMITER.
nathan
Roger Bjärevall
said
over 15 years ago
[This reply is migrated from our old forums.]
Re: Support 'DELIMITER' in scripts
Nathan,
Dynamically changing delimiter is on the future list.
Best Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Support 'DELIMITER' in scripts
Any update on this? Most other SQL utils seem to use "DELIMITER" around the exported DDL for stored procedures and triggers, so having this would allow easier imports in DBVis from these sources. And if DBVis had an option to write out "DELIMITER" in exports, instead of "--/", it would make them more portable.
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Support 'DELIMITER' in scripts
David,
This was fixed in the 7.0 version. Check the following section in the users guide for details:
http://www.dbvis.com/products/dbvis/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId437790
Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Support 'DELIMITER' in scripts
It would still require a search and replace of "DELIMITER" in DDL from other sources to "@DELIMITER", but for imports this is 99% (although it would be nice if your parse could handle the "DELIMITER" commands). However, your "Export Database..." routine still uses the anonymous SQL blocks around the trigger and stored procedure blocks. There's no preference to have it use DELIMITER or @DELIMITER instead? Even if it used "@DELIMITER" and I had to do a simple search/replace to run the output in another tool, that would be better than a complex regex on the multi-line "--/: blocks.
(The forum software seems to insert a space into your URL, breaking it.)
Hans Bergsten
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Support 'DELIMITER' in scripts
Hi David,
I have registered this request to replace the anonymous block delimiters with the @delimiter command in the script produced by Export. Thanks for the suggestion.
Best Regards,
Hans
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Support 'DELIMITER' in scripts
Perhaps a popup of "Delimiter escaping" in the "Options" area of the Export Database dialog? It could have three options. "Anonymous SQL blocks" for people who don't want the current behaviour to break. "@delimiter" to use your syntax. And "DELIMITER" to use that. The latter would let people exporting for use elsewhere to have a more portable set of DDL.
Roger Bjärevall
said
over 13 years ago
[This reply is migrated from our old forums.]
Re: Support 'DELIMITER' in scripts
David,
Just to inform that this is now fixed in the latest DbVisualizer 8.0 version.
Regards
Roger
anonymous
1 person likes this idea