[Dbix-class] Arbitrary SQL - Sybase backend

[Lee Standen]

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" <raimund.hook at is.co.za> 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 disclaimers at is.co.za 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/dbix-class@lists.scsys.co.uk
>