Using @variables:= in delphi query does not work

Question

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 ":" in ZQuery1.
How do I fix this? I need to use Delphi, because I cannot put this select in a MySQL procedure.
Zeos 6 does not support MySQL procedures that return a resultset.

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.

Is that valid MySQL? (I don't use MySQL, but something about the IF seems ... off; The MySQL CLI groks that okay?) — user166390
– user166390, Commented Apr 11, 2011 at 19:34
Yes it does, mistyped a "," for a ";" (fixed now), but other than that YES. — Johan
– Johan, Commented Apr 11, 2011 at 19:42
Hacked a solution, if anyone knows anything cleaner that would be great. Thanks all for thanking the time to help out. — Johan
– Johan, Commented Apr 11, 2011 at 21:07

Jan Doggen · Accepted Answer · 2011-04-11 19:37:25Z

2

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.

answered Apr 11, 2011 at 19:37

Jan Doggen

9,08615 gold badges82 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Johan Over a year ago

That will still give an error as soon as I make the query active. Or an I missing something. BTW I'm not bothered about the speed of preparing queries, the dataset this query works on is always small.

Nicholas Ring · Accepted Answer · 2011-04-11 20:11:18Z

1

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 ':='

answered Apr 11, 2011 at 20:11

Nicholas Ring

111 bronze badge

2 Comments

Johan Over a year ago

Yep tried that, but the code is horribly complex and my patches do not seem to do anything.

Johan Over a year ago

+1 really is a good idea, if only I was smart/patient enough to figure the ZEOS code out.

dev-null-dweller · Accepted Answer · 2011-04-11 19:43:27Z

0

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

answered Apr 11, 2011 at 19:43

dev-null-dweller

29.5k3 gold badges68 silver badges87 bronze badges

2 Comments

Johan Over a year ago

The query is actually in Dutch, I translated stuff for the benefit of the general public, did not realize I'd get a conflict with group, will fix the code back to Dutch :-)

Johan Over a year ago

Anyway that was not the problem, just that the orig query was long, so I condensed it.

robmil · Accepted Answer · 2011-04-11 20:10:50Z

0

Try to set TZQuery.ParamCheck to False. That will disable automatic parameters creation, when ':' is a parameter marker.

answered Apr 11, 2011 at 20:10

robmil

3122 silver badges4 bronze badges

1 Comment

Johan Over a year ago

I think this is due to ZEOS, without ParamCheck it just skips params it facts it then fails on a param, but it still does not grok @x:= @y.

Johan · Accepted Answer · 2011-04-11 21:05:33Z

0

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.

edited Apr 11, 2011 at 21:05

answered Apr 11, 2011 at 20:35

Johan

77.4k28 gold badges205 silver badges346 bronze badges

2 Comments

Andriy M Over a year ago

Are you sure the problem was with @-like names and not with :=?

Johan Over a year ago

You misunderstand, the problem in delphi is with :=, however the problem between sessions is with the @variable.

Collectives™ on Stack Overflow

Using @variables:= in delphi query does not work

5 Answers 5

1 Comment

2 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related