Rails - Apr 2010 - 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:
===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 $$
===FWIW, I''ve pawed over the sources and see that
mysql_adapter''s execute()
calls @connection(query), but I lose the thread after that.  Here''s the
function (some lines elided for brevity):
===  sql = <<SQL
-- COVERAGE(astart, aend, bstart, bend)
--
DELIMITER $$
DROP FUNCTION IF EXISTS `coverage`$$
CREATE FUNCTION `coverage`(abeg DATETIME, aend DATETIME, bbeg DATETIME,
bend DATETIME)
  RETURNS FLOAT DETERMINISTIC
BEGIN
  <snip>
END;
$$
DELIMITER ;
SQL
  ActiveRecord::Base.establish_connection
  ActiveRecord::Base.connection.execute(sql)
end
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Apr 21, 2010, at 2:35 PM, Fearless Fool wrote:
> 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:
> ===> 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 $$
> ===> FWIW, I''ve pawed over the sources and see that
mysql_adapter''s
> execute()
> calls @connection(query), but I lose the thread after that. 
Here''s
> the
> function (some lines elided for brevity):
> ===
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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org



-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Apr 21, 7:35 pm, Fearless Fool
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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:
> ===> 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 $$
Looks like the mysql adapter doesn''t enable the multi statement
option.

Fred

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Apr 21, 2010, at 5:19 PM, Fearless Fool wrote:
> 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
>
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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org



-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Apr 21, 10:19 pm, Fearless Fool
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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
>
> - ff
> --
> Posted viahttp://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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"
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Apr 22, 12:01 am, Fearless Fool
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.

On Apr 21, 7:52 pm, Fearless Fool
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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
> --
> Posted viahttp://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.