通过Rails用OUT参数调用MySQL存储过程

The stored procedure looks something like:

存储过程类似于:

CREATE PROCEDURE `manager`(IN amount decimal(9,2), IN list text, IN acc_id int(11), OUT return_code int(1))
BEGIN

 DECLARE exit handler for sqlexception, sqlwarning
  BEGIN
  set return_code = 1;
  rollback;
  END;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  --my code
  set return_code = 0;
END

The method from rails model is:

rails模型的方法是:

return_code = -1
return_code = self.connection.execute("call manager(#{amount}, '#{list}', #{acc_id}, #{return_code})")

But this throws an error:

但这会引发错误:

ActiveRecord::StatementInvalid: Mysql2::Error: OUT or INOUT argument 4 for routine staging.manager is not a variable or NEW pseudo-variable in BEFORE trigger: call manager(2222, 'list', 2, -1)

Need some help on how to pass OUT parameter while calling stored procedure from rails.

在从rails调用存储过程时如何传递OUT参数需要一些帮助。

1 个解决方案

#1

The solution below worked for me:

以下解决方案对我有用:

I just changed the #{return_code) with @return_code (no need to pass return_code = -1 as input, as it is an OUT parameter):

我只是用@return_code改变了#{return_code'(不需要传递return_code = -1作为输入,因为它是一个OUT参数):

return_code = self.connection.execute("call manager(#{amount}, '#{list}', #{acc_id}, @return_code)")

In the stored procedure, I replaced return_code with @return_code and added a select statement in the end:

在存储过程中,我用@return_code替换了return_code并在最后添加了一个select语句:

CREATE PROCEDURE `manager`(IN amount decimal(9,2), IN list text, IN acc_id int(11), OUT return_code int(1))
BEGIN

 DECLARE exit handler for sqlexception, sqlwarning
 BEGIN
  set return_code = 1;
  rollback;
 END;
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   --my code
 set return_code = 0;
 SELECT @return_code;
END

#1