Bug #116730 Mysql.Data C# Net connector version 9.1.0 Mysql.Data.Mysqlclient.net
Submitted: 20 Nov 2024 13:01 Modified: 20 Nov 2024 13:56
Reporter: Gianni Alberti Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:9.1. OS:Windows (10)
Assigned to: CPU Architecture:Any
Tags: formula in select statemnt

[20 Nov 2024 13:01] Gianni Alberti 
Description:
For emulated ROW_NUMBER() function on MsqlServer  5.7.38

string Query="SELECT (@r := @r+1) as id
MysqlDataAdapter Adapter
MysqlDataCommand CMD

CMD = new MysqlCommand(Query,connection)
cmd.Parameters.AddWithValue("@r",0)
adapter = new MysqlAdapter(CMD)
adapter.fill(TABLE)

Syntax error near '':= 1 + 1 ) as ID' at line 1

How to repeat:
Using Visual studio 2022

Include Mysql.Data.Mysqlclient

string Query="SELECT (@r := @r+1) as id
MysqlConnection connection;
MysqlDataAdapter Adapter;
MysqlDataCommand CMD
DataTable TABLE;

CMD = new MysqlCommand(Query,connection)
cmd.Parameters.AddWithValue("@r",0)
adapter = new MysqlAdapter(CMD)
adapter.fill(TABLE)
[20 Nov 2024 13:20] MySQL Verification Team 
Hi Mr. Alberti,

Thank you for your bug report.

However, you got a syntax error.

That means that 5.7  syntax that you wanted to emulate from 5.7 is not supported in server version  9.1.

Can you provide us with a full test case, so that we can confirm ??

CREATE TABLE, a necessary number of INSERTs and a query that you would like to run on 9.1.

Thank you very much in advance.
[20 Nov 2024 13:46] Gianni Alberti 
I found the reason for the syntax error.
Problem (not clear explained in the manual) was the session
variable definition :
   instead of @r as in my example the correct definition is @'r'

The query now can be safely changed as

    Query=" SET @'r'=0; SELECT (@'r' := @'r' + 1) as ID FROM ....."

Thanks anyway for the fast answer. Regards
[20 Nov 2024 13:56] MySQL Verification Team 
Ciao Signor Alberti,

Non c'รจ niente per cui ringraziarci.

Hai fatto tutto da solo.