从Rails中的mysql存储过程中获取多个结果集 [英] fetching multiple result sets from mysql stored procedure in rails

问题描述

我一直在寻找有关此问题的技巧，但到目前为止还没有真正的运气.使用mysql2 gem，尝试执行返回多个结果集的存储过程使我无法在此上下文错误下返回结果.我发现有人建议使用mysql gem代替(我无法找到两者之间的区别以及切换时可能遇到的问题的解释)，并且有了更大的进步.

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

有人知道使用mysql2或mysql gems使其工作的方法吗?该应用程序正在运行Rails 3.0.1.

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

推荐答案

好吧，我还没有弄清楚如何让AR做到这一点，所以我最终只是低级使用了mysql驱动程序本身，主要是可以...

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!

它有效，但是我无法想象没有更好的方法.另外，在此之后，我必须重新连接，否则在下一个查询时会出现错误，并且我还没有找到正确关闭会话的方法.哦，我必须使用mysql gem而不是mysql2.

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.

这篇关于从Rails中的mysql存储过程中获取多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！