Calling MySQL stored procedure with OUT parameter through Rails

Question

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:

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.

Check: [http://stackoverflow.com/q/7480579/1316440](http://stackoverflow.com/q/7480579/1316440) — wchiquito, Jun 03 '15 at 10:44
Thanks. That worked. I modified it a bit according to my code. I have posted the answer below. — codevj, Jun 05 '15 at 09:01

codevj · Accepted Answer · 2015-06-19T12:57:51.700

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 = 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:

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

Calling MySQL stored procedure with OUT parameter through Rails

1 Answers1