Prepared Statement conflicting with User Variables

Question

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");

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:

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

Has anyone encountered a similar issue?

Try using `Allow User Variables=True` in connection string parameter. I think `SET @userVar1 = 18` isn't simply enough for this issue. Provide your connection string to see if it's right. — Tetsuya Yamamoto, Nov 06 '17 at 00:57
@RufusL Please read the question more carefully, "@userVar1" is a user variable set in the SQL, not a parameter for the prepared statement. — Naxin, Nov 06 '17 at 01:00

score 3 · Accepted Answer · answered Nov 06 '17 at 01:17

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>

Manual Definition

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

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

Related issue:

How can I use a MySql User Defined Variable in a .NET MySqlCommand?

Prepared Statement conflicting with User Variables

1 Answers1

Linked