9 Replies Latest reply on Apr 16, 2013 8:24 AM by rareddy

Excel DS driver

pinkstondevin Apr 15, 2013 9:00 AM

Hello,

I am trying to integrate an Excel Spreadsheet as a datasource using a DSN-less connection. I have Teiid installed on a Centos Server with PostgreSQL and CSV datasources.

In my standalone-teiid.xml file I am receiving the error/warn:

08:49:41,043 WARN [org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 28) JBAS010402: Unable to instantiate driver class "sun.jdbc.odbc.JdbcOdbcDriver": java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver from [Module "sun.jdk:main" from local module loader @7d78077d (roots: /home/funluser/datavirtualization/jboss-as-7.1.1.Final/modules)]
08:49:41,054 INFO [org.jboss.as.naming] (MSC service thread 1-4) JBAS011802: Starting Naming Service
08:49:41,061 ERROR [org.jboss.as.controller.management-operation] (ServerService Thread Pool -- 28) Operation ("add") failed - address: ([
("subsystem" => "datasources"),
("jdbc-driver" => "odbc")
]) - failure description: "JBAS010434: Unable to instantiate driver class \"sun.jdbc.odbc.JdbcOdbcDriver\". See log (WARN) for more details"

I have edited the module.xml file and added correct path, and also my driver. My DS looks like:

<datasource jndi-name="java:/odbcDS" pool-name="odbcDS">
                    <connection-url>jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=../Teiidfiles/data/wdi.xls</connection-url>
                    <driver>odbc</driver>
                    <transaction-isolation>TRANSACTION_NONE</transaction-isolation>
                    <pool>
                        <prefill>false</prefill>
                        <use-strict-min>false</use-strict-min>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </pool>
                </datasource>

Driver:

<driver name="odbc" module="sun.jdk">
<driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class>
</driver>

module.xml

<module xmlns="urn:jboss:module:1.1" name="sun.jdk">
    <resources>
        
        <resource-root path="service-loader-resources"/>
    </resources>
    <dependencies>
        <system export="true">
            <paths>
                <path name="com/sun/script/javascript"/>
                <path name="com/sun/jndi/dns"/>
                <path name="com/sun/jndi/ldap"/>
                <path name="com/sun/jndi/url"/>
                <path name="com/sun/jndi/url/dns"/>
                <path name="com/sun/security/auth"/>
                <path name="com/sun/security/auth/login"/>
                <path name="com/sun/security/auth/module"/>
                <path name="sun/misc"/>
                <path name="sun/io"/>
                <path name="sun/nio"/>
                <path name="sun/nio/ch"/>
                <path name="sun/security"/>
                <path name="sun/security/krb5"/>
                <path name="sun/util"/>
                <path name="sun/util/calendar"/>
                <path name="sun/util/locale"/>
                <path name="sun/security/provider"/>
                <path name="META-INF/services"/>
                <path name="sun/jdbc/odbc"/>
            </paths>
            <exports>
                <include-set>
                    <path name="META-INF/services"/>

                </include-set>
            </exports>
        </system>
    </dependencies>
</module>

Could this be because I am using OpenJDK and not Sun? I have followed the examples I could find online with no luck.

Thanks!

1. Re: Excel DS driver

rareddy Apr 15, 2013 10:38 AM (in response to pinkstondevin)

Devin,

Please see the readme.txt file in the <jboss-as>/docs/teiid/datasources/odbc directory. I believe I only tested with sun jdk.

Ramesh..
1 of 1 people found this helpful
Actions
2. Re: Excel DS driver

pinkstondevin Apr 15, 2013 11:39 AM (in response to rareddy)

Ramesh,

Thanks! I removed OpenJDK and installed the Sun JDK 6u43. I can now start-up Teiid without errors, however i cannot establish a connection to my excel file. I have an excel file "wdi.xls" under my Jboss Root directory : /teiidfiles/data/wdi.xls. For some reason I am still unable to connect, any advice? Thanks!

