2011/05/17

Accessing ODBC from Java

Connecting to an ODBC database from Java can be a real nightmare. Most solutions suggest using a JDBC-ODBC bridge. So what are real possibilities here? Native JDBC-ODBC bridge by Sun/Oracle sun.jdbc.odbc.JdbcOdbcDriver. Well, there are some limitations like the number of concurrent connections and poor performance. I was not able to find any free (ideally open source) JDBC-ODBC bridge. There are multiple vendors but only one seems to have a de facto standard - Easysoft. They provide trial version of their bridge so you can try the solution before you buy it. Here we realized some troubles in Java Virtual Machine consuming too much memory. I do not want to blame the bridge. It was on a separate machine but we are not able to debug it and see how it works. So not being opensource killed Easysoft's chance to get a new customer.

After all I decided to write a small Java client that would be able to access an ODBC database. We needed just some basic querying capabilities anyway. But this is a little tricky, there is no ODBC communication protocol specification. Eventually, I decided to use unixODBC library using JNI. And here is beginning of the story.

First I designed a client for accessing the database. I had some previous ODBC API knowledge so I designed the client in order to write minimum code in C.

A design note: I failed passing database handles (type void*) between C and Java, so I created an array to store real handles in the C library and pass just an index to the array. This requires tha Java part to remember which handles have been used. There definitely exist a more sophisticated solution but I wanted to query an ODBC database at first. Also note that all data are returned as Strings for simplicity. There is also a shor main() method to test the client.

package org.marvec.odbc;

public class OdbcClient {
   
   private static SortedSet usedHandles = new TreeSet();
   private int handle;
   
   public native void connect(String connection) throws IOException;
   public native void execute(String statement) throws IOException;
   public native int getNumCols();
   public native ColumnMetadata getColMetadata(int col);
   public native boolean fetch();
   public native String getColData(int col) throws IOException;
   public native void freeStatement();
   private native void close();
      
   public OdbcClient() throws IOException {
      int i = 0;
      while (usedHandles.contains(i) && i <= 1024) {
         i++;
      }
      if (i == 1024) {
         throw new IOException("All handles are currently in use. Try to free some handles by disconnecting from ODBC.");
      }
      usedHandles.add(i);
      handle = i;
   }

   public void disconnect() {
      close();
      usedHandles.remove(handle);
   }

   public static class ColumnMetadata {
       public String name;
       public int type;
       public long length;
       public int digits;
       this.nullable = nullable;

       public String toString() {
          return name + ": " + type + "(" + length + ")" + "[" + digits + "]" + (nullable ? "*" : "");
       }
   }

   public static void main(String... args) throws Exception {
       System.loadLibrary(args[0]);
       OdbcClient c = new OdbcClient();
       c.connect("DRIVER={PostgreSQL64};DATABASE=myodbcdb;SERVER=localhost;PORT=35432;Uid=admin;Pwd=admin;");
       c.execute("select * from persons where person_id < 1000");
       System.out.println("Columns: " + c.getNumCols());
       for (int i = 1, j = c.getNumCols(); i <= j; i++) {
           ColumnMetadata meta = c.getColMetadata(i);
           System.out.println("Column " + i + ": " + meta);
       }
       while (c.fetch()) {
           for (int i = 1, j = c.getNumCols(); i <= j; i++) {
               System.out.print(c.getColData(i) + (i == j ? "\n" : ", "));
           }
       }
       c.freeStatement();
       c.disconnect();
   }
}

After compilation, I neede javah utility to obtain a C header file (.h).

javah -jni org.marvec.odbc.OdbcClient

Now the hard C part begins. I'm not a C guru and my compiler reports many warnings about my code. However, it compiles and works. First I copied org_marvec_odbc_OdbcClient.h to org_marvec_odbc_OdbcClient.c and added the shared handle storage to the header file as well as necessary includes. As you can see, we need to remember three different handles for each connection.

#include 
#include 
...
SQLHENV env[1024];
SQLHDBC dbc[1024];
SQLHSTMT stmt[1024];

You might find me wasting some memory but you know, in Java world, there is always enough memory... I will show you only some parts of the solution, link to a complete package is at the end of this post. The most difficult function is getting column metadata that requires creating a new Java object from C.

