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')
end
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.
9 Comments, Comment or Ping
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.
October 3rd, 2007
Try quoting the final argument…
jruby setup.rb config “–without=dbd_sqlite,dbd_sybase”
It works for me when I do this.
Stu
October 4th, 2007
Thanks. Works like magic. Thanks again.
October 8th, 2007
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.
December 20th, 2007
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?
rs.getBoolean(columnNumber)
else
rs.getObject(columnNumber)
end
when java.sql.Types::NUMERIC, java.sql.Types::DECIMAL
unless rs.getObject(columnNumber).nil?
metaData.getScale(columnNumber) == 0 ? rs.getLong(columnNumber) : rs.getDouble(columnNumber)
else
rs.getObject(columnNumber)
end
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))
else
rs.getObject(columnNumber)
end
end
December 21st, 2007
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!
December 21st, 2007
Thanks, this has been very useful.
Unfortunately, this doesn’t work with the new version of DBI (0.4.0) straight out of the box because of some API changes. I’ve been playing around a little with it, and I haven’t been successful. Maybe someone can get it working?
September 15th, 2008
Ola released this code as a gem. Gem install with :
jruby -S gem install dbd-jdbc
And, yes, it is now updated to work with DBI 0.4.0
October 21st, 2008
I know this is extremely late, but it makes me so happy to see this kind of progress with this project, it’s nice to feel that the project is finally starting to see some visibility.
-Erik
December 31st, 2008
Reply to “Ruby-DBI and JDBC”