As the title says, are you using java.sql.ResultSetMetaData.getColumnName in your code? It’s interesting, I have done for years, and I didn’t know that it was just a bug, waiting to happen.

Until I tried MySQL’s 5.1-branch of their JDBC code, I’d always assumed that getColumnName was the right one for generic SQL code. Turns out it isn’t. Specifically, it isn’t the right one if you’re using aliasing in your code. Say you have SELECT Host AS h FROM Host. Now, until the 5.1 branch MySQL JDBC, you would get “h” if you did getColumnName(1) on this result sets metadata. Not so anymore. Now you get “Host”. So what should you use? getColumnLabel. It’s on the same interface. Until tonight I’d never seen a difference between them. But now there is one – so go through all your JDBC code and make sure you’re using the right one here.

Oh, that’s right. MySQL 5.0.5 seems to have a bug in multibyte aliasing. So if you alias Host to be a Chinese character, for example, you will not get the same value back from getColumnName or getColumnLabel. I assume this is a bug, since the 5.1-branch seems good.

1 Comment | By Ola Bini | In: Uncategorized | tags: jdbc, mysql. | #

It would really be time for JDBC to add support for database agnostic DDL. This is still one of the more gross areas of many database libraries (just look at dialects in Hibernate). Most of it is actually caused by DDL. But at the end of the day, most of the operations supported are actually exactly the same. Am I the only one who thinks it would be nice to have programmatic access to DDL operations?

3 Comments | By Ola Bini | In: Uncategorized | tags: ddl, 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')
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 | By Ola Bini | In: Uncategorized | tags: jdbc, jruby, ruby-dbi. | #

Today I will introduce to you a method from ActiveRecord. The method takes a parameter called type and that value can bu for example :primary_key, :string or :integer. Now, in the first line there is a call to native_database_types. Generally, that call returns a structure that looks somewhat like this:

def native_database_types #:nodoc:
  {
   :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
   :string      => { :name => "varchar", :limit => 255 },
   :text        => { :name => "text" },
   :integer     => { :name => "int", :limit => 11 },
   :float       => { :name => "float" },
   :decimal     => { :name => "decimal" },
   :datetime    => { :name => "datetime" },
   :timestamp   => { :name => "datetime" },
   :time        => { :name => "time" },
   :date        => { :name => "date" },
   :binary      => { :name => "blob" },
   :boolean     => { :name => "tinyint", :limit => 1 }
 }
end

The method itself looks like this.

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  native = native_database_types[type]
 column_type_sql = native.is_a?(Hash) ? native[:name] : native
 if type == :decimal # ignore limit, use precison and scale
    precision ||= native[:precision]
   scale ||= native[:scale]
   if precision
     if scale
       column_type_sql << "(#{precision},#{scale})"
     else
       column_type_sql << "(#{precision})"
     end
   else
     raise ArgumentError, "Error adding decimal column: precision cannot be empty if scale if specified" if scale
   end
   column_type_sql
 else
   limit ||= native[:limit]
   column_type_sql << "(#{limit})" if limit
   column_type_sql
 end
end

There is something very wrong with this implementation. Of course, there could exist many errors here, but what I’m thinking about right now is a violation of the usual way methods should work. And in effect, that problem with this method have caused ActiveRecord-JDBC to implement some very inefficient code to handle this method. And it gets called a lot in ActiveRecord. I’ll get back later today with a pointer to what’s wrong here, and I will also discuss some of what I’ve done in AR-JDBC to handle this situation. I hope for many suggestions here! =)

7 Comments | By Ola Bini | In: Uncategorized | tags: active record, jdbc, ruby. | #

This will be a highly uninflammatory blog post, as contrast to the last one. Specifically, there is a slight problem when running the command

jruby -S rake

in a a JRuby on Rails-application. This problem is caused by some hard coded values in the database Rake definitions for Rails. But don’t despair, there is a simple solution to this. It’s not as simple as it should be (invisible) but it’s easy enough. Provided you have JRUBY_HOME set and your version of AR-JDBC is 0.3.1, execute this command from your Rails application root

cp $JRUBY_HOME/lib/ruby/gems/1.8/gems/ActiveRecord-JDBC-0.3.1/lib/tasks/jdbc_databases.rake lib/tasks

Since the hard coded values are hard to override, the jdbc_databases.rake file just hacks Rake to be able to redefine tasks and then redefines the core tasks. This shouldn’t affect a bi-Ruby installation, since the overriding only happens on JRuby, not on MRI. If someone has a better way to do this, please tell me. =)

3 Comments | By Ola Bini | In: Uncategorized | tags: active record, jdbc, jruby, test. | #

I have been a bit concerned about the performance of our component that connects ActiveRecord with JDBC. Since ActiveRecord demands that every result of a select should be turned into a big array of hashes of strings to strings, I suspected we would be quite inefficient at this, and I wasn’t sure I could put all my faith in JDBC either.

So, as a good developer, I decided to test this, with a very small microbenchmark, to see how bad the situation actually was.

Since I really wanted to check the raw database and unmarshalling performance, I decided to not use ActiveRecord classes, but do executions directly. The inner part of my benchmark execution looks like this:

conn.create_table :test_perf, :force => true do |t|
 t.column :one, :string
 t.column :two, :string
end
  
100.times do
 conn.insert("INSERT INTO test_perf(one, two) VALUES('one','two')")
end
  
1000.times do
 conn.select_all("SELECT * FROM test_perf")
end

conn.drop_table :test_perf

It is executed with a recent MySQL Community Edition 5 server, locally, with matching JDBC drivers. The MRI tests is run with 1.8.6, and both use ActiveRecord 1.15.3. ActiveRecord-JDBC is a prerelease of 0.2.4, available from trunk. My machine is an IBM Thinkpad T43p, running Debian. It’s 32bit and Java 6.

The results were highly interesting. First, let’s see the baseline: the Ruby results:

      user     system      total        real
 7.730000   0.020000   7.750000 (  8.531013)

Frankly, I wasn’t that impressed with these numbers. I thought Ruby database performance was better. Oh well. The interesting part is the JRuby AR-JDBC results:

      user     system      total        real
 6.948000   0.000000   6.948000 (  6.948000)

WOW! We’re actually faster in this quite interesting test. Not what I had expected at all, but very welcome news indeed. Note that there is still much block overhead in the interpreter, so the results are a little bit skewed in MRI’s favour by this, too.

3 Comments | By Ola Bini | In: Uncategorized | tags: active record, jdbc, jruby, performance. | #

Version 0.2.2 of ActiveRecord–JDBC have now been released. It contains numerous smaller bug fixes, but more importantly the support for MimerSQL. The internals have been slightly refactored to allow easier change of database specific instructions further down the road.

The release can be found at http://rubyforge.org/frs/?group_id=2014 or installed through RubyGems.

No Comments | By Ola Bini | In: Uncategorized | tags: active record, jdbc, jruby, jruby-extras, ruby. | #