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?

Triumphant

FWIW: From Feb 2006, DHH interview: "I’m entirely uninterested in fancy features like stored procedures, triggers, and the like" -- dev.mysql.com/tech-resources/interviews/… – Xylotomous

Also "I consider stored procedures and constraints vile and reckless destroyers of coherence"... – Girdler

2006... Ancient principles (yes, 6 years is ancient in a world like Rails') rarely hold meaning against the evolution of technology (640k is enough, anyone?) There is the theoretical and practical domain. In theory I absolutely stand by the Rails paradigm, but sometimes the best normalized database gives the worst performance in the real world. When a SP can gather the data for my report in less than 100ms and doing it 'the Rails way' takes over 2-3 seconds and multiple queries fired at a shared database, I know who the clear winner is. – Triumphant

They work just fine with the SQLServer adapter ActiveRecord::Base.execute_procedure(:some_proc_name, input1, input2), maybe someone just needs to implement it correctly in the adapter? – Kelsy

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.

Trentontrepan

Answer 2

This is for postgres to execute a stored procedure that returns instances of MyClass.

sql=<<-SQL
select * from my_cool_sp_with_3_parameters(?, ?, ?) as 
foo(
  column_1 <type1>,
  column_2 <type2>
)
SQL

MyClass.find_by_sql([sql, param1, param2, param3]);

Replace the column list inside of foo() with the columns from your model and the stored procedure results. I'm sure this could be made generic by inspecting the columns of the class.

Macnair

Answer 3

Those who are getting sync errors may have procedures that generate multiple results. You will need to do something like this to handle them:

raise 'You updated Rails. Check this duck punch is still valid' unless Rails.version == "3.2.15"
module ActiveRecord
  module ConnectionAdapters
    class Mysql2Adapter
      def call_stored_procedure(sql)
        results = []
        results << select_all(sql)
        while @connection.more_results?
          results << @connection.next_result
        end
        results
      end
    end
  end
end

Call like this:

ActiveRecord::Base.connection.call_stored_procedure("CALL your_procedure('foo')")

Myriam