存储过程错误“例程...的参数1不是触发器之前的变量或NEW伪变量". [英] Stored Procedure error "argument 1 for routine ... is not a variable or NEW pseudo-variable in BEFORE trigger"

问题描述

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返回:

例程youtubedb的OUT或INOUT参数1.在触发之前，Get_Next_Processing_Video不是变量或NEW伪变量.

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.

这是我用于调用过程的C#代码:

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();
        }

推荐答案

我认为问题出在您对程序的调用中.

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

对于不支持OUT参数的MySQL连接器版本，通常的解决方法是使用MySQL用户变量保存返回值，然后运行查询以获取那些变量的值.

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.

首先，执行存储过程，并让MySQL将OUT参数的值放入变量中:

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

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

请注意，这些变量不是命令参数.它们是在MySQL会话中保存的变量.要获取这些变量的值，请在调用过程后立即使用相同的MySQL连接:

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 ;

并像希望其他任何SELECT语句返回一行一样处理该查询的结果集.

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

对于支持OUT参数的MySQL连接器的较新版本，我认为您需要通过设置成员属性来指定这些参数为OUT参数:

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...

在不支持OUT参数的旧版MySQL连接器中，解决方法是使用MySQL变量作为参数来调用该过程.从过程调用返回的值保留在MySQL会话中.调用该过程后，我们将立即运行SELECT来检索用户变量的内容.

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.

这篇关于存储过程错误“例程...的参数1不是触发器之前的变量或NEW伪变量".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！