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

    Excel DS driver

    devin pinkston Newbie

      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>

              <!-- currently jboss modules has not way of importing services from

              classes.jar so we duplicate them here -->

              <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
          Ramesh Reddy Master

          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
          • 2. Re: Excel DS driver
            devin pinkston Newbie

            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

            • 3. Re: Excel DS driver
              Ramesh Reddy Master

              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
              • 4. Re: Excel DS driver
                devin pinkston Newbie

                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

                • 5. Re: Excel DS driver
                  Ramesh Reddy Master

                  Did you try with odbc.ini already and it works?

                  • 6. Re: Excel DS driver
                    devin pinkston Newbie

                    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

                    • 7. Re: Excel DS driver
                      Ramesh Reddy Master

                      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..

                      • 8. Re: Excel DS driver
                        devin pinkston Newbie

                        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.

                        • 9. Re: Excel DS driver
                          Ramesh Reddy Master

                          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..