ar-sybase-jdbc-adapter

ar-sybase-jdbc-adapter enhances activerecord-jdbc-adapter (Rails 3) to support .limit and offset for Sybase ASE DB.

At the moment Sybase ASE version 15 or grater is required. If you need it work for ASE version 12, please open an Issue

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

If you have any issues with the adapter please add an Issue or fork the project and send a pull request.

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

Implementation notes

If .limit with no .offset or .count methods is used, the adapter simply adds “TOP” keyword to SQL and sends it to the Sybase server:

User.limit(10)

produces:

SELECT TOP 10 users.* FROM users

The adapter has to rely on Java code to implement .offset or when .count is used together with .offset or .limit. In this case adapter will generate SQL like it was MySQL query:

User.limit(10).offset(20)

produces

SELECT  users.* FROM users LIMIT 10 OFFSET 21

This can be confusing if you are looking at the log file.

Java layer parses the SQL and executes it as multistep scrollable cursor query:

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

Unfortunately this approach is not very efficient for very large OFFSET values. Also scrollable cursor works for Sybase ASE starting from version 15.

I am not a Sybase expert, so Please let me know if you are aware of more efficient ways to do limit and offset.

Known issues

I am aware of a very strange issue where the adapter 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 adapter works fine by adding the “TOP” keyword to your SQL query: