-
1. Re: How to create datasource connection for ODBC (MS Access) in teiid server
rareddy Jan 27, 2016 11:55 AM (in response to govindarajs)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..
-
2. Re: How to create datasource connection for ODBC (MS Access) in teiid server
m.ardito Jan 28, 2016 12:23 PM (in response to rareddy)1 of 1 people found this helpfulCould ucanaccess translator (in later teiid versions) help in this respect?
Microsoft Access Translators - Teiid 8.12 - Project Documentation Editor
Marco
-
3. Re: How to create datasource connection for ODBC (MS Access) in teiid server
shawkins Jan 28, 2016 1:56 PM (in response to m.ardito)1 of 1 people found this helpful> Could ucanaccess translator (in later teiid versions) help in this respect?
Yes that would be a viable option in Teiid 8.11 and later.
-
4. Re: How to create datasource connection for ODBC (MS Access) in teiid server
m.ardito Feb 3, 2016 12:58 PM (in response to shawkins)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.
-
5. Re: How to create datasource connection for ODBC (MS Access) in teiid server
rareddy Feb 3, 2016 1:27 PM (in response to m.ardito)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.
-
6. Re: How to create datasource connection for ODBC (MS Access) in teiid server
m.ardito Feb 4, 2016 11:08 AM (in response to rareddy)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
-
7. Re: How to create datasource connection for ODBC (MS Access) in teiid server
shawkins Feb 4, 2016 2:39 PM (in response to m.ardito)Thanks for the write up Marco, we'll see if we can get this captured in the docs as well.