Sunday, September 03, 2006

Using the ActiveRecord-JDBC Adapter

Ola Bini, Nick Sieger, and others have been making great progress on the ActiveRecord-JDBC adapter lately. I figure it's a good time for a brief update on progress, and a short walkthrough on how to use it.

Where We Are, Where We're Going

There has been one official release of the ActiveRecord-JDBC adapter, a 0.1 that has basic SQL support for MySQL. It's available as a gem called "ActiveRecord-JDBC" (case-insensitive). It provides normal MySQL support for most major ActiveRecord operations today, and it's mostly the same code we used for our JavaOne demo.

Although the JDBC libraries give us plenty of information on database typing and precision, they provide practically nothing for generating DDL. Because of this, a large part of current ActiveRecord capabilities have to be written outside of the JDBC-aware code. DDL differs between databases, so like the core Rails project, we're forced to implement the various schema-related methods of ActiveRecord differently for different database vendors.

Currently, we're just reproducing the same code available in core Rails, with a little manipulation to work with ActiveRecord-JDBC, but we're hoping to work with the Rails team to abstract out the non-driver-specific portions of the core ActiveRecord adapters so the JDBC version can leverage the same code. I'm also hoping we'll be able to get the "jdbc" adapter name added to core Rails in the active_record.rb file where such adapters are listed. This will allow JRuby users to install Rails and the JDBC adapter and immediately start using them, without additional steps.

We are planning a second release of the JDBC adapter very shortly. It should have reasonably solid migrations/DDL support for MySQL, Oracle, and Firebird, as well as some level of support for HSQLDB, Derby, and others. It's moving along quickly now that it's been spun off into its own jruby-extras project. You can go there or to the jruby-extras SVN repository to grab the bleeding-edge source from SVN.

Getting up and running with ActiveRecord-JDBC

Despite the design complexities and our DDL woes, getting an ActiveRecord-based app up and running on JDBC is now very simple. This walkthrough proceeds from a stock JRuby install, and is based on current JRuby trunk (though it may work ok with 0.9.0 as well...we'll have a new release out soon).

  1. Ensure JRuby is set up and working correctly via the normal setup procedures
  2. gem install rails -y --no-rdoc --no-ri (ri and rdoc generation are still too slow under JRuby)
  3. gem install ActiveRecord-JDBC --no-rdoc --no-ri (this is the 0.1 version for now, but hopefully we'll have 0.2 out this week)
  4. Edit lib/ruby/gems/1.8/gems/activerecord.../lib/active_record.rb, adding jdbc to the list of RAILS_CONNECTION_ADAPTERS toward the bottom of the file:
    RAILS_CONNECTION_ADAPTERS = %w( jdbc mysql postgresql sqlite firebird sqlserver db2 oracle sybase openbase )
  5. Modify database.yml for your settings. For example, using MySQL:
    development:
    adapter: jdbc
    url: jdbc:mysql://localhost/testapp
    driver: com.mysql.jdbc.Driver
    username: testapp
    password: testapp
  6. As always, ensure the appropriate JDBC jar files are in your CLASSPATH
That's pretty much all there is to it. We'll get that second release out in a day or two so that migrations work and additional databases are supported, but these instructions won't change. I'll post here when that release is out.

How Well Does It Work?

It seems to work very well so far. As part of my JRuby on Rails presentation at RailsConf, I'll be demonstrating the new Depot application from Agile Web Development with Rails 2nd Ed. The entire app seems to work very well under JRuby, and I only had to make one additional manual change in the database because JRuby doesn't yet work well with edge rails (on which the book's examples are currently based). It's quick and responsive, and will only improve as we continue on.

Also keep in mind that the DDL issue only limits the JDBC adapter's usefulness for migrations or schema-manipulation purposes. Without DDL generation, it will still work with many, many databases without major modifications, since JDBC really shines here. Hopefully a future version of JDBC will provide DDL metadata or tools for manipulating database schemas. Until then, we'll continue to work with the Rails folks to find a good solution for DDL under the ActiveRecord-JDBC adapter.

7 comments:

Anonymous said...

Any plans to add sybase support to ActiveRecord-JDBC?

mortench said...

Will there be support for Microsoft SQL Server 2005?

Ola Bini said...

mr anonymous:

I would like to add sybase support, but I haven't been able to get hold of a Sybase DB to test against. If you can get this in some way, the process would be much easier.

mortench:

SQL Server works really well, but it's only tested against 2000. As far as I know, as long as there is a good JDBC driver for 2005, it will work unchanged. In other words; if you're lucky it works now.

Milhouse said...

Great work! I'll try it out versus iSeries DB2 and let you know how it works.

I've already hacked together an iSeries adapter using an iSeries ADO driver (Win32) thru DBI and a modified version of the IBM DB2 adapter.

Hasan said...

When I try to install rails as a gem indicated in the post, I get the following stacktrace:
./bin/jruby ./bin/gem install rails -y --no-doc --no-ri --install-dir /tmp/
org.jvyaml.BaseConstructorImpl.constructObject(BaseConstructorImpl.java:135): java.lang.NullPointerException: null (NativeException)
from org.jvyaml.BaseConstructorImpl.constructDocument(BaseConstructorImpl.java:108)
from org.jvyaml.BaseConstructorImpl.getData(BaseConstructorImpl.java:88)
from sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
from sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
from sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
from java.lang.reflect.Method.invoke(Method.java:585)
from org.jruby.javasupport.JavaMethod.invokeWithExceptionHandling(JavaMethod.java:180)
from org.jruby.javasupport.JavaMethod.invoke(JavaMethod.java:156)
... 199 levels...
from ./bin/gem:23:in `do_configuration'
from ./bin/gem:23:in `run'
from ./bin/gem:23
Complete Java stackTrace
java.lang.NullPointerException
at org.jvyaml.BaseConstructorImpl.constructObject(BaseConstructorImpl.java:135)
at org.jvyaml.BaseConstructorImpl.constructDocument(BaseConstructorImpl.java:108)
at org.jvyaml.BaseConstructorImpl.getData(BaseConstructorImpl.java:88)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jruby.javasupport.JavaMethod.invokeWithExceptionHandling(JavaMethod.java:180)
at org.jruby.javasupport.JavaMethod.invoke(JavaMethod.java:156)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jruby.runtime.callback.ReflectionCallback.execute(ReflectionCallback.java:140)
at org.jruby.internal.runtime.methods.CallbackMethod.internalCall(CallbackMethod.java:79)
at org.jruby.internal.runtime.methods.AbstractMethod.call(AbstractMethod.java:58)
at org.jruby.RubyObject.callMethod(RubyObject.java:371)
at org.jruby.RubyObject.callMethod(RubyObject.java:315)
at org.jruby.javasupport.JavaClass$1.execute(JavaClass.java:257)
at org.jruby.internal.runtime.methods.CallbackMethod.internalCall(CallbackMethod.java:79)
at org.jruby.internal.runtime.methods.AbstractMethod.call(AbstractMethod.java:58)
at org.jruby.RubyObject.callMethod(RubyObject.java:371)
at org.jruby.RubyObject.callMethod(RubyObject.java:323)
at org.jruby.evaluator.EvaluateVisitor$CallNodeVisitor.execute(EvaluateVisitor.java:574)
at org.jruby.evaluator.EvaluationState.executeNext(EvaluationState.java:274)
at org.jruby.evaluator.EvaluationState.begin(EvaluationState.java:320)
at org.jruby.RubyObject.eval(RubyObject.java:449)
at org.jruby.internal.runtime.methods.DefaultMethod.internalCall(DefaultMethod.java:111)
at org.jruby.internal.runtime.methods.AbstractMethod.call(AbstractMethod.java:58)
at org.jruby.RubyObject.callMethod(RubyObject.java:371)
at org.jruby.RubyObject.callMethod(RubyObject.java:323)
at org.jruby.evaluator.EvaluateVisitor$CallNodeVisitor.execute(EvaluateVisitor.java:574)
at org.jruby.evaluator.EvaluationState.executeNext(EvaluationState.java:274)
at org.jruby.evaluator.EvaluationState.begin(EvaluationState.java:320)
at org.jruby.internal.runtime.methods.EvaluateCallable.internalCall(EvaluateCallable.java:67)
at org.jruby.internal.runtime.methods.AbstractCallable.call(AbstractCallable.java:64)
at org.jruby.runtime.ThreadContext.yieldInternal(ThreadContext.java:496)
at org.jruby.runtime.ThreadContext.yieldCurrentBlock(ThreadContext.java:436)
at org.jruby.runtime.ThreadContext.yield(ThreadContext.java:415)
at org.jruby.runtime.builtin.meta.FileMetaClass.open(FileMetaClass.java:501)
at org.jruby.runtime.builtin.meta.FileMetaClass.open(FileMetaClass.java:477)
at org.jruby.RubyKernel.open(RubyKernel.java:261)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jruby.runtime.callback.ReflectionCallback.execute(ReflectionCallback.java:140)
at org.jruby.internal.runtime.methods.CallbackMethod.internalCall(CallbackMethod.java:79)
at org.jruby.internal.runtime.methods.AbstractMethod.call(AbstractMethod.java:58)
at org.jruby.RubyObject.callMethod(RubyObject.java:371)
at org.jruby.RubyObject.callMethod(RubyObject.java:323)
at org.jruby.evaluator.EvaluateVisitor$FCallNodeVisitor.execute(EvaluateVisitor.java:1077)
at org.jruby.evaluator.EvaluationState.executeNext(EvaluationState.java:274)
at org.jruby.evaluator.EvaluationState.begin(EvaluationState.java:320)
at org.jruby.evaluator.EvaluateVisitor$IterNodeVisitor.execute(EvaluateVisitor.java:1332)
at org.jruby.evaluator.EvaluationState.executeNext(EvaluationState.java:274)
at org.jruby.evaluator.EvaluationState.begin(EvaluationState.java:320)
at org.jruby.RubyObject.eval(RubyObject.java:449)
at org.jruby.internal.runtime.methods.DefaultMethod.internalCall(DefaultMethod.java:111)
at org.jruby.internal.runtime.methods.AbstractMethod.call(AbstractMethod.java:58)
at org.jruby.RubyObject.callMethod(RubyObject.java:371)
at org.jruby.RubyObject.callMethod(RubyObject.java:315)
at org.jruby.RubyObject.callInit(RubyObject.java:460)
at org.jruby.RubyClass.newInstance(RubyClass.java:174)
at sun.reflect.GeneratedMethodAccessor99.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jruby.runtime.callback.ReflectionCallback.execute(ReflectionCallback.java:140)
at org.jruby.internal.runtime.methods.CallbackMethod.internalCall(CallbackMethod.java:79)
at org.jruby.internal.runtime.methods.AbstractMethod.call(AbstractMethod.java:58)
at org.jruby.RubyObject.callMethod(RubyObject.java:371)
at org.jruby.RubyObject.callMethod(RubyObject.java:323)
at org.jruby.evaluator.EvaluateVisitor$CallNodeVisitor.execute(EvaluateVisitor.java:574)
at org.jruby.evaluator.EvaluationState.executeNext(EvaluationState.java:274)
at org.jruby.evaluator.EvaluationState.begin(EvaluationState.java:320)
at org.jruby.evaluator.EvaluateVisitor.setupArgs(EvaluateVisitor.java:2896)
at org.jruby.evaluator.EvaluateVisitor.access$3100(EvaluateVisitor.java:169)
at org.jruby.evaluator.EvaluateVisitor$CallNodeVisitor.execute(EvaluateVisitor.java:566)
at org.jruby.evaluator.EvaluationState.executeNext(EvaluationState.java:274)
at org.jruby.evaluator.EvaluationState.begin(EvaluationState.java:320)
at org.jruby.RubyObject.eval(RubyObject.java:449)
at org.jruby.internal.runtime.methods.DefaultMethod.internalCall(DefaultMethod.java:111)
at org.jruby.internal.runtime.methods.AbstractMethod.call(AbstractMethod.java:58)
at org.jruby.RubyObject.callMethod(RubyObject.java:371)
at org.jruby.RubyObject.callMethod(RubyObject.java:323)
at org.jruby.evaluator.EvaluateVisitor$FCallNodeVisitor.execute(EvaluateVisitor.java:1077)
at org.jruby.evaluator.EvaluationState.executeNext(EvaluationState.java:274)
at org.jruby.evaluator.EvaluationState.begin(EvaluationState.java:320)
at org.jruby.RubyObject.eval(RubyObject.java:449)
at org.jruby.internal.runtime.methods.DefaultMethod.internalCall(DefaultMethod.java:111)
at org.jruby.internal.runtime.methods.AbstractMethod.call(AbstractMethod.java:58)
at org.jruby.RubyObject.callMethod(RubyObject.java:371)
at org.jruby.RubyObject.callMethod(RubyObject.java:323)
at org.jruby.evaluator.EvaluateVisitor$CallNodeVisitor.execute(EvaluateVisitor.java:574)
at org.jruby.evaluator.EvaluationState.executeNext(EvaluationState.java:274)
at org.jruby.evaluator.EvaluationState.begin(EvaluationState.java:320)
at org.jruby.Ruby.eval(Ruby.java:207)
at org.jruby.Main.runInterpreter(Main.java:176)
at org.jruby.Main.runInterpreter(Main.java:145)
at org.jruby.Main.run(Main.java:111)
at org.jruby.Main.main(Main.java:86)

I'll look into it further later, but if you come up with any low-hanging fruit, kindly leave a comment. Many thanks!

zosman said...

I have tried this adapter against DB2 on AIX and z/OS, and I it looks like the quote routine interprets id columns as strings, passing them on SELECT in quotes. For example, a query will specify

id = '1'

The case value code in the db2_jdbc.rb evaluates to String. Where is this coming from?

Charles Oliver Nutter said...

zosman: Sounds like a bug in the logic used to pick appropriate DB types for a given field. You can try to dig it out yourself, or post something to the jruby-extras mailing list on RubyForge to report the bug. We're very interested in getting things working well :)