[PATCH] Fix problem using setLimit and setOffset with Sybase

[Terence Kwan <te...@hotmail.com>]

Hi,

I tested Torque 3.0 with Sybase ASE 11.9.2 and found some problem with 
Criteria.setLimit() and Criteria.setOffset(). There are two issues here.

1. Criteria.setLimit() will cause an Exception to be thrown with doSelect(): 
Incorrect syntax near 'LIMIT'.

Clearly BasePeer is using the SQL keyword "LIMIT" which Sybase doesn't 
support. Close examination revealed that DBSybase.getLimitStyle() is already 
returning DB.LIMIT_STYLE_SYBASE. However, the case has not been taken care 
of in BasePeer.createQuery. This patch put in the code to allow 
query.setRowCount to be called if db.getLimitStyle() is
DB.LIMIT_STYLE_SYBASE.

2. Having got setLimit() working, there came the second problem with 
setOffset(). Sybase (as of ASE 11.9.2) haven't got native offset support. 
Offset will thus be done by the Village APIs. If a RDBMS supports offset and 
limit natively, offset is applied before limit. E.g. if we want result rows 
10 to 19, we will set offset to 10 and limit to 10 in Criteria. Now that 
limit is handled by the Sybase server, no row will be returned in the above 
case, as 10 rows would be returned and an offset from the 10th row would be 
applied. Thus in Sybase's case, the actual limit passed on to the Village 
API should be 10 + 10 = 20, i.e. offset + limit as indicated by the given 
Criteria object.

I have tested (my own testcases) the patch with Sybase ASE 11.9.2 as well as 
MySQL 3.23 to ensure compatibility.

Regards,
Terence Kwan.


$ diff -u BasePeer.java.orig src/java/org/apache/torque/util/BasePeer.java
--- BasePeer.java.orig  Tue Jan 21 23:56:57 2003
+++ src/java/org/apache/torque/util/BasePeer.java       Wed Jan 22 18:14:11 
2003
@@ -1329,7 +1329,13 @@
         else if (limit > 0 && db.supportsNativeLimit()
                  && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
         {
-            limitString = String.valueOf(limit);
+            // For RDBMS that supports native limit but not native offset, 
e.g. Sybase, it is
+            // necessary to add the offset to the limit. Otherwise 
incorrect result
+            // will be returned when offset is applied to the already 
limited resultset.
+            if ((offset > 0) && (!db.supportsNativeOffset()))
+              limitString = String.valueOf(offset + limit);
+            else
+              limitString = String.valueOf(limit);

             // The following is now done in createQueryString() to enable 
this
             // method to be used as part of Criteria.toString() without 
altering
@@ -1344,9 +1350,16 @@

         if (limitString != null)
         {
-            query.setLimit(limitString);
-        }
+            switch (db.getLimitStyle())
+            {
+                case DB.LIMIT_STYLE_SYBASE:
+                    query.setRowcount(limitString);
+                    break;
+                default :
+                  query.setLimit(limitString);
+            }

+        }
         return query;
     }






_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail