A quick google search finds me: SELECT BOTTOM 5 * FROM (SELECT TOP 15 * FROM someTable ORDER BY orderColumns DESC)
Having a *very* brief look at the code, it seems some work might need to be done to separate the "paging" method out into DB specific modules... Does DBIC already use SQL::Abstract::Limit? Seems that has all the appropriate logic in there, but DB support is based on a "you have to read it list" rather than a specific module matching the DBD::module On 9/01/09 6:06 PM, "Raimund Hook" <[email protected]> wrote: > Hi > > I'm working with a system with a Sybase backend. > > I'm building a Catalyst application, and one of my pages pulls data from > a table with 20000+ rows. In the process of trying to paginate this > data, I've found that Sybase doesn't like the 'traditional' methods of > pagination, and in fact, the SQL generated has lead me down a long > frustrating journey to find an 'equivalent' command. > > (please forgive the Catalyst specific code, I know this isn't really a > Catalyst forum) > > my $accounts = $c->model('Common::Accounts')->search(undef, {columns => > [qw/AccountID AccountName/], page => $page, rows => 10}); > > with a SQLite backend, the SQL generated is: > > "SELECT me.AccountID, me.AccountName FROM Accounts me LIMIT 10 OFFSET > 20" (assuming $page = 3) > > Sybase doesn't support the LIMIT/OFFSET Syntax, and can only seem to do > a 'TOP x'-type of query. However, I've found the following query to work > (using Sybase's 'identity' column magic): > > "SELECT AccountID, AccountName, RowNum=identity(9) > INTO #temp_accounts > FROM Accounts > SELECT * FROM #temp_accounts WHERE RowNum BETWEEN 20 AND 30 > DROP TABLE #temp_accounts" > > I've tried to implement this as a custom ResultSource, but the problem > with that is the SELECT that wraps it. Sybase seems to have issues with > "An INTO clause is not allowed in a derived table." specifically. > > Essentially, I need a way to run my query directly, without being > wrapped in an additional SELECT. > > I believe there is a way to reference the dbh directly using > DBIx::Class::Storage::DBI->dbh, but I'd like to avoid that if at all > possible. (plus I'm not too sure how to invoke that properly in the > middle of my Catalyst app :P ) > > Any help here would be appreciated. > > Thanks and regards > Raimund Hook > raimund dot hook at is dot co dot za > Please note: This email and its content are subject to the disclaimer as > displayed at the following link > http://www.is.co.za/legal/E-mail+Confidentiality+Notice+and+Disclaimer.htm. > Should you not have Web access, send a mail to [email protected] and a copy > will be emailed to you. > > _______________________________________________ > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class > IRC: irc.perl.org#dbix-class > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ > Searchable Archive: http://www.grokbase.com/group/[email protected] > _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
