是否可以从Ruby调用MySQL存储过程? [英] Is it possible to call a MySQL stored procedure from Ruby?

问题描述

当我尝试从Rails调用存储过程时，出现此异常:

When I try to call a stored procedure from Rails, I get this exception:

ActiveRecord::StatementInvalid: Mysql::Error: PROCEDURE pipeline-ws_development.match_save_all can't return a result set in the given context: call match_save_all()
    from /Users/otto/Projects/Futures/src/pipeline-ws/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:150:in `log'
    from /Users/otto/Projects/Futures/src/pipeline-ws/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:281:in `execute'
    from (irb):3

Rails Wiki中有一个页面讨论了MySQL的补丁适配器可以解决此问题，但是它已经过时并且似乎不再起作用.

There is a page in the Rails Wiki that discusses a patch for the MySQL adapter that resolves this issue, but it's out-of-date and doesn't seem to work anymore.

配置代码正确地启用了存储过程，但是在存储过程调用之后，连接仍然不同步并且新的call_sp方法不再起作用.

The configuration code enables stored procedures correctly, but it still has the issue with the connection getting out of sync after a stored procedure call and the new call_sp method doesn't work anymore.

关于如何使它工作的任何建议?

Any suggestions for how to get this working?

这是我正在使用的代码:

This is the code I'm using:

ActiveRecord::Base.connection("call storedproc()")

无论storedproc()是否返回任何结果，它都会引发相同的异常.

It throws the same exception whether storedproc() returns any results or not.

推荐答案

将过程包装到函数中是否可行?如果由于没有返回行(...can't return a result set in the given context...)而导致Ruby的倒推，则可以解决此问题:

Would it work to wrap the procedure in a function? If Ruby's barfing due to no rows returned (...can't return a result set in the given context...), this may fix it:

DELIMITER $

CREATE PROCEDURE tProc()
BEGIN
    SET @a = 'test';
END;
$

CREATE FUNCTION tFunc()
RETURNS INT
BEGIN
    CALL tProc();
    RETURN 1;
END;
$

DELIMITER ;

SELECT tFunc() FROM DUAL;
>> 1

SELECT @a FROM DUAL;
>> 'test'

尽管，实际上，这不是一个非常可扩展的解决方案.

Although, realistically, this isn't a very extensible solution.

后续:我在Ruby/ActiveRecord上还不错，但是这个示例确实可以工作

ActiveRecord::Base.establish_connection(authopts)

class TestClass < ActiveRecord::Base
end

test_class = TestClass.new
puts %{#{test_class.connection.select_one('SELECT tFunc() AS tf FROM DUAL')}}
>> tf1

使用CALL tProc()会导致与您类似的错误.

Using CALL tProc() resulted in an error similar to yours.

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