söndag, september 23, 2007

Ruby-DBI and JDBC

Last week I decided it was time to get Ruby-DBI working with JDBC. I resolved to get on it since it would be highly useful and probably not so hard either. But as it turned out I didn't really need to do anything. The work has already been done by Kristopher Schmidt. Very nice. So instead, this post will detail how to get it working with JRuby.

First download the Ruby-DBI distribution from RubyForge. Secondly, unpack. The third step is to configure and install it. Execute these commands inside the Ruby-DBI directory:
jruby setup.rb config --without=dbd_sqlite,dbd_sybase
jruby setup.rb setup
jruby setup.rb install
Now you should have Ruby-DBI installed, but no DBD drivers. Verify that it's actually installed by running "jirb -rdbi". The next step is to install the JDBC driver. First create a directory called $JRUBY_HOME/lib/ruby/site_ruby/1.8/DBD/Jdbc. Download Jdbc.rb and JdbcTypeConversions.rb and put them into this directory. Also make sure that the JDBC driver you want to use is on your CLASSPATH. Now you can create a script like this:
require 'dbi'

DBI.connect('DBI:Jdbc:mysql://localhost/test', 'test', 'test',
'driver'=>'com.mysql.jdbc.Driver') do |dbh|
p dbh.select_all('SELECT * FROM a_table')
Make sure that you include the name of the driver as done in this code. The first parameter should be the regular JDBC URI with DBI: first. In this case it's the test database on localhost I connecting to, using the test username and test password. More information about how Ruby-DBI can be used can be found by Google.

8 kommentarer:

Admin sa...

Have anybody tried this on Windows platform.

I have instaled jRuby 1.0.1 and DBI 0.1.1 and wasn't able to install DBI.

I got an error stating that: "config: unknown option --without ..."

the very same command have executed perfectly in Ruby. If I omit the without option, than setup command fails.

I would like to use jRuby-DBI-jdbc very much. Specially for accesing legacy data.

Stuart Holden sa...

Try quoting the final argument...

jruby setup.rb config "--without=dbd_sqlite,dbd_sybase"

It works for me when I do this.


Admin sa...

Thanks. Works like magic. Thanks again.

Adam Boyle sa...

First I must say that this tutorial saved me a lot of grief in exporting data from a Cache' database... my sincerest thanks for that!

Unfortunately there seems to be a slight problem in the JDBC.rb file in the way that nulls are handled for numeric columns... they are returned as zero. This is due to the fact that Java primitive types cannot be null.

In the get_value(columnNumber, rs, metaData) function for the Statement class, the value of numeric fields is returned via ResultSet.getLong()... which returns a Java primitive long, and as such is afflicted by the null -> 0 problem. Is there an easy way to fix this code so that null values in a numeric column are returned as null and not zero? Maybe by applying a wrapper?

Any help you can provide is most appreciated.

Briefcase S sa...

It turns out I was able to solve my own problem with a few simple modifications to JDBC.rb.

The new definition for get_value() in the Statement class is as follows (Ola: you should consider allowing <code> tags...:):

def get_value(columnNumber, rs, metaData)
return case metaData.getColumnType(columnNumber)
when java.sql.Types::BIT
unless rs.getObject(columnNumber).nil?

when java.sql.Types::NUMERIC, java.sql.Types::DECIMAL
unless rs.getObject(columnNumber).nil?
metaData.getScale(columnNumber) == 0 ? rs.getLong(columnNumber) : rs.getDouble(columnNumber)

when java.sql.Types::DATE
jdbcdate_to_dbidate( rs.getDate(columnNumber))
when java.sql.Types::TIME
jdbctime_to_dbitime( rs.getTime(columnNumber))
when java.sql.Types::TIMESTAMP
jdbctimestamp_to_dbitimestamp( rs.getTimestamp(columnNumber))

Briefcase S sa...

Also, there is a small defect in JdbcTypeConversions.rb that has been pointed out back at RubyForge...

The following code:

def jdbc_to_dbi_sqltype(jdbctype)
return case jdbctype
when Types::BIGINT then DBI::BIGINT

should be:

def jdbc_to_dbi_sqltype(jdbctype)
return case jdbctype
when Types::BIGINT then DBI::SQL_BIGINT

Just thought you'd like to know!

chadj sa...

Ola released this code as a gem. Gem install with :

jruby -S gem install dbd-jdbc

Brad sa...

This doesn't appear to work properly with DBI 0.4.1; I think parameters are being inserted incorrectly into parameterized statements. It does, however, work with DBI 0.2.2.