How can we help you today?

Start a new topic
Discussions DbVisualizer Forums Feature Requests

Support 'DELIMITER' in scripts

[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
1 person likes this idea
12 CommentsSorted by Oldest First
[This reply is migrated from our old forums.]

Re: Support 'DELIMITER' in scripts
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
[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
[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
[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
[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
[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
[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.
[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
[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.)
[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
[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.
[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
Comments to this discussion are now closed!
More topics in Feature Requests
See all 621 topics