Error next to "DELIMITER $$"

Question

When I change the Delimeter from the mysql console or MySQL Workbench, I don't get any errors, but when I paste the same code into ruby ​​on rails, I get an error

mysql> DELIMITER $$ mysql> 

does not give errors.

but

 ActiveRecord::Base.connection.execute(%Q{ DELIMITER $$ }) 

gives:

 ActiveRecord::StatementInvalid: Mysql2::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 'DELIMITER $$' at line 1: 

Answer 1

DELIMITER is actually a MySQL command line parameter, not SQL: http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html . This means that you cannot set the delimiter in this way.

Also, this would not help if you could, since ActiveRecord::Base.connection.execute allows you to execute only one statement at a time (see http://www.seanr.ca/tech/?p=75 ) .

Answer 2

The correct answer is correct (Rails cannot execute DELIMITER because it is a MYSQL command), but did not answer the question @ ishandutta2007, so I will answer that here.

DELIMITER often used to wrap mysql functions and procedures; To achieve this on rails, simply wrap the body of the procedure in your own execution statement.

So, for example, code that might look like this:

 execute <<-SQL DROP FUNCTION IF EXISTS MyFunc; DELIMITER $$ CREATE FUNCTION My Func . . . $$ DELIMITER ; SQL 

Instead, it will become the following: with several execute calls acting as a “scope”, designed to override the delimiter:

 execute 'DROP FUNCTION IF EXISTS MyFunc' execute <<-SQL CREATE FUNCTION My Func . . . SQL