准备好的语句与用户变量冲突 [英] Prepared Statement conflicting with User Variables

问题描述

在 Pomelo MySQL C# 连接器中使用预准备语句时，添加参数的语法如下:

When using a prepared statement in the Pomelo MySQL C# Connector, the syntax for adding parameters is as such:

String sqlCommand = "select * from db where username=@param1"
MySqlCommand command = connection.CreateCommand();
command.Parameters.AddWithValue("@param1", "Ben");

我遇到的问题是因为在 MySQL 中使用用户定义变量的语法是:

The issue I'm having is that because the syntax for using User-Defined Variables in MySQL is:

String sqlCommand = "SET @userVar1 := 18; select * from db where username=@param1 AND age > @userVar1"

请注意，这里使用用户变量只是为了演示语法.我在其中使用它的真实示例需要使用准备好的语句和用户变量.但是，由于这种语法冲突，我得到了:

Note that the use of the user variable here is simply to demonstrate syntax.The real example that I'm using this in requires the use of both prepared statements and user variables. However, because of this syntax conflict I'm getting:

在命令执行期间遇到致命错误.--->Pomelo.Data.MySql.MySqlException:必须定义参数@userVar1"

Fatal error encountered during command execution. ---> Pomelo.Data.MySql.MySqlException: Parameter '@userVar1' must be defined

有人遇到过类似的问题吗?

Has anyone encountered a similar issue?

推荐答案

至于在查询字符串中启用带有用户自定义变量的SET，需要声明Allow User Variables=True 在连接字符串中，在 web.config 或 MySqlConnection 定义中:

As for enable SET with user-defined variables in a query string, you need to declare Allow User Variables=True in connection string, either in web.config or in MySqlConnection definition:

Web.config

<connectionStrings>
    <add name="DefaultConnection" connectionString="server=ServerName;database=DatabaseName;uid=UserName;pwd=Password;
     Allow User Variables=True" />
</connectionStrings>

手动定义

string connectionString = @"server=ServerName;database=DatabaseName;uid=UserName;pwd=Password;
                           Allow User Variables=True";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
    string sqlCommand = "SET @userVar1 := 18; select * from db where username=@param1 AND age > @userVar1"
    connection.Open();
    using (MySqlCommand command = new MySqlCommand(sqlCommand, connection))
    {
        // add required parameters here
        // and call ExecuteReader() afterwards
    }
}

从 .NET Connector 5.2.2 版开始可用的用户定义变量设置，如 这个解释.

The user-defined variable setting available starting from version 5.2.2 of .NET Connector as provided in this explanation.

相关问题:

我该怎么做在 .NET MySqlCommand 中使用 MySql 用户定义变量?

这篇关于准备好的语句与用户变量冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！