Why are stored procedures still not supported in Rails (3+)?

Question

I am familiar with the long standing love-hate relationship between Ruby on Rails, DB(MS)-drivers and Stored Procedures and I have been developing Rails applications since version 2.3.2.

However, every once in a while a situation arises where a SP is simply a better choice than combining data on the (much slower) application level. Specifically, running reports which combines data from multiple tables is usually better suited for a SP.

Why are stored procedures still so poorly integrated into Rails or the MySQL gem. I am currently working on a project with Rails 3.0.10 and MySQL2 gem 0.2.13 but as far as I can see, even the latest Edge Rails and MySQL gem 0.3+ still throw tantrums when you use SPs.

The problem which has been, and still is, is that the database connection is lost after a SP is called.

>> ActiveRecord::Base.connection.execute("CALL stored_proc")
=> #<Mysql::Result:0x103429c90>
>> ActiveRecord::Base.connection.execute("CALL stored_proc")
ActiveRecord::StatementInvalid: Mysql::Error: Commands out of sync; 
[...]
>> ActiveRecord::Base.connection.active?
=> false
>> ActiveRecord::Base.connection.reconnect!
=> nil
>> ActiveRecord::Base.connection.execute("CALL proc01")
=> #<Mysql::Result:0x1034102e0>
>> ActiveRecord::Base.connection.active?
=> false

Is this a really difficult problem to tackle, technically, or is this a design choice by Rails?

Answer 1

Stored procedures are supported in rails. The out of of sync error you are getting is because the MULTI_STATEMENTS flag for MySQL is not enabled by default in Rails. This flag allows for procedures to return more than 1 result set.

See here for a code sample on how to enable it: https://gist.github.com/wok/1367987

Stored procedures work out of the box with MS SQL Server.

I have been using stored procedures in almost all of my mySQL and SQL Server based rails projects without any issued.