Standalone-Teiid.xml
<datasource jndi-name="java:/odbc-DS" pool-name="odbcDS" enabled="true">
                    <connection-url>jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=$JBOSS_HOME/teiidfiles/data/wdi.xls</connection-url>
                    <driver>odbc</driver>
                    <transaction-isolation>TRANSACTION_NONE</transaction-isolation>
                    <pool>
                        <prefill>false</prefill>
                        <use-strict-min>false</use-strict-min>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </pool>
                </datasource>
11:31:17,865 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (HttpManagementService-threads - 10) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: Could not create connection
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:277) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:235) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:761) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:343) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.internalTestConnection(AbstractPool.java:627) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.strategy.OnePool.testConnection(OnePool.java:88) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.as.connector.pool.PoolOperations$TestConnectionInPool.invokeCommandOn(PoolOperations.java:121) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.connector.pool.PoolOperations$1.execute(PoolOperations.java:60) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.connector.pool.PoolOperations.execute(PoolOperations.java:74) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.ModelControllerImpl$DefaultPrepareStepHandler.execute(ModelControllerImpl.java:466) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:121) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.ModelControllerImpl$1.execute(ModelControllerImpl.java:309) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.ModelControllerImpl$1.execute(ModelControllerImpl.java:299) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.domain.http.server.DomainApiHandler.processRequest(DomainApiHandler.java:294)
        at org.jboss.as.domain.http.server.DomainApiHandler.doHandle(DomainApiHandler.java:201)
        at org.jboss.as.domain.http.server.DomainApiHandler.handle(DomainApiHandler.java:208)
        at org.jboss.as.domain.http.server.security.SubjectAssociationHandler.handle(SubjectAssociationHandler.java:51)
        at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:78)
        at org.jboss.sun.net.httpserver.AuthFilter.doFilter(AuthFilter.java:69)
        at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:81)
        at org.jboss.sun.net.httpserver.ServerImpl$Exchange$LinkHandler.handle(ServerImpl.java:710)
        at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:78)
        at org.jboss.as.domain.http.server.RealmReadinessFilter.doFilter(RealmReadinessFilter.java:54)
        at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:81)
        at org.jboss.sun.net.httpserver.ServerImpl$Exchange.run(ServerImpl.java:682)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_43]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_43]
        at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_43]
        at org.jboss.threads.JBossThread.run(JBossThread.java:122) [jboss-threads-2.0.0.GA.jar:2.0.0.GA]
Caused by: java.lang.NullPointerException
        at sun.jdbc.odbc.JdbcOdbcDriver.initialize(JdbcOdbcDriver.java:436) [rt.jar:1.6.0_43]
        at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:153) [rt.jar:1.6.0_43]
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:249) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
        ... 39 more
Actions
3. Re: Excel DS driver

rareddy Apr 15, 2013 12:45 PM (in response to pinkstondevin)

Same issue discussed here http://mailman.unixodbc.org/pipermail/unixodbc-support/2009-March/002032.html

This is library loading issue.

Ramesh..
1 of 1 people found this helpful
Actions
4. Re: Excel DS driver

pinkstondevin Apr 15, 2013 1:49 PM (in response to rareddy)

Thank you for that link Ramesh, you've been a tremendous help.

I'm trying to create this source DSN-less and still receiving errors. I feel that I have supplied the necessary information but it is still looking for a DSN. My standalone.xml has not change from above. I have Teiid on a CentOS server, so I can't use the Windows ODBC manager. Will I be forced to use the odbc.ini and add a DSN?

