MyQuery + LIMIT bug?

MyQuery + LIMIT bug?

Postby sean » Mon 11 Apr 2005 16:23

Hi,

I have a MyQuery & datasource attached to a grid. I have a drop-down control that allows filtering of rows according to date patterns.

All this works fine, until I start using limits. In MyQuery the default limit is "-1", meaning no limit. If I set it to 200, say, and then apply a filter (i.e. WHERE clase), the program fails.

When I follow the SQL traffic with the DB monitor, I see that the actual query set to the server has:

SELECT xx from YY WHERE zz LIMIT 0, 200;
^^^^^
The problem is that the limit formatting is wrong.
If I don't use filters (ORDER BY), this problem does not appear.

Do you understand what I mean? Looks like a bug?

Thanks in advance,

Sean
sean
 

Postby Ikar » Tue 12 Apr 2005 07:30

Please supply us following information.

- Exact version of MyDAC. You can see it in About sheet of TMyConnection Editor
- Exact version of Delphi, C++ Builder or Kylix
- Exact version of MySQL server
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby sean » Tue 12 Apr 2005 20:44

Delphi 7, Mydac 3.5.0.17 in WinXP
Mysql on Linux 4.1.7
sean
 

Postby Ikar » Wed 13 Apr 2005 08:20

Do you use TMyQuery or TMyTable?
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby sean » Wed 13 Apr 2005 15:06

TMyQuery
sean
 

Postby Ikar » Thu 14 Apr 2005 08:57

> Do you understand what I mean?

It looks as not. Please send a small complete sample.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby sean » Tue 10 May 2005 07:31

I'm back again, need to get this cleaned up....

I have a TMyTable and try to set the "Limit" property to 200, instead of the default "-1", as design time.
When I run the program it gives the error "List Index out of boounds (-1)", and traces into the form creation where the Ttable is.

In the DB monitor, I see the following SQL:
SELECT * FROM history ORDER BY datetime DESC LIMIT 0, 200
with the SQL "command completed successfully".

I also have a CRDBgrid attached to this dataset, but disabling that makes no difference either.
If I activate the MyTable at design time it doesn't give an error.
If I copy the Mytable, set LIMIT 200, and open it at run time, no problem. If I link a DataSource to it and a Grid, then it gives the error above.

So its something to do with databasource/grids with LIMIT?
sean
 

Postby Ikar » Tue 10 May 2005 09:00

Thank you for information.
We reproduced your problem and fixed it. This fix will be included in the next MyDAC build. It will be available in about three weeks.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby sean » Mon 18 Jul 2005 20:00

Is a new release available with this issue fixed yet?

Thanks.
sean
 

Postby Ikar » Tue 19 Jul 2005 06:48

Yes of course
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby sean » Tue 02 Aug 2005 12:34

I've upgraded and can now retrieve, say first 500 records by setting LIMIT to 500.

Now I'd like to retrieve records 500 to 1000, and 100-1500 etc.

In MySQL syntax one would set LIMIT 500,1000

But MyDAC expects LIMIT to be an integer. How can do the above?
I tried to set the "Offset", buutu this seems to change the offwet within the 500 records, not the record num to start the LIMIT on.

Suggestions? Thanks in advance,

Sean
sean
 

Postby Ikar » Wed 03 Aug 2005 11:20

> Now I'd like to retrieve records 500 to 1000, and 100-1500 etc.

You must use Limit and Offset properties in combination.

> I tried to set the "Offset", buutu this seems to change the offwet within the
> 500 records, not the record num to start the LIMIT on.

Please describe more detailed.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby sean » Wed 03 Aug 2005 14:30

Lets say that I have a table 'taHistory' with 3'000 rows. For performance reasons I only want to fetch 500 rows at a time, to show in a Grid.

I set taHistory.Limit:=500, then I start the app and can see the first 500 rows in my grid.

Now I'd like to see the next 500 rows, so I create a button that sets taHistory.Offset:=500, I don't see any rows in the grid. Why?

Sean
sean
 

Postby Ikar » Thu 04 Aug 2005 08:13

Using Offset and Limit doesn't help in this case, their setting requires reopening a query with the loss of previous data.

Use instead of it FetchAll = False. You can read about specific of this mode in SDAC help.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby sean » Mon 15 Aug 2005 08:38

Thanks. In fact by opening and closing the query (as you suggest) it does what I need, just returning a subset of all rows.
sean
 


Return to MySQL Data Access Components