How to create datasource connection for ODBC (MS Access) in teiid server

[govindarajs]

Hi,

     I have data in MS Access database and I have configured the MS Access database in the ODBC, so now I need to create datasource connection for odbc (MS Access) in teiid server, which driver I need to use and how to deploy the ODBC driver in the teiid server?, currently I am using Teiid server 8.7

Thanks & Regards

Govindaraj Sanjeevi

[rareddy]

You can ODBC JDBC bridge in JDK 1.7 for it. There are example data sources in the docs directory. Unfortunately in JDK 1.8 Oracle removed ODBC JDBC bridge. We have provided support for Excel but not for Access in later releases that do not use this ODBC JDBC bridge, but one can possibly write one.

Ramesh..

[m.ardito]

Could ucanaccess translator (in later teiid versions) help in this respect?

Microsoft Access Translators - Teiid 8.12 - Project Documentation Editor

Marco

[shawkins]

> Could ucanaccess translator (in later teiid versions) help in this respect?

Yes that would be a viable option in Teiid 8.11 and later.

[m.ardito]

Since this can turn out useful, soon or later, I'd like to try to deploy the jdbc driver in the web console of my 8.12 server,

I found ucanaccess-3.0.3.1.jar on UCanAccess-A Pure Java JDBC Driver for Access

I think it depends on all dependencies provided in the zip file under a lib subfolder

commons-lang-2.6.jar

jackcess-2.1.3.jar

hsqldb.jar

commons-logging-1.1.1.jar

Until now, I deployed mysql and jtds jdbc .jar drivers, but somehow I think this is different (read: I tried to deploy all jars through webconsole, dependencies first: dependencies deployed fine, ucanaccess .jar did not)

I admit my experience in this is quite limited, can anyone shed some light, or point somewhere to understand what's needed, what to do?

This stackoverflow post seems to be related, http://stackoverflow.com/questions/29615600/configuring-ucanaccess-driver-on-wildfly-8-0

Thanks.

[rareddy]

You can create uber jar of all those jars and make sure the driver jdbc4 compatible then you can use web-console to deploy and use like other jdbc drivers. See this for more information Data Source Configuration in AS 7

Otherwise, follow the stackoverflow, I added my comment how to fix there.

[m.ardito]

Thanks. following that link, and with the help of a colleague, I did it, it works!

Here is what I did, so that others can benefit from my experience... (I can turn this into a document if needed)

I dowloaded UCanAccess-3.0.3.1-bin.zip and extracted all 5 .jar files cited above in a temp folder.

Then, I created a base "uberjar" folder, moved there, and:

- from commons-lang-2.6.jar, I copied the whole org\apache\commons\lang folder structure

- from commons-logging-1.1.1.jar, I copied the whole org\apache\commons\logging folder structure

- from jackcess-2.1.3.jar, I copied the whole com\healthmarketscience\jackcess folder structure

- from hsqldb.jar, I copied the whole org\hsqldb folder structure

and finally,

- from ucanaccess-3.0.3.1.jar, I copied the whole net\ucanacces folder structure

then created a META-INF\services\java.sql.Driver file (with folder structure) always in the "uberjar" folder

in the \java.sql.Driver file I wrote two lines:

org.hsqldb.jdbc.JDBCDriver

net.ucanaccess.jdbc.UcanaccessDriver

maybe just the last one was necessary? It worked anyway.

then, zipped all the contents of the "uberjar" folder into uberjar.zip, reanmed in uberjar.jar

Finally, uploaded this .jar to the teiid 8.12 web console deployment utility, and all went fine!

It was then the time to create a datasource, which I did from the web console as usual,

- named test_mdb

- jndi name: java:/test_mdb

- driver (now found in the list): uAccess.jarnet.ucanaccess.jdbc.UcanaccessDriver_0_0

- connection url: jdbc:ucanaccess:///opt/msaccess/test.mdb

- user/password, left empty since my test mdb was not protected in any way.

(I did also a similar datasource that connects to a test.accdb)

To use it I created a simple multisource dynamic VDB

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="msaccesstest" version="1">

    <description>msaccesstest</description>

    <property name="UseConnectorMetadata" value="true" />

     <model name="testmdb">

        <property name="importer.useFullSchemaName" value="true"/>

        <source name="msaccesstest-connector" translator-name="ucanaccess" connection-jndi-name="java:/test_mdb"/>

    </model>

     <model name="testaccdb">

        <property name="importer.useFullSchemaName" value="true"/>

        <source name="msaccesstest2-connector" translator-name="ucanaccess" connection-jndi-name="java:/test_accdb"/>

    </model>

</vdb>

Squirrel could instantly connect to the msaccesstest VDB in the usual way, and I found both models available.

And it worked flawlessly at first try, nice! Thanks!

Marco

[shawkins]

Thanks for the write up Marco, we'll see if we can get this captured in the docs as well.