Search yielded only ancients results with a lot of dead links that didnt help me any further. In the past it has been a pretty big issue to incorperate stored procedures into Rails. By now, has 2.3.2 in 2009 made any changes to this? How would one go about in 2.3.2 to call a stored procedure and work with its result set? (Be it a genuine object, or a hash) Model.connection.execute "CALL storedProc()" Above line will give the error that it cant return results for the given context. My stored proc returns column values from several models and some dynamicly calculated columns not found in any model. -- Posted via http://www.ruby-forum.com/.
I''ve still had to hack the mysql_adapter to set a flag upon opening
the MySQL database connection in order to be able to use SPs:
module ActiveRecord
class Base
def self.mysql_connection(config)
...
# last line of method - add 65536 flag
ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host,
username, password, database, port, socket, 65536], config)
end
end
end
This has worked for me, and I have not read about a fix in 2.x to
handle this.
On Aug 24, 10:15 am, Chris Dekker
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> Search yielded only ancients results with a lot of dead links that didnt
> help me any further.
>
> In the past it has been a pretty big issue to incorperate stored
> procedures into Rails. By now, has 2.3.2 in 2009 made any changes to
> this?
>
> How would one go about in 2.3.2 to call a stored procedure and work with
> its result set? (Be it a genuine object, or a hash)
>
> Model.connection.execute "CALL storedProc()"
> Above line will give the error that it cant return results for the given
> context.
>
> My stored proc returns column values from several models and some
> dynamicly calculated columns not found in any model.
> --
> Posted viahttp://www.ruby-forum.com/.
This works for me:
ActiveRecord::Base.connection.execute("call SP_name (#{param1},
#{param2}, ... )")
2009/8/24 E. Litwin <elitwin-ur4TIblo6goN+BqQ9rBEUg@public.gmane.org>
>
> I''ve still had to hack the mysql_adapter to set a flag upon
opening
> the MySQL database connection in order to be able to use SPs:
>
> module ActiveRecord
> class Base
> def self.mysql_connection(config)
> ...
> # last line of method - add 65536 flag
> ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host,
> username, password, database, port, socket, 65536], config)
> end
> end
> end
>
> This has worked for me, and I have not read about a fix in 2.x to
> handle this.
>
>
> On Aug 24, 10:15 am, Chris Dekker
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> wrote:
> > Search yielded only ancients results with a lot of dead links that
didnt
> > help me any further.
> >
> > In the past it has been a pretty big issue to incorperate stored
> > procedures into Rails. By now, has 2.3.2 in 2009 made any changes to
> > this?
> >
> > How would one go about in 2.3.2 to call a stored procedure and work
with
> > its result set? (Be it a genuine object, or a hash)
> >
> > Model.connection.execute "CALL storedProc()"
> > Above line will give the error that it cant return results for the
given
> > context.
> >
> > My stored proc returns column values from several models and some
> > dynamicly calculated columns not found in any model.
> > --
> > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Adding the 65536 flag seems to allow me to gather a resultset from the
stored procedure, yet my commands go out of sync. Running any query
(even a simple Model.first) after running a Stored Proc that returns
results gives this error:
ActiveRecord::StatementInvalid: Mysql::Error: Commands out of sync; you
can''t run this command now: call
scores(DATE(''2009-08-24''), 3282, 1);
--
Posted via http://www.ruby-forum.com/.
Check this http://nasir.wordpress.com/2007/12/03/stored-procedures-and-rails On Aug 27, 3:09 pm, Chris Dekker <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Adding the 65536 flag seems to allow me to gather a resultset from the > stored procedure, yet my commands go out of sync. Running any query > (even a simple Model.first) after running a Stored Proc that returns > results gives this error: > > ActiveRecord::StatementInvalid: Mysql::Error: Commands out of sync; you > can''t run this command now: call scores(DATE(''2009-08-24''), 3282, 1); > -- > Posted viahttp://www.ruby-forum.com/.
nas wrote:> Check this > > http://nasir.wordpress.com/2007/12/03/stored-procedures-and-rails > > On Aug 27, 3:09�pm, Chris Dekker <rails-mailing-l...-ARtvInVfO7m5VldFQK4jKA@public.gmane.orgt>Thanks, but that link did not help me. For MYSQL it doesn''t even work. Stored procedures are called through ''CALL'', not ''EXECUTE''. Also as I wrote in my third post, I already get the stored procedure to execute, the problem seems to lie in the closing / freeing of the result set. Calling .free on the returned result set does not solve anything. Tested on both the latest 5.0 and 5.1 MySQL databases with the newest 2.8.1 mysql gem -- Posted via http://www.ruby-forum.com/.
Try extending mysql_adapter.rb and add this method:
def select_sp(sql, name = nil)
rows = select(sql, name = nil)
while (@connection.more_results?())
@connection.next_result()
end
return rows
end
Then call the SP using this method from your controller/model where
the sql param is: "call my_sp..."
On Aug 28, 1:10 am, Chris Dekker
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> nas wrote:
> > Check this
>
> >http://nasir.wordpress.com/2007/12/03/stored-procedures-and-rails
>
> > On Aug 27, 3:09 pm, Chris Dekker
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
>
> Thanks, but that link did not help me.
>
> For MYSQL it doesn''t even work. Stored procedures are called
through
> ''CALL'', not ''EXECUTE''.
>
> Also as I wrote in my third post, I already get the stored procedure to
> execute, the problem seems to lie in the closing / freeing of the result
> set.
>
> Calling .free on the returned result set does not solve anything. Tested
> on both the latest 5.0 and 5.1 MySQL databases with the newest 2.8.1
> mysql gem
> --
> Posted viahttp://www.ruby-forum.com/.
E. Litwin wrote:> I''ve still had to hack the mysql_adapter to set a flag upon opening > the MySQL database connection in order to be able to use SPs: > > module ActiveRecord > class Base > def self.mysql_connection(config) > ... > # last line of method - add 65536 flag > ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, > username, password, database, port, socket, 65536], config) > end > end > end > > This has worked for me, and I have not read about a fix in 2.x to > handle this.I''ve submitted a patch to Rails 2.3.4 fixing this problem - any support for my ticket is appreciated! https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures -- Posted via http://www.ruby-forum.com/.
From the script/console: $ script/console Loading development environment (Rails 2.3.5) /Library/Ruby/Gems/1.8/gems/rails-2.3.5/lib/rails/gem_dependency.rb:119:Warning: Gem::Dependency#version_requirements is deprecated and will be removed on or after August 2010. Use #requirement>> ActiveRecord::Base.connection.active?=> true>> User.all.size=> 2>> ActiveRecord::Base.connection.active?=> true>> ActiveRecord::Base.connection.execute("CALL proc01")=> #<Mysql::Result:0x103429c90>>> ActiveRecord::Base.connection.execute("CALL proc01")ActiveRecord::StatementInvalid: Mysql::Error: Commands out of sync; you can''t run this command now: CALL proc01 from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log'' from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/mysql_adapter.rb:323:in `execute'' from (irb):5>> ActiveRecord::Base.connection.active?=> false>> ActiveRecord::Base.connection.reconnect!=> nil>> ActiveRecord::Base.connection.execute("CALL proc01")=> #<Mysql::Result:0x1034102e0>>> ActiveRecord::Base.connection.active?=> false>> ActiveRecord::Base.connection.reconnect!=> nil>> ActiveRecord::Base.connection.execute("CALL proc01")=> #<Mysql::Result:0x1033fc8a8>>> ActiveRecord::Base.connection.reconnect!=> nil>> ActiveRecord::Base.connection.execute("CALL proc01")=> #<Mysql::Result:0x1033f0b98> It looks like CALLing a stored procedure will drop a ActiveRecord connection. Maybe a "reconnect! if !active?" can be a quick patch... p.s. there is a ticket: https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures (not tried yet) -- 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.