Why are stored procedures still not supported in Rails (3+)?
mysql, ruby-on-rails, ruby-on-rails-3, software-design, stored-procedures
Solution
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.
Problem
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?