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

Question

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.

Answer 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

Answer 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.