fetching multiple result sets from mysql stored procedure in rails

Question

I've been searching all over for tips on this and have not really had any luck so far. With the mysql2 gem, trying to execute a stored procedure that returns multiple result sets gives me an unable to return results in this context error. I found someone had suggested to use the mysql gem instead (which I can't find an explanation of what's different between the two and what I might encounter by switching), and with that I've had more progress.

Here's what I have so far:

>> db = ActiveRecord::Base.connection.raw_connection
=> #<Mysql:0x1056ae3d8>
>> ActiveRecord::Base.connection.select_all("CALL p_rpt_test('', '');")
=> [{"Header"=>"Client,Project,Type,Due Date,Assigned To"}]
>> db.more_results?
=> true
>> db.next_result
Mysql::Error: Commands out of sync; you can't run this command now
        from (irb):3:in `next_result'
        from (irb):3

Does anyone know of a way to get this to work, with mysql2 or mysql gems? The app is running rails 3.0.1.

Answer 1

Ok well I have no figured out how to get AR to do this so I've ended up just going low level and using the mysql driver itself, which mostly works...

data = Array.new
db = ActiveRecord::Base.connection.raw_connection
header = db.query("CALL #{self.proc}(#{args});")
header.each {|r| data << r}
if db.next_result
  rows = db.store_result
  rows.each {|r| data << r}
end

ActiveRecord::Base.connection.reconnect!

It works, but I can't imagine there's not a better way. Also I have to reconnect after this or I get an error on the next query, and I haven't found a way to properly close the session. Oh and I have to use the mysql gem and not mysql2.

Grrrrr.