I have been trying to switch from Class::DBI to Rose::DB using Oracle as my
database. I discovered that limiting and paging do not work with oracle
because oracle does not regonize LIMIT.
To fix this I have created my own local Rose::DB::Object::QueryBuilder
module that contains these changes:
(line 511 in version .75)
#$qs .= "\nLIMIT " . $limit if(defined $limit && !$use_prefix_limit);
if (defined $limit && !$use_prefix_limit) {
if ($dbh->{'Driver'}{'Name'} eq 'Oracle') {
if ($limit =~ m/(\d+)(\sOFFSET\s(\d+))?/) {
my $o_size = $1;
my $o_start = $3 ? $3+1 : 0;
my $o_end = $o_start + $o_size - ($3 ? 1 : 0);
$qs = q[ select * from (select oquery.*, rownum oracle_rownum
from (] .
$qs .
q[) oquery where rownum <= ?) where oracle_rownum > ?];
push @bind, $o_end, $o_start;
}
} else {
$qs .= "\nLIMIT " . $limit;
}
}
Now limiting and paging works with DB Driver Name 'Oracle'. I don't know if
this is something you would like to include or if it makes sense to make a
Rose::DB::Object::QueryBuilder::Oracle module for Oracle specific query
operations.
Thanks,
-Kevin McGrath
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
