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.