13:23:40,451 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (HttpManagementService-threads - 4) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: Could not create connection
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:277) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:235) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:761) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:343) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.internalTestConnection(AbstractPool.java:627) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.jca.core.connectionmanager.pool.strategy.OnePool.testConnection(OnePool.java:88) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
        at org.jboss.as.connector.pool.PoolOperations$TestConnectionInPool.invokeCommandOn(PoolOperations.java:121) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.connector.pool.PoolOperations$1.execute(PoolOperations.java:60) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.connector.pool.PoolOperations.execute(PoolOperations.java:74) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.ModelControllerImpl$DefaultPrepareStepHandler.execute(ModelControllerImpl.java:466) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:121) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.ModelControllerImpl$1.execute(ModelControllerImpl.java:309) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.controller.ModelControllerImpl$1.execute(ModelControllerImpl.java:299) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
        at org.jboss.as.domain.http.server.DomainApiHandler.processRequest(DomainApiHandler.java:294)
        at org.jboss.as.domain.http.server.DomainApiHandler.doHandle(DomainApiHandler.java:201)
        at org.jboss.as.domain.http.server.DomainApiHandler.handle(DomainApiHandler.java:208)
        at org.jboss.as.domain.http.server.security.SubjectAssociationHandler.handle(SubjectAssociationHandler.java:51)
        at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:78)
        at org.jboss.sun.net.httpserver.AuthFilter.doFilter(AuthFilter.java:69)
        at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:81)
        at org.jboss.sun.net.httpserver.ServerImpl$Exchange$LinkHandler.handle(ServerImpl.java:710)
        at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:78)
        at org.jboss.as.domain.http.server.RealmReadinessFilter.doFilter(RealmReadinessFilter.java:54)
        at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:81)
        at org.jboss.sun.net.httpserver.ServerImpl$Exchange.run(ServerImpl.java:682)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_43]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_43]
        at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_43]
        at org.jboss.threads.JBossThread.run(JBossThread.java:122) [jboss-threads-2.0.0.GA.jar:2.0.0.GA]
Caused by: java.sql.SQLException: [unixODBC][Driver Manager]Data source name not found, and no default driver specified
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957) [rt.jar:1.6.0_43]
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114) [rt.jar:1.6.0_43]
        at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073) [rt.jar:1.6.0_43]
        at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323) [rt.jar:1.6.0_43]
        at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174) [rt.jar:1.6.0_43]
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:249) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
        ... 39 more
Thanks
Actions
5. Re: Excel DS driver

rareddy Apr 15, 2013 2:05 PM (in response to pinkstondevin)

Did you try with odbc.ini already and it works?
Actions
6. Re: Excel DS driver

pinkstondevin Apr 15, 2013 2:06 PM (in response to rareddy)

I have not tried with odbc.ini. I've never used it before that's why I originally went with a DSN-less connection.

I'll try using the odbc.ini
Actions
7. Re: Excel DS driver

rareddy Apr 15, 2013 2:14 PM (in response to pinkstondevin)

See the the ODBC <-> JDBC solution is two pronged.

1) You need to have Excel driver on the system you are working with. i.e on CenOS you need to have Excel ODBC Driver. I do not know if any such thing exists? On Windows this comes pre-built in.
2) Then the ODBC Manager on the either Linux or Windows talk to this driver and to provide the connection.

The DSN-less functionality is not one provided by the ODBC Manager, instead it is functionality of the ODBC driver. I would be surprised if you make it work either way on CenOS.

There is long standing issue on Teiid to connect to Excel without using ODBC-JDBC bridge, https://issues.jboss.org/browse/TEIID-965 but no resolution on it yet.

Ramesh..
Actions
8. Re: Excel DS driver

pinkstondevin Apr 16, 2013 8:03 AM (in response to rareddy)

I tried researching a few JDBC drivers with no luck either. Unfortunately the bridge driver is no longer updated and is going to be removed in JDK 8.

Thank you again Ramesh, I greatly appreciate it.
Actions
9. Re: Excel DS driver

rareddy Apr 16, 2013 8:24 AM (in response to pinkstondevin)

Try these jdbc drivers

https://code.google.com/p/sqlsheet/

or http://sourceforge.net/projects/xlsql/

See if they work. Tell us if they work and your experience. Teiid can either adopt or suggest as alternatives to JDBC-ODBC bridge.

Ramesh..
Actions

Go to original post