LIMIT statement doesn't work

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for MySQL in Delphi and C++Builder
Post Reply
flightplan
Posts: 2
Joined: Wed 07 Feb 2007 01:46

LIMIT statement doesn't work

Post by flightplan » Wed 07 Feb 2007 02:05

Hi everybody;

Just few lines to ask a question..

A TSQLConnection and TSQLquery are implemented on the Form of my application. The Table on wich I try to send requests contains about 20 million records ( Database's name is "primer" and has only one LONGINT field called "PrimerNumber"). Here is the the request I send :

Code: Select all

      Form1.SQLQuery1.SQL.Text := 'SELECT * FROM primer  Limit 1,100'
      Form1.SQLQuery1.Active := true;
      Showmessage(IntToStr(Form1.SQLQuery1.RecordCount));
      
The last command always returns the total number of records the table contains and does not select the 100 first records.

I use Delphi 2006 with Core LAB DbExpress driver for Mysql with a 5.0.22 server version.

Thank you for your help.

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Thu 08 Feb 2007 08:31

This is dbExpress behaviour. It generates a wrong query to select record count for queries with a LIMIT clause. The template for selecting record count is stored in the SSelectCount constant of the SqlExpr unit.

flightplan
Posts: 2
Joined: Wed 07 Feb 2007 01:46

Post by flightplan » Fri 09 Feb 2007 15:07

Is there a relation between the dbExpress behaviour and the SQL browser built in Delphi ? I tried to send the same query via the Delphi's SQL query browser and the clause LIMIT works in this case !!!

Is there a solution to solve this problem ?

Thank you for your help.

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Fri 09 Feb 2007 16:08

The only dbExpress error in this case is that it uses wrong command to request record count. Actual record count returned by the server corresponds to the value used in the LIMIT clause.
I guess that the Delphi's tool just counts records in its storage. dbExpress does not have its own storage, so it should send a command to server to get record count.
This problem is in dbExpress code, so there is no possibility to fix it by changing code in our driver. You can migrate to MyDAC which has no such problems.

Post Reply