[castor-dev] Oracle LIMIT Patch

[Will Glozer]

I'm attaching a diff that adds support for the "limit" statement to the 
Oracle driver.  If it looks a little weird, that is because you need to do a 
nested select if you use an order by with the ROWNUM pseudocolumn, and you 
can't have any column name conflicts.

This only works with Oracle8i+ afaik; a side effect of using ROWNUM on joins 
will limit the number of objects returned on the N side of 1:N relations; 
and you can only select the first X rows, rather than rows X - Y, i.e:

query containing: "limit ?"
query.bind(10);

will retrieve rows 1-10.  Combine this with a where clause and you can get 
records in batches.

If someone would let me know when/if this is added to the official source 
tree, I'd greatly appreciate it.

Thanks,
Will


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

Index: OracleQueryExpression.java
===================================================================
RCS file: 
/cvs/castor/castor/src/main/org/exolab/castor/jdo/drivers/OracleQueryExpression.java,v
retrieving revision 1.13
diff -c -r1.13 OracleQueryExpression.java
*** OracleQueryExpression.java  2001/08/13 23:28:34     1.13
--- OracleQueryExpression.java  2001/08/22 21:30:40
***************
*** 68,74 ****
--- 68,82 ----
          super( factory );
      }

+     public void addColumn( String tableName, String columnName )
+     {
+         _tables.put( tableName, tableName );

+         String alias = _factory.quoteName(tableName + "_" + columnName);
+         _cols.addElement( _factory.quoteName( tableName + 
JDBCSyntax.TableColumnSeparator + columnName) +
+                           " " + alias);
+     }
+
      public String getStatement( boolean lock )
      {
          StringBuffer sql;
***************
*** 129,141 ****
              }
          }
          first = addWhereClause( sql, first );
!
          if ( _order != null )
            sql.append(JDBCSyntax.OrderBy).append(_order);

          // Use FOR UPDATE to lock selected tables.
          if ( lock )
              sql.append( " FOR UPDATE" );
          return sql.toString();
      }

--- 137,155 ----
              }
          }
          first = addWhereClause( sql, first );
!
          if ( _order != null )
            sql.append(JDBCSyntax.OrderBy).append(_order);

          // Use FOR UPDATE to lock selected tables.
          if ( lock )
              sql.append( " FOR UPDATE" );
+
+         if (_limit != null && _limit.equals("") == false) {
+             sql.insert(0, "SELECT * FROM (");
+             sql.append(") WHERE ROWNUM <= ").append(_limit);
+         }
+
          return sql.toString();
      }