cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gary Jarrel <garyjar...@gmail.com>
Subject setFetchOffset & setFetchLimit issue
Date Wed, 20 Jul 2011 14:52:04 GMT
Hi Guys,

I have an issue with using setFetchOffset & setFetchLimit together
with SQL Server 2008 R2.

Basically the code looks something like this:

SelectQuery query = getPrototypeQuery().queryWithParameters(params, true);
query.setFetchOffset(offset);
query.setFetchLimit(limit);
List<ConsentForm> result = getDataContext().performQuery(query);

I would have thought that given for example offset 10 and limit 20 I
could get a subset of the data in the database. Yet the generated code
that I get from the logs is as follows keeping in mind I am using
ordering and distinct:

Page 1: Offset 0 Limit 25
SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder,
t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress,
t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE
t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind:
1->CustomerID:8]
=== returned 25 rows. - took 26 ms

The above works as expected however the code appears strange.

Page 2: Offset 25 Limit 25
SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder,
t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress,
t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE
t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind:
1->CustomerID:8]
=== returned 0 rows. - took 20 ms

This already stops working

Page: 3: Offset 50 Limit 25
SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder,
t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress,
t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE
t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind:
1->CustomerID:8]
=== returned 0 rows. - took 20 ms.

Basically the first page appears to be working fine, but nothing afterwards.

I am not certain if I am using the combination of setFetchOffset and
setFetchLimit in a correct manner!

Any assistance would be appreciated. Thank you!

Gary

Mime
View raw message