Using a MySQL query with variables from C#

Question

In MySQL Workbench the MySQL query

SET @rownum=-1;
SELECT @rownum:=@rownum+1 AS row_num
FROM someTable

returns a table where the value of row_num starts at 0 and goes up by 1 for each row:

+---------+
| row_num |
+---------+
| 0       |
+---------+
| 1       |
+---------+
| 2       |

  ....

I am trying to execute the same query from C#.

string connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" +
    "UID=" + uid + ";" + "PASSWORD=" + password + ";" + "ALLOW USER VARIABLES = true;";

MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand command;
MySqlDataAdapter adapter = new MySqlDataAdapter();

connection.Open();
command = connection.CreateCommand();

command.CommandText = "SELECT @rownum := @rownum + 1 AS row_num FROM someTable";
command.Parameters.Add("@rownum", MySqlDbType.Int32);
command.Parameters["@rownum"].Value = -1;
adapter.SelectCommand = command;

DataTable table = new DataTable();
adapter.Fill(table);

The last line of the above results in the following MySqlException

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= -1 + 1 AS row_num FROM someTable'

Why does the query not work from C#?

@Ehsan: becasue := is the assignment operator in MySQL. Using @rownum = @rownun + 1 in the MySQL query results in the comparison -1 = (-1 + 1) and so each row has the value 0 for row_num — Josh Faure
– Josh Faure, Commented Dec 18, 2016 at 19:56
@Mihai: isn't `@rownum declared indirectly via command.Parameters? — Josh Faure
– Josh Faure, Commented Dec 18, 2016 at 20:04
@Mihai: and you can see that MySQL recognizes @rownum as a variable because the error message shows -1 after the := — Josh Faure
– Josh Faure, Commented Dec 18, 2016 at 20:09

Paddy · Accepted Answer · 2020-11-11 09:21:02Z

2

I found that this solution only works if specify the parameter "AllowUserVariables=True" in the connectionstring

answered Nov 11, 2020 at 9:21

Paddy

212 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pratik Bhajankar Over a year ago

Please add this type of answer in the comment box

Robert Koernke Over a year ago

I disagree with Pratik, this should be the accepted answer. This is the reason it didn't work in the first place, from the original question, and it helped me. If you look at his 'Connection-String' he has "Allow User Variables" incorrectly.

CodingYoshi · Accepted Answer · 2016-12-18 20:59:45Z

1

Remove these lines for parameters:

command.Parameters.Add("@rownum", MySqlDbType.Int32);
command.Parameters["@rownum"].Value = -1;

And use this query:

command.CommandText = "SET @rownum=-1;SELECT @rownum := @rownum + 1 AS row_num FROM someTable";

The way your are doing is both "@rownum" will be replaced with -1. So you end up with a query like this:

SELECT -1:= -1 + 1 AS row_num FROM someTable

edited Dec 18, 2016 at 20:59

answered Dec 18, 2016 at 20:14

CodingYoshi

27.3k7 gold badges68 silver badges75 bronze badges

5 Comments

Josh Faure Over a year ago

Execution of the query and any variable assignment takes place in the MySQL engine. Your string.Format results in exactly the same CommandText as my code uses and results in the same error too

CodingYoshi Over a year ago

Are you sure? I quickly had made an edit to my post. Please make sure you are using the latest edit because yes, the first one was incorrect but I made the edit so fast. You must have picked it up before I edited my answer.

Josh Faure Over a year ago

I have tried both (before the edit and after). Both result in the same error

CodingYoshi Over a year ago

Another approach. Please try that.

Robert Koernke Over a year ago

The connection string is wrong. Paddy should be marked as the correct answer.

Collectives™ on Stack Overflow

Using a MySQL query with variables from C#

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related