Return-Path: X-Original-To: apmail-cayenne-user-archive@www.apache.org Delivered-To: apmail-cayenne-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id CE79273A7 for ; Wed, 20 Jul 2011 14:52:33 +0000 (UTC) Received: (qmail 11688 invoked by uid 500); 20 Jul 2011 14:52:33 -0000 Delivered-To: apmail-cayenne-user-archive@cayenne.apache.org Received: (qmail 11593 invoked by uid 500); 20 Jul 2011 14:52:32 -0000 Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cayenne.apache.org Delivered-To: mailing list user@cayenne.apache.org Received: (qmail 11585 invoked by uid 99); 20 Jul 2011 14:52:32 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 20 Jul 2011 14:52:32 +0000 X-ASF-Spam-Status: No, hits=-0.7 required=5.0 tests=FREEMAIL_FROM,RCVD_IN_DNSWL_LOW,SPF_PASS,T_TO_NO_BRKTS_FREEMAIL X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of garyjarrel@gmail.com designates 209.85.161.41 as permitted sender) Received: from [209.85.161.41] (HELO mail-fx0-f41.google.com) (209.85.161.41) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 20 Jul 2011 14:52:26 +0000 Received: by fxg9 with SMTP id 9so1772460fxg.14 for ; Wed, 20 Jul 2011 07:52:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; bh=XcQrPnApog248TDfuc1kH5+PIf0hFHdD/ZRU7GDALfQ=; b=F3TFaadHrn0WAo2h0ANB0zvYnqe83rVcbXv8ExyKT5/8qF4BIRNUEvS2ENFpjI5hnQ Ht3dc1s/NRoKOPeykUaOfxr5joTUdk2C48+3QsUpuLsSmtmf6wEsV7DExHSPfzvxuCuE JY0ejvmGdzYPidYCh02iOLbNO3Sk6/B73xsNk= MIME-Version: 1.0 Received: by 10.204.3.21 with SMTP id 21mr2595378bkl.6.1311173524682; Wed, 20 Jul 2011 07:52:04 -0700 (PDT) Received: by 10.204.39.5 with HTTP; Wed, 20 Jul 2011 07:52:04 -0700 (PDT) Date: Thu, 21 Jul 2011 00:52:04 +1000 Message-ID: Subject: setFetchOffset & setFetchLimit issue From: Gary Jarrel To: user@cayenne.apache.org Content-Type: text/plain; charset=ISO-8859-1 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 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