Using AR.connection.execute(sql) -- messing with my string?

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! :slight_smile:

- ff