如何从Rails中的MySQL存储过程获取输出参数? [英] How to get output parameters from MySQL stored procedure in Rails?

问题描述

我正在尝试从MySQL存储过程获取输出参数，让我们看下面的示例:

I'm trying to get a output parameter from MySQL stored procedure, let's have look a example below:

1在mysql中，我创建了此过程，并且可以正常工作.

1 In mysql I created this procedure and it works.

CREATE PROCEDURE sp_name (out id int)
Begin
    select id into @id from table order by id desc limit 1;
End

mysql> call sp_deduct_credit_and_money(@id);
Query OK, 0 rows affected (0.01 sec)

mysql> select @id;
+--------------+
|          @id |
+--------------+
|           24 |
+--------------+
1 row in set (0.00 sec)

2因此，它也可以在Rails中使用，但是不会为我返回任何值:

2 Therefore, it also works in Rails, BUT it won't return any value for me:

ActiveRecord::Base.connection.execute("call sp_name(@id)")
ActiveRecord::Base.connection.execute("select @id")

这是Rails中的日志

Here is the log in Rails

(2.0ms)  call sp_name(@id)
(0.1ms)  select @id

我的问题是，如何获取输出参数@id的返回值?

My question is that how could I get the return value of the output parameter @id?

推荐答案

我找到了一种获取输出参数，与大家共享的方法，如下所示:

I found a way to get the output parameter, to share with you guys, see below:

1创建连接

client = Mysql2::Client.new(ActiveRecord::Base.configurations[Rails.env.to_s].symbolize_keys)

2使用此连接呼叫您的sp

2 use this connection to call your sp

client.query "call sp_xxx"

3获取输出参数

result = client.query("select @output_parameter")

4返回输出

return !result.first["@output_parameter"].nil?

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