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
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
