How to Use a MySQL User Defined Variable in a .Net MySQLcommand

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

I found the solution in this blog

I have to add

;Allow User Variables=True

to the connection string:

$connectionstring = "Server=$Server;Port=$port;Database=$DataBase;Uid=$User;Pwd=$Password;allow zero datetime=yes;Allow User Variables=True"

works. I tested it with version 6.3.6.0. of MySql.Data.

Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?

I found this blog, which tells, that with newer versions of .net Connector you have to add

;Allow User Variables=True

to the connection string.
Compare my SO question How can I use a MySql User Defined Variable in a .NET MySqlCommand?

Prepared Statement conflicting with User Variables

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?

Is it possible to use MySql assignment operator (:=) in a MySqlCommand?

The comment by @Uueerdo prompted me to try removing the cmd.Parameters.AddWithValue("r", 0); line from the C# code. This fixed the error and gave me the results I was expecting.

.NET MySqlCommand @ placeholder conflicted with MySQL variable

Have a look at the following MySql.Data.MySqlClient.MySqlException: Parameter ‘@id’ must be defined

Which states

Finally, I found that this was indeed a change in the .Net Connector.
(I’m not sure what version number but starting from 5.2.0 would be my
guess) Starting from version 5.2.2 of the Connector you should add
the Allow User Variables=True Connection String Setting in order to
use User Defined Variables in your SQL statements.

Example of Connection String:

 Database=testdb;Data Source=localhost;User Id=root;Password=hello;Allow User Variables=True

MySql SET @variable throwing Fatal Error in C# code

Actually, I don't think that you can pass this line SET @oldguid = 250006; into a mysqlcommand object (actually I don't know). What you should do is have your program put these values in a local variable, and then replace the parameter in your update queries.

Find a way to mix your code and this one:

        // This line should be outside the While loop
        Dictionary<string, string> variables = new Dictionary<string, string>();

        if (line.Trim().ToUpper().StartsWith("SET"))
        {
            List<string> commandLine;
            commandLine = line.Trim(' ',';').Split().Distinct().ToList();
            commandLine.Remove(string.Empty);

            // After next line, the 'variables' dictionary contains the name 
            // of the variable you want to set, and its value
            variables[commandLine[1]] = commandLine[3];

            // ...
            // Do something here (try catch, chatever, but NO MySqlCommand)
            // ...
        }
        else
        {
            // If the line contains any of the variables you previously set,
            // i will be the index of this variable, -1 otherwise
            int i = line.ContainsAnyOfAt(variables.Keys);
            while(i>=0)
            {
                // Here we replace the parameter by its value, for example:
                // UPDATE `creature` SET `guid`=@newguid WHERE `guid`=@oldguid;
                // becomes (after all loops):
                // UPDATE `creature` SET `guid`=202602 WHERE `guid`=250006;
                line = line.Replace(variables.Keys.ElementAt(i), variables.Values.ElementAt(i));
                i = line.ContainsAnyOfAt(variables.Keys,i+1);
            }

            // ...
            // This is where you should put the code of MySqlCommand
            // ...
        }

And here is the extension method ContainsAnyOfAt :

        public static int ContainsAnyOfAt<T>(this global::System.String source, IEnumerable<T> values, int startIndex = 0)
        {
            if (source == null) throw new ArgumentNullException("source");
            for (int i = startIndex ; i < values.Count(); i++)
            {
                if (source.Contains(values.ElementAt(i).ToString()))
                {
                    return i;
                }
            }
            return -1;
        }

Please give it a try and give feedback.
Greetings

Entity Framework : Set MySQL custom environment variables

Since your statement is not a query (i.e. does not return any result) you should use ExecuteStoreCommand. Something like this should work:

ctx.ExecuteStoreCommand("SET @my_var = 'test'")

MySQL User-Defined Variable Values Can't be Used as Identifiers. How do I do this?

may be you should try following approach:

set @databaseName := 'job_hunt_2';
SET @s = CONCAT('drop database ', @databaseName); 
PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

---

Related Topics