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

  1. Install

    gem install ar-sybase-jdbc-adapter
    
  2. 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.

  1. If the offset is a large number, the space taken by the temptable can fill up the tempdb.

  2. 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:

  1. Scrollable cursor works for Sybase ASE starting from version 15.

  2. 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 © 2011 arkadiy kraportov. See LICENSE.txt for further details.