6 Replies Latest reply on Mar 27, 2012 11:31 PM by mikecanniff

    SQL Server - sqljdbc4 jar location

    mikecanniff

      Hi - New TEIID user here - I'm working through the Getting Started Parts example in 7.6 and I 'm having trouble accessing my data source SQL Server 2008 through the TEIID designer. Here's my progress so far

       

      - I've installed the sqljdbc and sqljdbc4 jar files in the lib folder

      - I've been able to import the XML mode files

      - I've genereted the DDL and created the database and tables in SQL Server

      - I've imported the data files

      - In the data base perspective I am able to connect to the database and perform SQL queries

      - My problem is that in the TEIID designer perspective I can't preview the data. I receive the following error in the log file

       

      Remote org.teiid.core.TeiidProcessingException: PartsSourceA: Unable to get managed connection for PREVIEW_6ef63fa4-46b9-4c98-b3a4-cfb919f62f54_PartsProject_PartsSourceA; - nested throwable: (javax.resource.ResourceException: Unable to get managed connection for PREVIEW_6ef63fa4-46b9-4c98-b3a4-cfb919f62f54_PartsProject_PartsSourceA.

       

      aused by: org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.)

      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:225)

      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:195)

      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:633)

      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:267)

      at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:622)

      at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)

      ... 20 more

      Caused by: java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

      at com.microsoft.sqlserver.jdbc.SQLServerConnection.<init>(SQLServerConnection.java:238)

       

       

      I ran into this error when I first created the driver definition : Windows --> Preferences --> Data Management --> Connectivity --> Driver Definitions. So I changed the JAR list for that definition to point to the sqljdbc4 file.

       

      So where do I specify that TEIID connections use a different jar file?

       

      Mike Canniff

        • 1. Re: SQL Server - sqljdbc4 jar location
          rareddy

          Mike,

           

          The problem may be

           

          Java Runtime Environment (JRE) version 1.6 is not supported by this driver

           

          in the log above. In Eclipse, when you are creating the connection profile for data source, you define the JDBC jar. Also, for Teiid, you need to place the JAR in the "lib" directory of the JBoss AS. We have good success with JTDS driver for MS SQLServer, if you want you can give it a try.

           

          http://jtds.sourceforge.net/

           

          Ramesh..

          • 2. Re: SQL Server - sqljdbc4 jar location
            mikecanniff

            Thanks Ramesh - It turns out that I have both JDBC jar files from microsoft located in the lib directory. This is the sqljdbc.jar and the sqljdbc4.jar. When I first set up the connection profile, I told TEIID to use the sqljdbc4.jar file. I had received a similar error to what is listed above when I provided the other jar file. So once I switched to sqljdbc4, I thought everything would be OK. ButI'm wondering if the data preview feature uses a different path or is hardwired to sqljdbc.jar? I will try to install the alternate version listed above.

            • 3. Re: SQL Server - sqljdbc4 jar location
              rareddy

              Preview uses the same classloading of the Drivers as DTP. Actually, underneath it uses the DTP.

               

              Ramesh..

              • 4. Re: SQL Server - sqljdbc4 jar location
                mikecanniff

                Is there a place in the Admin console to set the connection jar? I've only done the setup via the Designer. Also I installed the JTDS JAR in the same directory as the sql ones. I updated the Designer to use this, but I still have the same error. I'm assuming that the jars do not go into the default deployment directory.

                 

                Thanks for responding Ramesh!

                • 5. Re: SQL Server - sqljdbc4 jar location
                  rareddy

                  Mike,

                   

                  The I would advise removing all the JARs to begin with, try to get to a clean slate. Now, there are two places any of the Jar files go (pick between Microsoft or JTDS)

                   

                  1) if you are using "default" profile, place the jar in "jboss-as/server/default/lib" directory. Then restart the server.

                   

                  2) When you are importing the metadata from source in Designer, create a new connection, then configure the connection profile and point the JAR file to your disk location. Note underneath this uses DTP profile view.

                   

                  If you still get error, you still have not cleaned up jars correctly, so rinse and repeat.

                   

                  Ramesh..

                  • 6. Re: SQL Server - sqljdbc4 jar location
                    mikecanniff

                    Thanks Ramesh - I was actually able to use the Microsoft sqljdbc4.jar file, but placed it in the location you specified. I also removed the other jar files and this worked. I can now move on with other examples!