JNIEXPORT jobject JNICALL Java_org_marvec_odbc_OdbcClient_getColMetadata(JNIEnv *jnienv, jobject obj, jint col) {
    int h = getStaticHandle(jnienv, obj);
    SQLSMALLINT nameLength, dataType, decimalDigits, nullable;
    SQLULEN colSize;
    jstring jstr;
    char name[512];
    
    SQLDescribeCol(stmt[h], (SQLUSMALLINT) col, name, sizeof(name), &nameLength, &dataType, &colSize, &decimalDigits, &nullable);

    jstr = (*jnienv)->NewStringUTF(jnienv, name);

    jclass clazz;
    jmethodID cid;
    jobject meta;

    clazz = (*jnienv)->FindClass(jnienv, "org/marvec/odbc/OdbcClient$ColumnMetadata");
    cid = (*jnienv)->GetMethodID(jnienv, clazz, "", "(Ljava/lang/String;IJIZ)V");
    meta = (*jnienv)->NewObject(jnienv, clazz, cid, jstr, dataType, colSize, decimalDigits, (jboolean) (nullable == SQL_NULLABLE));

    return meta;
}

In some problematic methods I also check for errors using another utility library I mostly copied from Jinput project (see function throwIOException() for example).

JNIEXPORT void JNICALL Java_org_marvec_odbc_OdbcClient_connect(JNIEnv *jnienv, jobject obj, jstring conn) {
    SQLRETURN ret;
    jbyte *str;
    int h = getStaticHandle(jnienv, obj);
    char error[10240];

    jclass cls = (*jnienv)->GetObjectClass(jnienv, obj);

    str = (*jnienv)->GetStringUTFChars(jnienv, conn, NULL);

    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &(env[h]));
    SQLSetEnvAttr(env[h], SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    SQLAllocHandle(SQL_HANDLE_DBC, env[h], &(dbc[h]));
    ret = SQLDriverConnect(dbc[h], NULL, str, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

    if (!SQL_SUCCEEDED(ret)) {
        extractError(dbc[h], SQL_HANDLE_DBC, error, sizeof(error));
        throwIOException(jnienv, "SQLDriverConnect failed with return value %d:\n%s", ret, error);
    }

    (*jnienv)->ReleaseStringUTFChars(jnienv, conn, str);
}

Now there are some dependencies in the C project. You must have installed the following libraries in your system:

  • unixODBC
  • unixODBC-dev
  • some ODBC driver for your target database like odbc-postgresql
  • libltdl
  • and some common C libraries that are likely to be already present on your computer: pthread, dl

Now you must configure ODBC to provide you the driver (see the first part of the connection string DRIVER=). This is done in /etc/odbcinst.ini and we can configure both 32- and 64-bit versions:

[PostgreSQL]
Description     = PostgreSQL driver for Linux & Win32
Driver          = /usr/lib/odbc/psqlodbca.so
Setup           = /usr/lib/odbc/libodbcpsqlS.so

[PostgreSQL64]
Description     = PostgreSQL driver for Linux & Win32
Driver          = /usr/lib64/odbc/psqlodbca.so
Setup           = /usr/lib64/odbc/libodbcpsqlS.so

To compile the C library I created a small Makefile:

CC=gcc
CFLAGS=-shared -fPIC -w -I$(JAVA_HOME)/include -I$(JAVA_HOME)/include/linux
PROJECT=org_marvec_odbc_OdbcClient

all: $(PROJECT).so

$(PROJECT).so: $(PROJECT).c
        $(CC) $(CFLAGS) util.c $(PROJECT).c -o $(PROJECT).so -lodbc -lpthread -lltdl -ldl
        ln -sf $(PROJECT).so libjavaODBC.so

clean:
        rm $(PROJECT).so libjavaODBC.so

Note the creation of a symbolic link. Java loads libraries on library path with standard name lib[name fo the library].so and only the library name is what you pass as an argument to System.loadLibrary().

Now to run the client just call:

java -Djava.library.path=. org.marvec.odbc.OdbcClient javaODBC

Feel free to download the complete source code at github. The code is provided as is, I do not take any responsibility for any damage it might cause to anybody or anything (including hardware, software, people, animals, etc). For instructions see JBoss Community document.

. .