ar-sybase-jdbc-adapter
ar-sybase-jdbc-adapter enhances activerecord-jdbc-adapter (Rails 3) to support “limit” and “offset” for Sybase ASE DB.
**This project is a proof of concept that Sybase ASE can work nicely with Rails** Once the project reaches “close to production” functionality I will try to merge it with activerecord-jdbc-adapter
Usage
-
Install
gem install ar-sybase-jdbc-adapter
-
Configuration
To use this gem, set the “dialect” configuration parameter to “sybase_jtds”. Example:
development:
adapter: jdbc
username: user_name
password: password
database: your_database
dialect: sybase_jtds
driver: net.sourceforge.jtds.jdbc.Driver
url: jdbc:jtds:sybase://host:port/db_name
Offset in Sybase ASE
Sybase is an awful DB in term of support for “offset”. I found 2 ways to do it for generic queries:
1. Use TempTables:
select top <limit + offset> * into #tt from (<original query with NO order by>) t ORDER BY <original order> ASC
select top <limit> * from #tt into #tt_sorted ORDER BY <original order> DESC
select * from #tt_sorted ORDER BY <original order>
There are 2 major drawbacks here.
-
If the offset is a large number, the space taken by the temptable can fill up the tempdb.
-
The original query should be parsed to strip out “ORDER BY”
2. Use a scrollable cursor:
declare crsr insensitive scroll cursor for
select * from <original query>
go
open crsr
set cursor rows <limit> for crsr
fetch absolute <offset> from crsr
close crsr
deallocate crsr
The problems here are:
-
Scrollable cursor works for Sybase ASE starting from version 15.
-
Cursors are not very efficient in Sybase ASE, and very inefficient in Sybase IQ.
I am not a Sybase expert, so *Please let me know if you are aware of more efficient ways to do limit and offset.*
Limit and Count in Sybase ASE
There is another interesting issue with Sybase DB I have just discovered. To implement “limit” I add “TOP <limit>” to the query and it works fine. To check how many records this query returns the obvios thing is to run something like
select count(*) from (select top 10 * from table_name) t -- DOES NOT WORK!
But it does not work! The result of this query will be total number of rows in the table. So the only solution will be to fall back to ‘cursor` and get `@@rowcount` to get the number of rows.
declare crsr insensitive scroll cursor for
select * from <original query>
go
open crsr
set cursor rows <limit> for crsr
fetch absolute <offset> from crsr
select @@rowcount
close crsr
deallocate crsr
Known issues
I am aware of a very strange issue where the driver does not work when the very first query uses “limit()”.
e.g.
$ rails c
Loading development environment (Rails 3.0.3)
irb(main):001:0> Client.limit(10).to_sql
=> "SELECT clients.* FROM clients LIMIT 10"
Otherwise, the driver works fine by adding the “TOP” keyword to your SQL query:
e.g.
$ rails c
Loading development environment (Rails 3.0.3)
irb(main):001:0> Client.scoped.to_sql
=> "SELECT clients.* FROM clients"
irb(main):002:0> Client.limit(10).to_sql
=> "SELECT TOP 10 clients.* FROM clients"
Contributing to ar-sybase-jdbc-adapter
-
Check out the latest master to make sure the feature hasn’t been implemented or the bug hasn’t been fixed yet
-
Check out the issue tracker to make sure someone already hasn’t requested it and/or contributed it
-
Fork the project
-
Start a feature/bugfix branch
-
Commit and push until you are happy with your contribution
-
Make sure to add tests for it. This is important so I don’t break it in a future version unintentionally.
-
Please try not to mess with the Rakefile, version, or history. If you want to have your own version, or is otherwise necessary, that is fine, but please isolate to its own commit so I can cherry-pick around it.
Copyright
Copyright © 2011 arkadiy kraportov. See LICENSE.txt for further details.