Trouble with Rails 2.1 RC + JRuby 1.1.1 +MS SQL Server 2005

Ruby JRuby
1

Hi,

I hope this is the right place to ask, and if not, that someone can
point me to the right place.

We have a working Rails app which I want to test with JRuby, but after
following the JRuby Wiki I am stumped by the behaviour of the
activerecord-jdbc-adapter. In a nutshell, if I run the rails console,
[NB, windows] ie:

jruby scripts\console

Then try to load a model and fetch some records:

c = Customers.find(:all)

I get the following exception backtrace:

ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError: Table
[Customers] does not exist: SELECT * FROM [Customers]
from
T:/ABM/BMSWeb/vendor/rails/activerecord/lib/active_record/connection_ada
pters/abstract_adapter.rb:151:in log' from C:/BMS_Web_For_ABM/jruby/1.1.1/lib/ruby/gems/1.8/gems/activerecord-jdbc- adapter-0.8/lib/active_record/connection_adapters/jdbc_adapter.rb:520:inexecute’
from
C:/BMS_Web_For_ABM/jruby/1.1.1/lib/ruby/gems/1.8/gems/activerecord-jdbc-
adapter-0.8/lib/active_record/connection_adapters/jdbc_adapter.rb:580:in
select' from C:/BMS_Web_For_ABM/jruby/1.1.1/lib/ruby/gems/1.8/gems/activerecord-jdbc- adapter-0.8/lib/active_record/connection_adapters/jdbc_adapter.rb:505:injdbc_select_all’
from
T:/ABM/BMSWeb/vendor/rails/activerecord/lib/active_record/connection_ada
pters/abstract/query_cache.rb:55:in select_all_with_query_cache' from T:/ABM/BMSWeb/vendor/rails/activerecord/lib/active_record/base.rb:559:infind_by_sql’
from
T:/ABM/BMSWeb/vendor/rails/activerecord/lib/active_record/base.rb:1314:i
n find_every' from T:/ABM/BMSWeb/vendor/rails/activerecord/lib/active_record/base.rb:518:infind’
from (irb):2:in `signal_status’

Of course, Customers DOES exists. The problem seems to be that the
tablename is delimited by square brackets.

If I then manually construct the SQL, then the select works, ie:

c = Customers.find_by_sql(‘select * from Customers’)
=> LOTS OF DATA

Configuration:
JRuby 1.1.1
Rails 2.1 RC Revision 9250 [in vendor via rake:freeze:edge]
Microsoft SQL Server 2005 “sqljdbc” 1.2
Activerecord-jdbc-adapter 0.8

My database.yml configuration looks like this:

adapter: jdbc
url:
jdbc:sqlserver://myserver;databasename=mydb;integratedSecurity=true;
driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

I can see from the activerecord-jdbc-adapter tracker that people are
submitting MS SQL Server patches - can someone pipe up and share their
working configuration details - perhaps that will give me a clue as to
how my config is different.

Looking forward to any help,

Regards

Neville

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
2

Neville,

Since you’re using recent versions of jruby and rails, it should be as
simple as changing your adapter line:

sqlserver => jdbcsqlserver

I believe Nick S. has some slides sitting around on his blog that
show how to make that dynamic so it will work regardless of whether
you start up with MRI or JRuby.

  • Larry

On Wed, May 21, 2008 at 11:35 PM, Neville B.
[email protected] wrote:

jruby scripts\console
T:/ABM/BMSWeb/vendor/rails/activerecord/lib/active_record/connection_ada
C:/BMS_Web_For_ABM/jruby/1.1.1/lib/ruby/gems/1.8/gems/activerecord-jdbc-
n `find_every’

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
3

On Thu, May 22, 2008 at 5:29 AM, Larry M.
[email protected] wrote:

Neville,

Since you’re using recent versions of jruby and rails, it should be as
simple as changing your adapter line:

sqlserver => jdbcsqlserver

Actually, this is only for the open source databases whose JDBC
adapters are freely available. Neville’s database.yml setup is correct
as far as I can see. I referred him here to see if there were others
working with SQL Server who might share experiences getting going.

/Nick

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
4

Thor,

As far as I can tell SQL Server did support quoting with [] at one
time. Do you know if that is no longer the case?

/Nick

On Thu, May 22, 2008 at 1:49 PM, Thor.henrichsen
[email protected] wrote:

