1

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 ~__~

ain
  • 22,394
  • 3
  • 54
  • 74
Muhammad Yassin Indra Buana
  • 41
  • 3
  • 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 Feb 27 '13 at 16:24
  • as fas as you are using ':' in your SQL I'm pretty shure it's the same problem as here http://stackoverflow.com/questions/15103180/adodb-component-causes-access-violation-on-win7-server-2008. Try to set ParamCheck to false. – bummi Feb 27 '13 at 16:50

1 Answers1

2

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

  1. You cannot execute 2 statements in a single SQL (this is a protection against SQL injection).

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

Johan
  • 74,508
  • 24
  • 191
  • 319
  • Thanks for your answer, but : 1. I don't need protection at this level, as its created on the fly and don't need user writing. 2. Set it to False, still doesn't work. --- Yes I know its bad idea, but its just a sample, I've cut down everything. What I need is selecting record and make it an incremental order list, those values will be on report on the far left. – Muhammad Yassin Indra Buana Feb 28 '13 at 02:42