Java ODBC Data Source ( undefined symbol: SQLAllocEnv )

Question

I have the following Java code. Purpose of this code is to establish a connection to a remote MySQL database ProductionDb ( a data source defined in my /etc/odbc.ini file ).

import java.sql.*;
import java.util.*;
import java.io.*;

public class Test {

    public static void main(String[] args) {

        try {
            Connection conn = null;
            PreparedStatement s = null;
            String driver = "sun.jdbc.odbc.JdbcOdbcDriver";

            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection("jdbc:odbc:ProductionDb");

        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
    }

}

The /etc/odbc.ini file is:

$ cat /etc/odbc.ini
[ProductionDb]
Driver = /usr/lib/odbc/libmyodbc.so
Description = Production Database
Server = [ hidden ] 
Port = 3306
User = [ hidden ] 
Password = [ hidden ] 
Database = ProductionDb

By the way - I am using Java 7 and Ubuntu :

 $java -version
    java version "1.7.0_09"
    Java(TM) SE Runtime Environment (build 1.7.0_09-b05)
    Java HotSpot(TM) 64-Bit Server VM (build 23.5-b02, mixed mode)

 $lsb_release -a
    No LSB modules are available.
    Distributor ID: Ubuntu
    Description:    Ubuntu 11.04
    Release:    11.04
    Codename:   natty

When I try to run my program I get the following error:

$java Test
java: symbol lookup error: /usr/lib/jvm/java-7-oracle/jre/lib/amd64/libJdbcOdbc.so: undefined symbol: SQLAllocEnv

Does anyone know why I get this error ? What is wrong here ?

P.S By the way I did run sudo apt-get install unixodbc-dev , sudo apt-get install libmyodbc and sudo apt-get install libmysql-java :-)

UPDATE:

I have also tried the idea suggested in one of the replies below ( by Benny Hill ) : to use the /etc/odbcinst.ini as well as /etc/odbc.ini. Still doesn't work and I get the same error message.

$ cat /etc/odbc.ini
    [ProductionDb]
    Driver = MySQL Driver 
    Description = Production Database
    Server = [ hidden ] 
    Port = 3306
    User = [ hidden ] 
    Password = [ hidden ] 
    Database = ProductionDb

$ cat /etc/odbcinst.ini
    [MySQL Driver]
    Driver = /usr/lib/odbc/libmyodbc.so

ADDITIONAL NOTE:

I can use this ODBC data source successfully from the R programming language.

> library(odbc)
> con = odbcConnect("ProductionDb") 
> con
RODBC Connection 1
Details:
  case=nochange
  DSN=ProductionDb

Answer 1

Sounds like a missing or mismatched library. Try to debug the ldd processing.

First, check what

$ ldd /usr/lib/jvm/java-7-oracle/jre/lib/amd64/libJdbcOdbc.so

says, do all listed dependencies exist?

Then, try setting LD_DEBUG and and start your Java program again, to see the loader debug.

$ export LD_DEBUG=all
$ java Test

Answer 2

The error is the result of libJdbcOdbc.so looking for the function "SQLAllocEnv" in some other .so and not finding it. The way to debug this is to run the command ldd /usr/lib/jvm/java-7-oracle/jre/lib/amd64/libJdbcOdbc.so. That will show you a list of linked .so objects and where they are located.

Generally speaking they should be in /usr/lib however if you have compiled any software yourself you may find that some of these libs are in /usr/local/lib or some other location. If you have anything that shows up in /usr/local/lib it's possible this is what's causing your problem. To test, rename the library in /usr/local/lib to something else (sudo mv /usr/local/lib/mylib.so /usr/local/lib/mylib.so.SAVE).

Now run your program and see if you still get the same error. If that fixes your problem then great! If not, let us know if you get the same error message or if you get a new one.

I would expect your odbc.ini file to look like this:

[primary]
Description             = primary
Driver                  = iSeries Access ODBC Driver
System                  = XXX.XXX.XXX.XXX
UserID                  = XXXXXXXXXX
Password                = XXXXXXXXXX
Naming                  = 0
DefaultLibraries        = QGPL
Database                = XXXXXXXXXX
ConnectionType          = 0
CommitMode              = 2
ExtendedDynamic         = 0
DefaultPkgLibrary       = QGPL
DefaultPackage          = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression    = 1
LibraryView             = 0
AllowUnsupportedChar    = 0
ForceTranslation        = 0
Trace                   = 0

And your odbcinst.ini file to look like this:

[iSeries Access ODBC Driver]
Description     = iSeries Access for Linux ODBC Driver
Driver          = /usr/lib/libcwbodbc.so
Setup           = /usr/lib/libcwbodbcs.so
NOTE1           = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2           = the following Driver64/Setup64 keywords will provide that support.
Driver64        = /usr/lib/lib64/libcwbodbc.so
Setup64         = /usr/lib/lib64/libcwbodbcs.so
Threading       = 2
DontDLClose     = 1
UsageCount      = 1

My example shows my setup for a remote iSeries but I'm sure you can see what you would need to change for MySQL.

Namely your odbc.ini "Driver = ..." line is wrong. It should be something like "Driver = mysql" and then you need to define [mysql] in your odbcinst.ini file.

Answer 3

To fix this add the following to your startup script or profile:

export LD_PRELOAD=$LD_PRELOAD:/usr/lib/libodbc.so:/usr/lib/libodbcinst.so

Your path may vary a bit, for me the .so files where in /usr/lib64.

"There is a bug due to the fact that the libraries "libodbc.so" and "libodbcinst.so" libJdbcOdbc.so are not loaded from the library that implements the JDBC-ODBC bridge."

According to https://code.google.com/p/queryconnector/source/browse/wiki/HowToInstall.wiki?spec=svn122&r=121