[Dbix-class] Arbitrary SQL - Sybase backend

[Raimund Hook]

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.