Using @variables:= in delphi query does not work
I have the following problem.
ZQuery1.SQL.Text:=
' SELECT '+
' IF(q.rank2 = 1, @rank:= 1, @rank:= @rank + 1) AS rank '+
' ,q.* FROM ( '+
' SELECT groep.id - MinGroepId(groep.id) AS rank2 '+
' ,groep.otherfields '+
' FROM groep '+
' ORDER BY rank2 ) q; ';
ZQuery.Open;
When I run this code I get an exception Incorrect token followed by "开发者_如何学Python:"
in ZQuery1.
P.S.
I'm using Delphi 2007 and MySQL 5.1 with ZEOS 6.6.6. Although I'm pretty sure the versions don't matter. I'm not willing to switch versions as I'm too far into the project.This can't be done, you can only parameterize the value. Best you can do is SQL.Text := StringReplace() but you lose the speed of preparing queries.
MySQL has the ability to have user variables (session based) that are referred to @ (so I hate to say that LaKraven is slightly off the mark). I had the same problem with the Dac for MySQL (http://www.microolap.com/products/connectivity/mysqldac/) at work. They corrected by putting in special check to see if the character after ':' was an '=' and if it was, parameter replacement did not occur.
I do not know all that much about the Zeos components, so the only thing I can suggest is that you trace down the path of execution and see where the exception is occurring and patch the code to handle the character sequence of ':='
I don't know if this is the case here, but you have errors in your SQL: semicolon in IF
should be replaced with comma, there is comma missing after AS rank
and group
is reserved word so when used as table name it should be quoted in `` .
Try to set TZQuery.ParamCheck to False. That will disable automatic parameters creation, when ':' is a parameter marker.
OK, I hacked a solution.
But it sure is ugly, still it works (sorta).
EDIT, this one works in dbForge-MySQL and Delphi
First I created a stored function 'ranking' in MySQL, that stores a value and/or offset in @rank.
CREATE DEFINER = 'root'@'localhost'
FUNCTION MyDatabase.Ranking(NewRank INT, Addition INT)
RETURNS int(11)
BEGIN
IF NOT(NewRank IS NULL) THEN SET @rank:= NewRank; END IF;
IF NOT(Addition IS NULL) THEN SET @rank:= @rank + Addition; END IF;
RETURN @rank;
END
Next up, I changed the ZQuery1 to read something like:
select ranking(null,1) as rank
,groep.*
from groep
join (select ranking(0,null)) r
This works, and the full complex code in Delphi also works.(-_-')
Another triumph over the evil machines
So to recap.
@varname is persistent within a stored procedure (inside a single connection of course).
Exchanging @varname between the select statement and the stored procedure works in dbForge, but fails in Delphi.
精彩评论