MySql "SET @variable" throwing Fatal Error in C# code

add ;Allow User Variables=True to the connection string.

This is a problem I discovered when upgrading MySql.Data.dll from 1.0 to 6.9. For example:

MYSQL:

SET @foo = 'foo'

Message: Fatal error encountered during command execution.

I suspect the MySql.Data is trying to interperate the @foo as a prepared statement variable, and wants me to populate it. On pain of death. The solution therefore seem to be to tell MySql.Data that this is a literal '@' and not a token.

There may be other ways, but the pragmatic solution I have found is to quote the variable name in back-quotes:

SET @`foo` = 'foo'
SELECT @`foo`

I hope this helps.

The code basically does this:

• load all SQL commands from a file into a collection of strings.
• iterate through that collection from the top:
  • make a new MySqlCommand (thereby creating its own scope!)
• MySQL throws an error on the 3rd line of code, as the variable @oldguid hasn't been declared.

If you want to be able to use variables, use one MySqlCommand object, and call ExecNonQuery() once.

Your best bet is to create a stored procedure. Perhaps it'll need 2 parameters for your "guid"/integer values.

The reason it works in SQLYog is that all those statements are running in the same context, rather than in different sessions.

Андрей's suggestion worked for me.

I have a rather complex procedure in my MySQL database which I dont want to include in my C# code. Here is how I do it:

//Open connection
string conString = "server=myserver; Uid=myusername; database=myscheme; Pwd=mypwd";
MySqlConnection con = new MySqlConnection(conString);

try
{
    //Set first variable, @START
    string sql = "SET @START = '" + date1 + "'";
    MySqlScript cmd = new MySqlScript(con, sql);
    cmd.Connection.Open();
    cmd.Execute();

    //Set second variable, @END
    sql = "SET @END = '" + date2 + "'";
    cmd = new MySqlScript(con, sql);
    cmd.Execute();

    //Send procedure call
    MySqlDataAdapter MyDA = new MySqlDataAdapter();
    sql = "call MY_DB_PROCEDURE";
    MyDA.SelectCommand = new MySqlCommand(sql, con);

    //From here you can process the DB response as you want
    //For instance, display the results in a datagridview
    DataTable table = new DataTable();
    MyDA.Fill(table);

    BindingSource bSource = new BindingSource();
    bSource.DataSource = table;

    //"dataGridView1" is already placed in the active form
    dataGridView1.DataSource = bSource;
    dataGridView1.Visible = true;

}
catch (Exception e)
{
    //Error handling procedure
}
finally
{
    if (con.State == ConnectionState.Open)
    {
        con.Dispose(); // return connection to the pool
    }
}

EDIT

A more elegant solution is to add "Allow User Variables=True" to the connection string. Example as follows:

//Open connection
string conString = "server=myserver; Uid=myusername; database=myscheme; Pwd=mypwd; Allow User Variables=True";
MySqlConnection con = new MySqlConnection(conString);

try
{
    //Open connection
    con.Open();

    //Set first variable, @START
    //Set second variable, @END
    //Send procedure call
    string sql = "SET @START = '" + date1 + "';" +
                 "SET @END   = '" + date2 + "';" +
                 "CALL MY_DB_PROCEDURE";

    MySqlDataAdapter MyDA = new MySqlDataAdapter();
    MyDA.SelectCommand = new MySqlCommand(sql, con);        

    //From here you can process the DB response as you like
    //...


}
catch (Exception e)
{
    //Error handling procedure
}
finally
{
    if (con.State == ConnectionState.Open)
    {
        con.Dispose(); // return connection to the pool
    }
}

Wrote procedures containing variables (@ name_var). An exception was thrown. Helped change the class MySqlCommand class MySqlScript

add connection string ; Allow User Variables=True