Page 2 of 2

Re: MySQL DELIMITER statement

PostPosted: Sat May 14, 2016 7:03 am
by Serge
DBeaver supports MySQL DELIMITER statement since version 3.6.

Re: MySQL DELIMITER statement

PostPosted: Mon May 23, 2016 9:19 am
by Mr Query
Being able to change the delimiter in the settings does not solve the issue in MySQL/MariaDB scripts. For the definition of a procedure or function, you need to be able to set the delimiter within the script, as in the example earlier in this thread.

Our SQL code is repeatable (meaning that you can run it over an existing database or over a new one, and it will make sure it gets into the desired state). To achieve this, table creation statements use the "IF NOT EXISTS" clause and later changes, like adding new columns, are put in stored procedures after the table create statements. An example can be found at https://www.howtoforge.com/tutorial/evolving-a-database-with-mysql/#alas-we-need-them-stored-routines (this page describes how to build modular, repeatable scripts in MySQL).

These scripts can be run with the MySQL command-line client and with some database front-ends, like SQLYog.

The DELIMITER statement is in MySQL a bit of an odd statement: it is not an SQL statement but a directive in the MySQL command-line client. But some database front-ends do support it, because it is the only way I know of to get a procedure definition to the server. So the feature request is to be able to change the delimiter from the script, so we can run scripts from the database front-end like we do from the MySQL command-line client.

Re: MySQL DELIMITER statement

PostPosted: Mon May 23, 2016 10:36 am
by Mr Query
Ah, I see. I did not know the DELIMITER statement was implemented, because I always got an error message when I used a DELIMITER statement. I followed an example from the MySQL documentation, which uses two characters instead of one.

This works:

Code: Select all
DELIMITER ~
-- Procedure definition here
~
DELIMITER ;


This does not work:

Code: Select all
DELIMITER //
-- Procedure definition here
//
DELIMITER ;


(At the // that closes the definition, you will get an error "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 '/' at line xxx")

I am glad the delimiter statement works. I just have to choose a better delimiter.

Thank you!