Devart Forums

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

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

Delphi 7, Mydac 3.5.0.17 in WinXP
Mysql on Linux 4.1.7

Do you use TMyQuery or TMyTable?

TMyQuery

> Do you understand what I mean?

It looks as not. Please send a small complete sample.

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?

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.

Is a new release available with this issue fixed yet?

Thanks.

Yes of course

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

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

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

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.

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