On 22. mai. 2008, at 17.47, “Nick S.” [email protected] wrote:

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
5

Nick,

Sql server does work with quoting, in fact the sqlserver adapter (with
mri) quotes tables the same way.

The cause of the problem seems to be the driver, in my case jtds, that
throws an error when it cannot find ‘[tablename]’ in the schema.

/thor marius

On 22. mai. 2008, at 22.18, “Nick S.” [email protected] wrote:

On 22. mai. 2008, at 17.47, “Nick S.” [email protected]

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
6

Neville,

We’ve also been having trouble with this on sqlserver. the issue seems
to be the square brackets around the table name.

You can fix this by implementing quote_table_name in arjdbc yourself,
or by applying the patch I submitted to rubyforge under arjdbc last
week.

/thor m

On 22. mai. 2008, at 17.47, “Nick S.” [email protected] wrote:

Actually, this is only for the open source databases whose JDBC

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
7

Thanks Thor!

to be the square brackets around the table name.

You can fix this by implementing quote_table_name in arjdbc yourself,
or by applying the patch I submitted to rubyforge under arjdbc last
week.

/thor m

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
8

Neville B. wrote:

failed with “table not found” under JRuby + RoR + Jdbc.
“table not found” (or, more precisely, “Table x does not exist”) comes
from ar-jdbc when it looks up column information (around line 460 of
JdbcAdapterInternalService.java). It does this for each model and for
queries that aren’t associated directly with a model. On SQL Server, it
doesn’t match views, and if you “SELECT * FROM func()”, it will probably
report that the table “func” doesn’t exist.

SQL Server 2005

/Nick

yourself, or by

working with SQL Server who might share experiences getting going.

To unsubscribe from this list, please visit:

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
9

Good point Matt,

“table not found” (or, more precisely, “Table x does not exist”) comes
from ar-jdbc when it looks up column information (around line 460 of
JdbcAdapterInternalService.java). It does this for each model and for
queries that aren’t associated directly with a model. On SQL Server,
it
doesn’t match views, and if you “SELECT * FROM func()”, it will
probably report that the table “func” doesn’t exist.

Yes, this is the behaviour I am observing.

Its worth noting that MRI RoR does not behave this way, and so anyone
using SQL Views or SQL Functions with MRI RoR wont be able to migrate to
JRuby RoR.

Kindly,

Neville

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
10

FWIW, I thought the problem might be the Microsoft Jdbc driver, however
I have been playing with JRuby DBI:JDBC and a slightly modified version
of the “standard” Rails SQL Server Adapter and ‘[tablename]’ works just
fine.

So it seems to me that perhaps the jdbc_adapter is doing a table lookup
somewhere and failing due to the [tablename] syntax.

In fact I was led to try JRuby DBI because we use a SQL Server Function
execute in our app which works just fine under MRI + RoR + DBI:ADO, but
failed with “table not found” under JRuby + RoR + Jdbc.

However the SQL Server function execute works fine under JRuby + RoR +
DBI:JDBC.

mri) quotes tables the same way.

The cause of the problem seems to be the driver, in my case jtds, that
throws an error when it cannot find ‘[tablename]’ in the schema.

/thor marius

On 22. mai. 2008, at 22.18, “Nick S.” [email protected]
wrote:

Neville,

simple as changing your adapter line:
/Nick

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
11

Matt B. wrote:

Neville B. wrote:

failed with “table not found” under JRuby + RoR + Jdbc.
“table not found” (or, more precisely, “Table x does not exist”) comes
from ar-jdbc when it looks up column information (around line 460 of
JdbcAdapterInternalService.java). It does this for each model and for
queries that aren’t associated directly with a model. On SQL Server, it
doesn’t match views, and if you “SELECT * FROM func()”, it will probably
report that the table “func” doesn’t exist.

Hello G.s,
I ran into the same problem of ‘non existing table’, although the table
(not a view) is standing in the (SQL Server 2005) database.
So my first feeling was that AR 2.1 was faulty. So I made a quick
comparison (running 2.0.2 vs. 2.1 => both same activerecord-jdbc-adapter
0.8 version) and, yes, program is running fine with AR 2.0.2.
not anymore with 2.1 :{

I’ve been tracing into AR code but without success till now.
Anyone out there with a little idea?
(i’m using jRuby 1.1.2)

thanks thanks thanks thanks thanks in advance!!!