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?