Rails3 talks to SQL Server 2000-----ActiveRecord::

问题:

A simple Rails 3 application tries to talk to SQL Server 2000 using activerecord-jdbc-adapter. I tried both microsoft jdbc driver and jtds driver. seems to connect to database OK.

when it is time to SHOW data I get this error:

ActiveRecord::StatementInvalid in PencilsController#show

ActiveRecord::JDBCError: 'ROW_NUMBER' is not a recognized function name.: SELECT t.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY [pencils].id) AS _row_num, [pencils].* FROM [pencils] WHERE [pencils].[id] = 1) AS t WHERE t._row_num BETWEEN 1 AND 1

---

回答1:

The real problem here is the DB do not support proper LIMIT and OFFSET functions. Rails 2 would have the same problem.

For one of my old projects I had to use Sybase15, which is quite similar to old SQL Server. To make limit and offset work with that DB I had to write my own adapter:

https://github.com/arkadiyk/ar-sybase-jdbc-adapter .

It uses scrollable cursors to simulate offset. You can try to use it as it is with SQL SERVER 2000 or feel free to clone it and modify for your specific needs.

Update: The ROW_NUMBER function is called at https://github.com/jruby/activerecord-jdbc-adapter/blob/master/lib/arjdbc/mssql/limit_helpers.rb line 82 (SqlServerReplaceLimitOffset)

There is no replacement for this function. There are other ways of implementing OFFSET but there is no straight forward one.

回答2:

This is kind a old, but if anyone is passing through here, i put together another solution that uses activerecord-sqlserver-adapter that can be used to connect a rails 3.2 app to sqlserver 2000

https://bitbucket.org/jose_schmidt/rails-sqlserver-adapter-sql-server-2000-friendly