错误而从Rails 3中调用MySQL的存储过程（即返回结果集） [英] Error while invoking MySQL Stored procedure(that returns resultset) from Rails 3

问题描述

我从我的模型调用存储过程（MySQL的）。此存储过程返回一个结果，但我得到这个错误...

I am invoking a stored procedure (MySQL) from my model. This stored procedure returns a resultset, but i get this error...

Mysql2 ::错误：过程   my_db.sp_venue_nearby_with_questions   不能返回其结果在给定的设置   背景：......

Mysql2::Error: PROCEDURE my_db.sp_venue_nearby_with_questions can't return a result set in the given context:....

下面的铁轨code我使用 -

Here's the rails code i use -

connection.select_all("call sp_some_proc()")

我已经试过connection.execute还有，失败也是如此。我已经成功地能够从我的模型调用另一个存储过程，但一个不返回一个结果。

I have tried "connection.execute" as well, that fails as well. I have successfully been able to invoke another stored proc from my model, but that one does not return a resultset.

推荐答案

我想同样的事情与MySQL 2宝石这是在Rails 3中默认使用，并得到了类似的错误。问题是，MySQL的2宝石在默认情况下不使用它需要MULTI_STATEMENTS当你想获得一个结果集返回从过程。

I tried the same thing with the MySQL 2 gem which is used by default in Rails 3 and got a similar error. The problem is that the MySQL 2 gem by default does not use the MULTI_STATEMENTS which is needed when you want to get a result set back from the procedure.

经过一番调查，我决定坚持原来的MySQL的宝石（适配器：mysql的而不是适配器：mysql2 在的database.yml ），这似乎也做工精细用Rails 3。

After some investigation I've decided to stick with the original MySQL gem (adapter:mysql instead of adapter:mysql2 in database.yml) which seems to work fine also in Rails 3.

下面是我做的，以便从一个存储过程得到结果的ActiveRecord类：

Here is what I do in order to get the result from a stored procedure to an ActiveRecord class:

db = ActiveRecord::Base.connection.raw_connection
entries = Entry.find_by_sql( 'CALL sp_get_all_entries()' )

# we need to flush the result set otherwise following SQL statements cannot be processed
db.next_result if ( db.more_results? )

现在从存储过程返回的行会可在输入对象，如：

Now the rows returned from the stored procedure will be available on the entries objects, e.g.

entries.each do |entry|
    puts entry.name
    puts entry.extra_column_from_sp
end

请注意，您可以在SP添加额外的列。这些额外的列将永远是类型为串，所以你可能需要将它们转换，如为日期。

Note that you can add extra columns in the SP. Those extra columns will always be of type "String" so you might need to convert them, e.g. to a date.

这篇关于错误而从Rails 3中调用MySQL的存储过程（即返回结果集）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！