unable to use mysql assigning operator in c#

Question

I am trying to use assigning operator @a := 0 for crossjoin (mysql) in my c# program. But it throws error saying

Fatal error encountered during command execution. Parameter '@a' must be defined.

the below query executes well in my mysql tool. but fails to perform while using it in my C# programming.

string str = "select t1.x1,(@a := @a + t1.x1) x2 from table1 t1 cross join (select @a := 0) params";
MySqlCommand cmd = new MySqlCommand(str, connection);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();

Output should be:

x1 x2

1 1

2 3

3 6

4 10

5 15

How do i solve this? or is there a work around for this?

What's the C# code you're using to execute the query? [This post](https://stackoverflow.com/questions/56245789/is-it-possible-to-use-mysql-assignment-operator-in-a-mysqlcommand) and [this one](https://stackoverflow.com/questions/41212179/using-a-mysql-query-with-variables-from-c-sharp) suggest removing any parameters set in C# for the variable in question. — Diado, Feb 25 '20 at 08:45
The query code is legal for MySQL (except the `ORDER BY` clause needed for deterministic result). So this is c# issue (and the error message is c# too). Try `string str = "SET @a := 0; select x1,(@a := @a + x1) x2 from table1;";` — Akina, Feb 25 '20 at 08:57
Try: string str = "select t1.x1,(\@a := \@a + t1.x1) x2 from table1 t1 cross join (select \@a := 0) params"; and use cmd.ExecuteReader(); instead cmd.ExecuteNonQuery(); — Fran Cerezo, Feb 25 '20 at 09:01
.NET thinks @a is a not declared SqlParameter, if you can not escape @ character, you are going to get desired result set by other way, maybe linq. Or you can create a stored procedure and call it. This way you avoid the problem with @ — Fran Cerezo, Feb 25 '20 at 10:16

score 3 · Accepted Answer · answered Feb 25 '20 at 22:08

By default, MySQL Connector/NET does not allow variables in SQL statements, only command parameters. If you use MySqlConnector you will get a more helpful error message:

Parameter '@a' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string.

As this states, add AllowUserVariables=true to your connection string to permit the use of variables in SQL commands.

unable to use mysql assigning operator in c#

1 Answers1