Oxygen Will Not Connect to my Data Sources

[matchew]

I need to connect to an MS Access data source. I have had this working on a windows computer last year, however, I am having trouble connecting this on Ubuntu. It continually returns java.sql.SQLException: null




I feel like it is a simple solution, but I am stumped and have spent too much of my work day on this. Has anyone encountered this before, or has any advice to offer?


What I have Done:

In <oXygen/>
1. Configured JDBC-ODBC Bridge

2.Configured Connection


In Ubuntu 11.04
1. Added a link to a file-system client based on ssh

Code: Select all

sshfs -p22 matt@192.168.1.196:/productionFolder /media/productionServer

2. Added Microsoft Access Driver to odbcinst.ini

Code: Select all

$ cat /etc/odbcinst.ini 

[Microsoft Access Driver]

Description =   MDB Tools ODBC drivers

Driver = /usr/lib/libmdbodbc.so.0

Setup = /usr/lib/libmdbodbc.so.0

FileUseage = 1

3. Added ODBC connection to odbc.ini[/list]

Code: Select all

$ cat /etc/odbc.ini 

[production]

Driver  =   Microsoft Access Driver

Description =   Production ODBC Connector

Trace   =   Yes

TraceFile   =   /tmp/odbc.log

Database    =   /media/ProductionServer/Production/production--00.mdb

4. Tested the connection

Code: Select all

$ isql -v production 

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL> quit

And Yet, in oXygen It still returns with java.sql.SQLException: null

[adrian]

Hello,

Oxygen provides the Sun JDBC-ODBC bridge only for Windows platforms. It is not available for Linux or Mac. I'm guessing you have imported your previous Oxygen options from Windows and now found the JDBC-ODBC Bridge(sun.jdbc.odbc.JdbcOdbcDriver) data source driver available in Linux.

Anyway, the JDBC-ODBC bridge should only be used as a last resort, it's not particularly fast or reliable.

The best way to resolve this is to use an SQLite JDBC connector:
http://www.zentus.com/sqlitejdbc/ (sqlitejdbc-v056.jar)
http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC (sqlite-jdbc-3.7.2.jar)

To use any of these drivers you have to create a new generic JDBC Data Source in Oxygen and add the corresponding jar(Add Files), then the driver class should be detected automatically: org.sqlite.JDBC

Afterwards you can create a new connection, choose the Data Source configured above and provide the JDBC URL(e.g. jdbc:sqlite:sample.db), user and password.

Regards,
Adrian

[matchew]

That sounds promising. But, it hasn't solved the problem. I have a new direction to look and I appreciate it, but perhaps you can address these two new questions.

The first is very minor, but I found the JDBC-ODBC bridge in the linux distribution of oXygen in my case ~/Oxygen\ XML\ Editor12/jre/lib/rt.jar. Why would oXygen distribute a windows only driver in linux? Can you point in a direction that specifies this distinction?

I think sqlite is promising, but will it connect to my odbc network source I have configured in /etc/odbc.ini? Because currently it does not. It returns a connection that says (default) but will do nothing else when I specify jdbc:sqlite:production. However, when I specify a path to the .mdb file, jdbc:sqlite:/media/ProductionServer/Production/production--00.mdb it returns: java.sql.SQLException: [SQLITE_NOTADB] File opened that is not a database file (file is encrypted or is not a database). Which makes me wonder if I need to convert to a .db or sqlite database. However, others in this building insist on entering their data in Access. I'd prefer to avoid garbing and converting a static version of the database every time I need to run a transformation.

[adrian]

Hello,

Oxygen bundles a Sun/Oracle Java SE Runtime Environment(JRE) in its installation directory: oxygen/jre
The JDBC-ODBC bridge can be found in any Java SE distribution(even for Linux) in the runtime jar file(rt.jar). Note however that Oxygen does not support this bridge in Linux as it is considered nonfunctional.


I've discovered a small discrepancy in the directory paths from your setup:
sshfs -p22 matt@192.168.1.196:/productionFolder /media/productionServer

odbc.ini:
Database = /media/ProductionServer/Production/production--00.mdb

Linux has case sensitive file paths so you might want to double check all file paths that you are using and/or have configured.

Regards,
Adrian

[matchew]

Thanks again adrian. That was a typo on my part, it was consistent in my config. I did not see a place to edit the post.

Unfortunately I did not see a way to get this connected without converting the .mdb (access file) to a sqlite db. I do have a VM machiene that runs windows so I installed oXygen there for the transformation that requires data from the Access table, but ultimately it was inconvenient. I would still like to see this problem resolved. Though, it will be taking a backseat for now.