Does ActiveRecord.connection.execute(string) perform any modifications on the given string? If so, what quoting do I need to add?
Here's why: I'm trying to use execute(string) to load a stored procedure. I can hand-load the procedure without error (i.e. via dbconsole), but when I call the function below, it throws the error:
Can you do it in two steps?
# -- COVERAGE(astart, aend, bstart, bend)
sql_drop = <<SQL DROP FUNCTION IF EXISTS `coverage` SQL
sql_create = <<SQL CREATE FUNCTION `coverage`(abeg DATETIME, aend DATETIME, bbeg DATETIME, bend DATETIME) RETURNS FLOAT DETERMINISTIC BEGIN <snip> END; SQL
ActiveRecord::Base.establish_connection ActiveRecord::Base.connection.execute(sql_drop) ActiveRecord::Base.connection.execute(sql_create)
You could also split your single sql on a '$$' boundary and iterate over each fragment.
-Rob
Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com
Rob Biedenharn wrote:
Can you do it in two steps? ... You could also split your single sql on a '$$' boundary and iterate over each fragment.
-Rob
Hi Rob: Sure - I could feed a line at a time for that matter. But consider me curious: what's the rationale behind splitting it? (I admit I'm a little nervous about a function that leaves the intermediate state of the SQL interpreter with delimiters set to $$ -- I suspect everything would stop working if that was interrupted before resetting the delimiters...)
- ff
Looks like the mysql adapter doesn't enable the multi statement option.
Fred
I'm not saying to change the delimiter at all! You just need to pass one statement to the mysql adapter (note Fred's post) rather than expecting it to parse the multiple statements. You're not feeding "lines", but "statements". The create part is certainly more than one line.
-Rob
Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com
Rob Biedenharn wrote:
> Can you do it in two steps? > ... > You could also split your single sql on a '$$' boundary and iterate > over each fragment.
> -Rob
Hi Rob: Sure - I could feed a line at a time for that matter. But consider me curious: what's the rationale behind splitting it? (I admit I'm a little nervous about a function that leaves the intermediate state of the SQL interpreter with delimiters set to $$ -- I suspect everything would stop working if that was interrupted before resetting the delimiters...)
That sort of state is done on a per connection basis. Secondly my understanding is that the point of changing the delimiter to $$ is because you don't want a ; in your function to be interpreted as the end of your create function statement. However if multiple statement handling isn't turned on, do you need to change the delimiter at all ? (you would need to separate the drop and the create since those are two separate statements)
Fred
Frederick Cheung wrote:
That sort of state is done on a per connection basis. Secondly my understanding is that the point of changing the delimiter to $$ is because you don't want a ; in your function to be interpreted as the end of your create function statement. However if multiple statement handling isn't turned on, do you need to change the delimiter at all ? (you would need to separate the drop and the create since those are two separate statements)
Fred
I'm way out of my league here, but I believe you need to set the delimiters when you store a function: SQL requires ';' termination at the end of individual statements within the function, but unless you modify the delimiters, the client will interpret ';' as the end of the CREATE FUNCTION ... call.
There's an explanation for the delimiter munging[*] on the mysql site:
http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html
... but heck, I'm willing to try it w/o the delimiters. Just not today -- client deadline.
- ff
[*] MUNG: (verb and acronym), recursively defined as "Mung Until No Good"
P.S.: I should have mentioned: I get your point about multiple statements, and can certainly do the DROP FUNCTION and the CREATE FUNCTION as two separate execute() calls. Whether or not the delimiters will mess it up is a discovery waiting to happen -- boundless joy.
- ff
Feh. I've split it into FOUR statements, summarized as:
ActiveRecord::Base.connection.execute('DROP FUNCTION IF EXISTS `coverage`;') ActiveRecord::Base.connection.execute('DELIMITER $$') ActiveRecord::Base.connection.execute(sql_body) ActiveRecord::Base.connection.execute('DELIMITER ;')
... and it's still bombing out at the 'DELIMITER $$' statement:
ActiveRecord::StatementInvalid: Mysql::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: DELIMITER $$
I'm still not sure what's going on, since (as I mentioned) I can hand-type this into dbconsole without getting an error.
I appreciate Rob & Fred's help, but it's not a show-stopper since I've manually stored the function. But I remain curious as to why something entered via execute() will fail when executing it by hand works. But that's just curiosity, not a need.
Thanks.
- ff
ActiveRecord::StatementInvalid: Mysql::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: DELIMITER $$
I'm still not sure what's going on, since (as I mentioned) I can hand-type this into dbconsole without getting an error.
As far as i understand things delimiter is a command/setting that is part of the mysql command line utility, not the dialect of sql spoken by mysql (just like exit, help etc).
The doc you linked to says
The first thing we do is to change the command-line client's command delimiter, to ensure that we can use semicolons inside the stored procedure without the client regarding them as end-of-statement. If you're using another client, you should of course skip the delimiter commands
when connecting via activerecord you're using a different client so you get to skip the delimiter stuff.
Fred
Frederick Cheung wrote:
when connecting via activerecord you're using a different client so you get to skip the delimiter stuff.
Fred
Lovely. I commented out the DELIMITER calls, and sure enough, it works:
ActiveRecord::Base.connection.execute('DROP FUNCTION IF EXISTS `coverage`;') # ActiveRecord::Base.connection.execute('DELIMITER $$') ActiveRecord::Base.connection.execute(sql_body) # ActiveRecord::Base.connection.execute('DELIMITER ;')
I look forward to the day when I know epsilon more than someone else on this list and can help them as much as you've helped me.
Best,
- ff
My understanding was that MySQL does not allow multiple statements to be executed in one shot as a means to try and avoid piggy backing and hacking. Could be wrong, though.
pepe wrote:
My understanding was that MySQL does not allow multiple statements to be executed in one shot as a means to try and avoid piggy backing and hacking. Could be wrong, though.
I now *also* understand that only one statement can be executed per call
-- and the reasoning makes good sense. But it sure would be nice if the
documentation for execute() mentioned that! 
- ff