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) {
      if (i == 1024) {
         throw new IOException("All handles are currently in use. Try to free some handles by disconnecting from ODBC.");
      handle = i;

   public void disconnect() {

   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 {
       OdbcClient c = new OdbcClient();
       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" : ", "));

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.

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:

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

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:

CFLAGS=-shared -fPIC -w -I$(JAVA_HOME)/include -I$(JAVA_HOME)/include/linux

all: $(PROJECT).so

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

        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.

. .