Stored Procedure error "argument 1 for routine ... is not a variable or NEW pseudo-variable in BEFORE trigger"

Question

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Get_Next_Processing_Video`(   
    OUT out_IDVideo                   INT ,
    OUT out_YoutubeIDVideo            VARCHAR(15) 
)
BEGIN

    DECLARE a INT;
    DECLARE b VARCHAR(15);

    set a = 0;
    set b = "ciao";

    SELECT  NP.IDVideo, NP.YoutubeIDVideo INTO a, b 
    FROM    next_processing AS NP
    LIMIT 1; 

    UPDATE  Video AS V 
    SET     V.SchedulingFlag = TRUE
    WHERE   IDVideo = a;

SET out_IDVideo = a;
SET out_YoutubeIDVideo = b;

END

MySQL returns:

OUT or INOUT argument 1 for routine youtubedb.Get_Next_Processing_Video is not a variable or NEW pseudo-variable in BEFORE trigger.

What is the problem? The code seems correct.

This is my C# code for call procedure:

using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["AxWaveConnection"].ToString()))
        {
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            MySqlCommand cmd = new MySqlCommand("Get_Next_Processing_Video", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("out_IDVideo", idVideo));
            cmd.Parameters.Add(new MySqlParameter("out_YoutubeIDVideo", youtubeId));

            try
            {
                cmd.ExecuteNonQuery();  
            }
            catch (Exception e)
            {

            }

            conn.Close();
        }

Answer 1

I think the problem is in your call to the procedure.

With versions of the MySQL connector that don't support OUT parameters, the normal workaround is to use MySQL user variables to hold the return values, and then run a query to get the values of those variables.

First, execute the stored procedure, and have MySQL put the values of the OUT arguments into variables:

CALL `Get_Next_Processing_Video`(@IDVideo, @YoutubeIDVideo);

Note that those variables are not command parameters; they are variables that are held in the MySQL session. To get the values of those variables, immediately after the call to the procedure, using the same MySQL connection:

SELECT @IDVideo, @YoutubeIDVideo ;

And process the resultset from that query like you would like any other SELECT statement you expect to return one row.

---

Update:

With more recent versions of the MySQL Connector that support OUT parameters, I think you need to specify that those parameters are OUT parameters by setting a member attribute:

cmd.Parameters["out_IDVideo"].Direction = ParameterDirection.Output;
cmd.Parameters["out_YoutubeIDVideo"].Direction = ParameterDirection.Output;

---

As I indicated previously...

In older versions of the MySQL Connector which did not support OUT parameters, the workaround was to call the procedure using MySQL variables as arguments. The values returned from the procedure call are retained in the MySQL session. Immediately after calling the procedure, we would run a SELECT to retrieve the contents of the user variables.

Answer 2

Having come across the same issue calling a stored procedure from builder c++ for batch record creation where come calls were succesful and others not.

CREATE DEFINER=`root`@`localhost` PROCEDURE `article_import`(IN `idclient` INT(11) UNSIGNED, IN `code` VARCHAR(16), IN `shortdesc` VARCHAR(16),
                                        IN `descrip` VARCHAR(32),IN `status` VARCHAR(12), IN `fifo` SMALLINT(1), IN `maxfifo` SMALLINT(3),
                                        IN `flvduration` SMALLINT(3),IN `retendays` SMALLINT(3), IN `maxconserv` SMALLINT(3),
                                        IN `flcduration` SMALLINT(3), IN `flvunits` SMALLINT(1),IN `conservunits` SMALLINT(1),
                                        IN `flcunits` SMALLINT(1), IN `entrymode` SMALLINT(1), IN `exitmode` SMALLINT(1),
                                        IN `section` SMALLINT(1), IN `family` SMALLINT(1), IN `origen` SMALLINT(1), IN `mgroup` SMALLINT(1),
                                        IN `sgroup` SMALLINT(1), IN `invclass` SMALLINT(1), IN `created` DATE, INOUT `result` VARCHAR(64)) 
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  SET result = CONCAT('KO:', @text,':',@errno);
END;

INSERT INTO articles_imp (`idclient`,`code`,`shortdesc`,`descrip`,`status`,`fifo`,`maxfifo`,`flvduration`,`retendays`,`maxconserv`,
                                        `flcduration`,`flvunits`,`conservunits`,`flcunits`,`entrymode`,`exitmode`,`section`,`family`,
                                        `origen`,`mgroup`,`sgroup`,`invclass`,`created`)
      VALUES  (idclient,code,shortdesc,descrip,status,fifo,maxfifo,flvduration,retendays,maxconserv,
                                        flcduration,flvunits,conservunits,flcunits,entrymode,exitmode,section,family,
                                        origen,mgroup,sgroup,invclass,created);
SET result = CONCAT('OK:inserted:',LAST_INSERT_ID());
END

I looked up the myodbc.sql generated by setting Log queries to... checkbox found in the Debug tab in the MySql Connector/ODBC Data Source Configuration and found the difference between succesful and unsuccesful calls:

1418923873:Using prepared statement;
1418923873: call article_import(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ;
1418923873:ssps has been executed;
1418923873:query has been executed;

1418923873: call article_import(355, '20804', '', 'ANCHOAS ACEITE TIRAS90GRS', '', 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2014-12-18', '') ;
1418923873:Using direct execution;
1418923873:query has been executed;
1418923873:OUT or INOUT argument 24 for routine gedispa.article_import is not a variable or NEW pseudo-variable in BEFORE trigger;

In Builder the ADOStoredProc has a Prepared attribute, but setting it to true made no difference. In the end I declared the result parameter as an INOUT parameter rather than an OUT parameter and that fixed it.

We are nothing without logs.

Answer 3

I fix the same error adding the direction to all the variables:

MySQL SP Parameters:

OUT P_IDAGENTE_NETWORK int(11),
INOUT P_PROTOCOLO varchar(45)

C# code:

new MySqlParameter("P_IDAGENTE_NETWORK",0){Direction = ParameterDirection.Output},
new MySqlParameter("P_PROTOCOLO", network.Protocol){Direction=ParameterDirection.InputOutput}