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

问题描述

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'd，但我最近遇到了同样的错误.

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("调用example_proc()")ActiveRecord::Base.clear_active_connections!

清除连接后，您可以运行任何其他查询，而之前尝试通过 rails 或其他存储过程访问数据库时会失败.

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 存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！