Can you use MySQL @ session variables in a C # connector?
I have a query that uses 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 a parameter input variable.
Is there a way to get around this?
Thank.
source to share
Hi Allison!
I faced the same problem lately, but found that by default, 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 the connection parameter Allow User Variables = True for it to work.
Hope I helped.
Hiago Takaki
source to share
There is another way, if you don't want to make the change global by setting up a connection string.
This can be done by surrounding the name with single quotes '
as follows.
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.
source to share