Subscribe to Developer Soapbox

Stay up to date! Get all the latest & greatest posts delivered straight to your inbox

jruby

Connect to MS Access Database using JRuby

  • Developer

    Our resident opinionated developer.

    More posts by Developer.

Developer

• 4 min read

The example below will show you how to query a Microsoft Access database using JRuby and JDBC.  I have to say that this was probably the most fun POC I’ve done in a few months.

And let me address this question too: Access backend???  Why??  Because its my blog and my free time, thats why.  And it obviously got your attention since you are here :) .

The other great thing here is that this should work with ANY JDBC driver which you can’t get to work with JRuby.

What You’ll Need

  1. JRuby
  2. Java JDK
  3. Maven
  4. UCanAccess library (dependency included in pom.xml example below)

What You’ll Get

A working JDBC connection from JRuby to MS Access.  The possibilities are endless after there.  Dare I say JRuby on Rails with Access?

For the Purists

The final solution may not suit the Ruby purists, but its the only one that worked.  After several hours spent trying to get JRuby to recognize my UCanAccess driver, I was at the brink of giving up.  I decided to give DuckDuckGo I shot and look for other JRuby inthusiasts with similar headaches.  I found this article, which gave me tremendous insight - https://stackoverflow.com/questions/6128643/jruby-jdbc-netezza

At a high level, JDBC should work as follows in JRuby:

require "java"
require 'your_jdbc_driver_dependencies.jar'

module JrubyJdbc
    #Load driver
    Java::NetUcanaccessJdbc.UcanaccessDriver
    #Try this too...
    p clazz = Java::JavaClass.for_name("net.ucanaccess.jdbc.UcanaccessDriver")    
    #See if its loaded, which it does print:
    java.sql.DriverManager.getDrivers.each{ |e| puts e }
    
    #Get Connection
    connection = driver.getConnection("jdbc:ucanaccess://your_database_file.accdb")
    stmt = connection.create_statement
      stmtSelect = connSelect.create_statement, etc, etc...

end

However, even though I could see my driver class loaded using the “puts e” console output, I would keep getting greeted with:

Unhandled Java exception: java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://Database1.accdb
java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://Database1.accdb

Abstraction to the Rescue

One of my best teachers during my Computer Science college program once said “99% of computer science problems can be solved by simply adding another layer of abstraction”.  The more I experience in the field the more I see how true that is.  And its certainly what we’ll apply here.

The solution we’ll use is creating a Java class which will give us the ready JDBC connection for our driver of choice.  After that is done, we should be able to use the connection in JRuby for everything else.

The POM

First, create a simple Java maven project.  Include the following in your pom.xml:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>dependency</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>dependency</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>net.sf.ucanaccess</groupId>
      <artifactId>ucanaccess</artifactId>
      <version>4.0.4</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-assembly-plugin</artifactId>
        <version>3.0.0</version>
        <configuration>
          <descriptorRefs>
            <descriptorRef>jar-with-dependencies</descriptorRef>
          </descriptorRefs>
          <archive>
            <manifest>
            </manifest>
          </archive>
        </configuration>
        <executions>
          <execution>
            <phase>package</phase>
            <goals>
              <goal>single</goal>
            </goals>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>

</project>

The Class

Your single Java class will look like this:

package com.developersoapbox;

import net.ucanaccess.jdbc.JackcessOpenerInterface;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Driver {
    public Connection getConnection(String url) throws ClassNotFoundException, SQLException {
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        return DriverManager.getConnection(url);
    }
}

Build the project with maven (mvn clean package), and you should get a file named .jar-with-dependencies.  Thats the fat jar which includes all the jars needed for the project, including the Microsoft Access JDBC driver (UcanAccess), and your new helper class.

Bring it all Together

Once you’ve created your jar, you may use it in the JRuby script and simply create an instance of the Driver class and call getConnection, passing your ucanaccess database url.  Below is an example which inserts a record into a table named “People” in Access which has a single column named “Name” for a person’s name.

require 'java'
require 'C:/path_to_your_dependency.jar'

module JRubyMSAccess
    driver = Java::OrgExample.Driver.new
    connection = driver.getConnection("jdbc:ucanaccess://C:/path_to_your_database.accdb")
    
    stmt = connection.create_statement
  #Example insert record:
    stmt.execute_update("INSERT INTO People(Name) VALUES('Jane')")

    rs = stmt.execute_query("SELECT * FROM People")
    
    while(rs.next) do
        puts rs.getString("Name")
    end

    connection.close
end

Sure we cheated a little bit, but that was only for a single line in the script.  The right tool for the job is more like it in my opinion.

Developer Soapbox © 2021