# Stored Procedures MYSQL and Rails 2.3.2 **URL:** \ **Category:** Rails\ **Created:** [August 24, 2009, 7:15pm UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297 "2009-08-24T19:15:24Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [August 24, 2009, 7:15pm UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/1 "2009-08-24T19:15:24Z")
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. ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [August 24, 2009, 9:18pm UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/2 "2009-08-24T21:18:35Z")
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 D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [August 26, 2009, 9:41pm UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/3 "2009-08-26T21:41:07Z")
This works for me: ActiveRecord::Base.connection.execute(“call SP\_name (#{param1}, #{param2}, … )”) 2009/8/24 E. Litwin [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [August 27, 2009, 4:09pm UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/4 "2009-08-27T16:09:04Z")
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); ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [August 28, 2009, 9:38am UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/5 "2009-08-28T09:38:50Z")
Check this > **[Stored Procedures and Rails – Part 1](https://nasir.wordpress.com/2007/12/03/stored-procedures-and-rails/)** > > There is no documentation in Rails about how to use stored procedures or atleast it is difficult to find where to look at. And according to DHH stored procedures is not the Rails Way to do things. … On Aug 27, 3:09 pm, Chris D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [August 28, 2009, 10:10am UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/6 "2009-08-28T10:10:22Z")
nas wrote: > Check this > > [Stored Procedures and Rails – Part 1 – Nasir Jamal](http://nasir.wordpress.com/2007/12/03/stored-procedures-and-rails) > > On Aug 27, 3:09�pm, Chris D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [August 28, 2009, 11:55pm UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/7 "2009-08-28T23:55:53Z")
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 D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [December 12, 2009, 9:21pm UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/8 "2009-12-12T21:21:42Z")
Fogive me if I’m entering this conversation in the middle, but I’m having problems using stored procedures with the latest patch (on rails 2.3.4) and with rails 2.3.5 (which, from my understanding, has the mysql patch included). When I use find\_by\_sql(“call sp()”), I get the error: Mysql::Error: Commands out of sync; you can’t run this command now: SHOW FIELDS FROM `sometable` When I use connection.execute or connection.select\_all to call the stored procedure, I get a hash returned. That’s great that it works, but I really need an AR result set, so I can use the getter methods already peppered throughout my application. If find\_by\_sql is not supposed to work with stored procedures, is there at least a way to convert the hash returned by the connection.execute method to an AR result object? Justin B. wrote: > 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! > > [#3151 MySQL adapter update to enable use of stored procedures - Ruby on Rails - rails](https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures) ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [December 13, 2009, 3:38am UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/9 "2009-12-13T03:38:59Z")
Bill R. wrote: > … > > When I use connection.execute or connection.select\_all to call the > stored procedure, I get a hash returned. A correction to my previous post… When I use connection.execute(“call sp()”), I get a MySql::Result returned. ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [September 6, 2009, 8:34pm UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/10 "2009-09-06T20:34:18Z")
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! [#3151 MySQL adapter update to enable use of stored procedures - Ruby on Rails - rails](https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures) ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [December 13, 2009, 4:38am UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/11 "2009-12-13T04:38:32Z")
Sorry for the multiple updates. Last one until I hear good news… It doesn’t matter what method I used to call the stored procedure from Rails - I always get the command-out-of-sync error. The stored procedure returns data just fine. But any call to mysql after that returns the error. ---
**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\ **Post date:** [March 23, 2010, 9:59am UTC](https://www.ruby-forum.com/t/stored-procedures-mysql-and-rails-2-3-2/174297/12 "2010-03-23T09:59:21Z")
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](https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures) (not tried yet)