Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Feature Requests

Thread: Support 'DELIMITER' in scripts


Permlink Replies: 12 - Pages: 1 - Last Post: Jun 21, 2011 7:54 AM Last Post By: Roger Bjarevall Threads: [ Previous | Next ]
Nathan

Posts: 14
Registered: 10/10/05
Support 'DELIMITER' in scripts
Posted: Feb 7, 2008 9:07 PM
   thread.click_reply Reply
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
<trigger statement 1>;
<trigger statement 2>;
...
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
Roger Bjarevall


Posts: 5,042
Registered: 12/17/04
Re: Support 'DELIMITER' in scripts
Posted: Feb 7, 2008 10:32 PM    global.in_response_to.tooltip in response to: Nathan
   thread.click_reply Reply
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
<trigger statement 1>;
<trigger statement 2>;
...
END
/

Regards

Roger
Nathan

Posts: 14
Registered: 10/10/05
Re: Support 'DELIMITER' in scripts
Posted: Feb 7, 2008 11:28 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
Thanks! That will work.

Nathan
Ed

Posts: 96
Registered: 03/31/08
Re: Support 'DELIMITER' in scripts
Posted: Apr 7, 2009 9:06 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
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 Bjarevall


Posts: 5,042
Registered: 12/17/04
Re: Support 'DELIMITER' in scripts
Posted: Apr 8, 2009 11:21 AM    global.in_response_to.tooltip in response to: Ed
   thread.click_reply Reply
Ed,

Ed wrote:
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
Nathan

Posts: 14
Registered: 10/10/05
Re: Support 'DELIMITER' in scripts
Posted: Apr 24, 2009 2:24 AM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
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 Bjarevall


Posts: 5,042
Registered: 12/17/04
Re: Support 'DELIMITER' in scripts
Posted: Apr 29, 2009 12:22 PM    global.in_response_to.tooltip in response to: Nathan
   thread.click_reply Reply
Nathan,

Dynamically changing delimiter is on the future list.

Best Regards

Roger
David Oberst

Posts: 15
Registered: 10/05/08
Re: Support 'DELIMITER' in scripts
Posted: May 14, 2010 7:43 AM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
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 Bjarevall


Posts: 5,042
Registered: 12/17/04
Re: Support 'DELIMITER' in scripts
Posted: May 14, 2010 10:41 AM    global.in_response_to.tooltip in response to: David Oberst
   thread.click_reply Reply
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
David Oberst

Posts: 15
Registered: 10/05/08
Re: Support 'DELIMITER' in scripts
Posted: May 14, 2010 12:00 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
   thread.click_reply Reply
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


Posts: 1,535
Registered: 10/11/06
Re: Support 'DELIMITER' in scripts
Posted: May 14, 2010 8:19 PM    global.in_response_to.tooltip in response to: David Oberst
   thread.click_reply Reply
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
David Oberst

Posts: 15
Registered: 10/05/08
Re: Support 'DELIMITER' in scripts
Posted: May 15, 2010 1:58 AM    global.in_response_to.tooltip in response to: Hans Bergsten
   thread.click_reply Reply
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 Bjarevall


Posts: 5,042
Registered: 12/17/04
Re: Support 'DELIMITER' in scripts
Posted: Jun 21, 2011 7:54 AM    global.in_response_to.tooltip in response to: David Oberst
   thread.click_reply Reply
David,

Just to inform that this is now fixed in the latest DbVisualizer 8.0 version.

Regards

Roger

Point your RSS reader here for a feed of the latest messages in all forums