MySQL DELIMITER statement

Feature requests and their debate

Re: MySQL DELIMITER statement

Postby Serge » Sat May 14, 2016 7:03 am

DBeaver supports MySQL DELIMITER statement since version 3.6.
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: MySQL DELIMITER statement

Postby Mr Query » Mon May 23, 2016 9:19 am

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.
Mr Query
 
Posts: 6
Joined: Wed Mar 09, 2016 9:56 am

Re: MySQL DELIMITER statement

Postby Mr Query » Mon May 23, 2016 10:36 am

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!
Mr Query
 
Posts: 6
Joined: Wed Mar 09, 2016 9:56 am

Previous

Return to Feature Requests



Who is online

Users browsing this forum: No registered users and 2 guests