Temporary autoincrement column on Delphi + Zeos, multiple queries failed?

Question

Let's say I have very simple table :

CREATE TABLE Bands (Name VARCHAR(20) NOT NULL) ENGINE = INNODB;
INSERT INTO Bands VALUES ('Hammerfall'), ('Helloween'), ('Haggard'),
                         ('Therion'), ('Tarja');

And run a query :

SET @i := 0;
SELECT @i := @i + 1 No, Name
FROM Bands
WHERE Name LIKE 'H%'

On HeidiSQL and PhyMyAdmin it runs well :

1 Hammerfall
2 Helloween
3 Haggard

But in Delphi 2009 failed with error

SQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @i := @i + 1 No, Name FROM Bands WHERE Name LIKE 'H%'

I write the query in TZQuery.SQL box, Query.SQL.Text and Query.SQL.Add(). All failed.

Is there a way to run that query?

The real query I will use is to join some 20+ columns tables to generate some reports. But I cant even make this tiny thing works ~__~

I can't test it now but I suspect the problem lies in the fact that you are issuing 2 sql commands in the same execution of the query. Try leaving only the select sentence and setting the @i with myquery.ParamByName('i').AsInteger — Guillem Vicens
as fas as you are using ':' in your SQL I'm pretty shure it's the same problem as here stackoverflow.com/questions/15103180/…. Try to set ParamCheck to false. — bummi

Johan Johan · Accepted Answer · 2013-02-27T18:45:25

As stated by the commenters the problem is two-fold.

You cannot execute 2 statements in a single SQL (this is a protection against SQL injection).
You cannot use : if TQuery.CheckParams = true.

Note that it's a bad idea to have InnoDB tables with no explicit primary keys.
The problem is that if you don't create a PK, MySQL will create a hidden PK for you and that will slow join performance way down.

If you want InnoDB to run efficient it's advisable to create

integer primary key auto_increment

fields in every table.

Temporary autoincrement column on Delphi + Zeos, multiple queries failed?

1 Answers