Can you use MySQL @ session variables in the C# connector?

I have a query that utilizes MySQL session variables (note the @rank variable)

SELECT Rank, UserId, CurrentVDOT
FROM
    (
        SELECT @rank := @rank + 1 AS Rank, UserId, MaxVDOT AS CurrentVDOT
        FROM
            (
                SELECT UserId, MAX(VDOT) AS MaxVDOT
                FROM
                    (
                        SELECT U.UserId, U.VDOT
                        FROM
                            (
                                SELECT UserId, MAX(Created) AS Created
                                FROM UserVDOT
                                GROUP BY UserId
                            ) G
                        INNER JOIN UserVDOT U
                        ON U.UserId = G.UserId
                            AND U.Created = G.Created
                    ) M
                GROUP BY UserId
                ORDER BY MaxVDOT DESC
            ) R, (SELECT @rank := 0) foo
    ) F
WHERE F.UserId = @UserId;

If I try to execute this against the C# MySQL connector, it tries to tell me that I need to declare @rank as an input parameter variable.

Is there any way around this?

Thanks.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Hello Allison!

I was facing the same problem recently, but I found that by default,
the SQL connection disables the use of variables in queries. To solve
this problem you can enable the connection string as follows:

Connection String:

server=192.168.0.0;password=root;User Id=root;Persist Security Info=True;database=my_db;Allow User Variables=True

You must enter connection parameter Allow User Variables=True to make it work.

I hope I’ve helped.

Hiago Takaki

Method 2

There is another way if you don’t want to make the change global by setting the connection string.

This can be done by surrounding the name with single quotes ' like the following.

SELECT Rank, UserId, CurrentVDOT
FROM
    (
        SELECT @'rank' := @'rank' + 1 AS Rank, UserId, MaxVDOT AS CurrentVDOT
        FROM
            (
                SELECT UserId, MAX(VDOT) AS MaxVDOT
                FROM
                    (
                        SELECT U.UserId, U.VDOT
                        FROM
                            (
                                SELECT UserId, MAX(Created) AS Created
                                FROM UserVDOT
                                GROUP BY UserId
                            ) G
                        INNER JOIN UserVDOT U
                        ON U.UserId = G.UserId
                            AND U.Created = G.Created
                    ) M
                GROUP BY UserId
                ORDER BY MaxVDOT DESC
            ) R, (SELECT @'rank' := 0) foo
    ) F
WHERE F.UserId = @UserId;

It will now be treated as a session variable.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0