如何从Rails调用MySQL存储过程? [英] How to call MySQL stored procedure from Rails?

问题描述

MySQL中一个简单的存储过程:

A simple stored procedure in MySQL:

CREATE PROCEDURE `proc01`()
BEGIN
 SELECT * FROM users;
END

启动Rails控制台:

Starts Rails console:

$ script/console
Loading development environment (Rails 2.3.5)
>> User.connection.execute("CALL proc01")
=> #<Mysql::Result:0x10343efa0>

看起来不错.但是，通过现有连接再调用同一存储过程将导致命令不同步错误:

Looks good. BUT, any more call to the same stored procedure via the existing connection will result in an Commands out of sync error:

>> User.connection.execute("CALL proc01")
ActiveRecord::StatementInvalid: Mysql::Error: Commands out of sync; you can't run this command now: CALL proc01
    from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log'
    from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/mysql_adapter.rb:323:in `execute'
    from (irb):2

可以通过重新加载！"清除错误.控制台中的命令:

The error can be cleared by a "reload!" command in the console:

>> reload!
Reloading...
=> true
>> User.connection.execute("CALL proc01")
=> #<Mysql::Result:0x1033f14d0>
>> 

如何从Rails调用MySQL存储过程?

How can I call MySQL stored procedure from Rails?

推荐答案

-

使用ActiveRecord::Base.connections.exec_query()是一种更好的方法，因为它返回的哈希值数组与人们期望的一样，而ActiveRecord::Base.connections.execute则没有.

Using ActiveRecord::Base.connections.exec_query() is as far as I can tell a MUCH better approach just because it returns an array of hashes as one would expect, which ActiveRecord::Base.connections.execute does not.

文档

-

请阅读上面的修改，我留以下内容供您参考.

虽然我意识到这个问题已经很老了，并且由于ohho发布的链接有404错误，但最近我也遇到了同样的错误.

While I realise this question is quite old and because the links ohho posted have 404'd, I had this same error recently.

我能够通过以下操作对其进行修复:

I was able to fix it by doing the following:

result = ActiveRecord::Base.connection.execute("call example_proc()") ActiveRecord::Base.clear_active_connections!

result = ActiveRecord::Base.connection.execute("call example_proc()") ActiveRecord::Base.clear_active_connections!

一旦清除了连接，就可以像以前一样尝试通过rails或另一个存储的proc访问数据库失败，然后可以运行任何其他查询.

Once you've cleared connections, you can run any other queries where as before it would have failed on trying to access the database through rails or another stored proc.

http://apidock.com/rails/v3. 2.13/ActiveRecord/Base/clear_active_connections％21/class

-

还值得一提的是，不应按照leente在链接

It's also worth mentioning that one shouldn't store the ActiveRecord connection in a variable as per leente's post on this link

不要缓存它！

不要将连接存储在变量中，因为当另一个线程已经签入连接池后，其他线程可能会尝试使用它.请参阅: ConnectionPool "

Don’t store a connection in a variable, because another thread might try to use it when it’s already checked back in into the connection pool. See: ConnectionPool"

connection = ActiveRecord::Base.connection   #WRONG

threads = (1..100).map do
 Thread.new do
begin
  10.times do
    connection.execute("SELECT SLEEP(1)")  # WRONG
    ActiveRecord::Base.connection.execute("SELECT SLEEP(1)")  # CORRECT
  end
  puts "success"
rescue => e
  puts e.message
   end
  end
end

threads.each(&:join) 

这篇关于如何从Rails调用MySQL存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！