11 Replies Latest reply on Oct 31, 2003 4:49 PM by Chuck Harris

    jcml conversion to ds.xml

    Chuck Harris Newbie

      I am moving from jboss 2.3 to 3.2 and am having trouble with
      Data source configuration. The current data source in jboss.jcml
      Looks like:


      com.merant.datadirect.jdbc.sqlserver.SQLServerDriver
      ,sun.jdbc.odbc.JdbcOdbcDriver,org.hsql.jdbcDriver
      ,org.enhydra.instantdb.jdbc.idbDriver



      org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
      DevDb
      jdbc:sqlserver://url:1433;SelectMethod=cursor
      DatabaseName=devdb
      idpass
      idpass
      0
      10
      false
      1200000
      120000
      false
      false
      true
      false
      false
      1800000
      1.0

      I got this to work but it seems painfully slow when multiple threads are
      hitting the db at the same time:

      <local-tx-datasource>
      <jndi-name>DevDb</jndi-name>
      <connection-url>jdbc:sqlserver://url:1433;SelectMethod=cursor;DatabaseName=devdb
      </connection-url>
      <driver-class>com.merant.datadirect.jdbc.sqlserver.SQLServerDriver</driver-class>
      <user-name>idpass</user-name>
      idpass
      <min-pool-size>10</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <blocking-timeout-millis>5000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      </local-tx-datasource>

      I tried using:

      <xa-datasource>
      <jndi-name>DevDb</jndi-name>
      <xa-datasource-class>com.merant.datadirect.jdbc.sqlserver.SQLServerDriver</xa-datasource-class>
      <xa-datasource-property name="ServerName">jdbc:sqlserver://url:1433</xa-datasource-property>
      <xa-datasource-property name="DatabaseName">devdb</xa-datasource-property>
      <xa-datasource-property name="SelectMethod">cursor</xa-datasource-property>
      <xa-datasource-property name="User">idpass</xa-datasource-property>
      <xa-datasource-property name="Password">idpass</xa-datasource-property>
      <!-- not sure if these should be here
      <user-name>idpass</user-name>
      idpass -->
      </xa-datasource>

      But it throws the following exception. What is the correct conversion of the jcml configuration?
      Are there other files that must be modified?


      08:45:17,311 WARN [Thread-5] [org.jboss.resource.connectionmanager.JBossManagedConnectionPool]
      Throwable while at tempting to get a new connection:
      org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable:
      (java.lang.ClassCastException: com.merant.datadirect.jdbc.sqlserver.SQLServerDriver)
      at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.createManagedConnection
      (XAManagedConnectionFactory.java:145) at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnection
      (InternalManagedConnectionPool.java:372) at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedC
      onnectionPool.java:165) at org.jboss.resource.connectionmanager
      .JBossManagedConnectionPool$OnePool.getConnection(JbossMana
      gedConnectionPool.java:696) at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnection
      Manager2.java:426) at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionMana
      ger.java:331) at org.jboss.resource.connectionmanager
      .BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:509) at org.jboss.resource.connectionmanager
      .BaseConnectionManager2
      $ConnectionManagerProxy.allocateConn
      ction(BaseConnectionManager2.java:839)
      at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:102)
      at mt.DBAccessUtils.getConnection(DBAccessUtils.java)
      at mt.DBAccessUtils.getConnection(DBAccessUtils.java)
      at mt.UserDBAccess.getConnection(UserDBAccess.java)
      at mt.UserDBAccess.getUserByUsername(UserDBAccess.java)
      at mt.UserDBProxy.getUserByUsername(UserDBProxy.java)
      at org.apache.jsp.afsp_login_post_jsp._jspService(afsp_login_post_jsp.java:88)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:643)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:643)
      at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:228)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:641)
      at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:641)
      at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:641)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2416)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:643)
      at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:641)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:641)
      at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:641)
      at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.ja
      va:641)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:601)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:3
      92)
      at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:565)
      at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
      at java.lang.Thread.run(Thread.java:479)
      + nested throwable:
      java.lang.ClassCastException: com.merant.datadirect.jdbc.sqlserver.SQLServerDriver
      at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.getXADataSource(XAManagedConnect
      ionFactory.java:240)
      at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManaged
      ConnectionFactory.java:137)
      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnection(InternalManage
      dConnectionPool.java:372)
      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedC
      onnectionPool.java:165)
      at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool.getConnection(JbossMana
      gedConnectionPool.java:696)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnection
      Manager2.java:426)
      at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionMana
      ger.java:331)
      at org.jboss.resource.connectionmanager
      .BaseConnectionManager2.allocateConnection(BaseConnectionManager2.j
      ava:509)
      at org.jboss.resource.connectionmanager
      .BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:839)

        • 1. Re: jcml conversion to ds.xml
          Chuck Harris Newbie

          One correction, the move is from 2.4 to 3.2.

          • 2. Re: jcml conversion to ds.xml
            Jon Barnett Master

            Your first configuration example is the correct format for the new -ds.xml. The second configuration is not as this driver is not a true XA DataSource implementation.

            There are some reports that the selectmethod=cursor can be slower under certain conditions. Since this is intended as I understand it for allowing concurrent queries on the same connection perhaps try switching it off.

            As an alternative, have you tried the jTDS driver from SourceForge just to trial a comparison of speeds and effects of special connection parameters?

            • 3. Re: jcml conversion to ds.xml
              Chuck Harris Newbie

              I tried removing the selectmethod=cursor but I started getting
              exceptions. I downloaded the jTDS driver and it works about the same
              as far as performance goes. I did not need the selectmethod with
              the jTDS driver. I am getting this message:

              13:21:10,436 WARN [Thread Pool Worker-36] [org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResour
              ce] Prepare called on a local tx. Use of local transactions on a jta transaction with
              more than one branch may result in inconsistent data in some cases of failure.
              Looking into this message, it looks like it has to do with using a local data source
              rather than an xa which was why I was trying to use the xa . What is the difference
              between a local and xa connection and is one better than the other? The jcml
              shows the service as a XADataSource. Is jb just convereting it to a local ?

              • 4. Re: jcml conversion to ds.xml
                Jon Barnett Master

                The 2.x versions of JBoss, as far as I can remember did not support true XA. The wrapper implementation provided XA-look-alike (wrapper) for JDBC drivers that did not actually provide XA capabilities.

                Previous versions of jboss provided database connectivity and connection pooling through the XADataSourceLoaderMBean. This was normally used with the XADataSourceImpl, which provided an xa interface for a non-xa jdbc driver. Of course, since the underlying driver did not support xa transactions, neither did the XA wrapper.

                The old JBoss should have actually implemented the underlying service as Local with the XA wrapper around it.

                XA provides for distributed transactions - e.g. such as across a chain of JBoss servers. Local means local to the server. So unless your driver really is doing XA work and supports XA (getXAConnection, and your JNDI lookup is cast as an XADataSource) it probably isn't one. From your first stacktrace, it looks like the driver doesn't support the XADatasource methods expected - JBoss 3.x provides support for a full XA driver through the xa-datasource.

                Now it looks like in your code you are trying to do some JTA/XA related statements that isn't supported by local - are you doing a prepare()?. You may want to search the forums for hints on this problem and recheck your code.

                Hope this makes sense.

                • 5. Re: jcml conversion to ds.xml
                  Chuck Harris Newbie

                  Thanks for the help.
                  We are using PreparedStatement. Does that qualify?
                  I downloaded the latest version of the datadirect driver
                  and tried to use the xa and it still failed. I think you are
                  right that we are not actually using xa-datasource. The is no
                  getXAconnection or casting to an XADataSource.
                  The jta warning message is displayed after a message driven bean
                  has updated several tables in the mssql 7 database from the
                  same thread. Thread http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=
                  indicates
                  that the message is displayed only once and it is for every message the bean receives.
                  Could the multiple updates
                  from the same thread be the cause for the warning message?

                  • 6. Re: jcml conversion to ds.xml
                    Jon Barnett Master

                    It is possible that you are impacting this. Since the MDB is sort of like a stateless session bean in that the transaction completes when you leave the method, this is when the transaction manager will try to commit any writes pending on any of the connections from the connection pool for that execution instance. A PreparedStatement will work fine if you get the connection when you enter the method of the MDB, create the PreparedStatement, execute it, and close it and the connection (return it to the connection pool) before you leave the method.

                    Since we don't know enough about what you are doing, it is difficult to determine whether you have done something that violates the transactional boundaries.

                    Hope that helps.

                    • 7. Re: jcml conversion to ds.xml
                      Chuck Harris Newbie

                      Essentially, the MDB executes a case that
                      Contains one or a series of
                      activities based upon what the message (case) contains.
                      The MDB updates the database and then
                      calls the case which executes the activitie(s).
                      Each activity accesses the database many times but
                      each time a new connection
                      (and prepared statement is instantiated) is
                      retrieved from the pool
                      and then it is returned (stmt closed()) to the pool. Each activity will
                      update several tables in its respective execute method.
                      So it will update table a and then update table b and
                      then maybe update a again. This could occur for each activity. Does that description make sense of what I am doing?

                      • 8. Re: jcml conversion to ds.xml
                        Jon Barnett Master

                        There is nothing obviously wrong with what you are doing. I'm not quite sure why a prepare() is being called unless it is related to the JMS. What if the MDB calls an EJB that does the connection work? I haven't had the chance to experiment with JDBC connections in MDBs. We usually use them to decouple the primary systems from failure prone systems like SMS servers, pager transmitters and e-mail servers.

                        • 9. Re: jcml conversion to ds.xml
                          Chuck Harris Newbie

                          Thanks for the help.
                          I can try that to see if adding an ejb to handle connections
                          will clear the message. If I do nothing, what am I exposing
                          myself to? If the worst is that a failure will result in some
                          tables not being populated, I can live with that.
                          I just start things over from the beginning.
                          If tables are not updated when no failure occurs, I am
                          concerned.

                          • 10. Re: jcml conversion to ds.xml
                            Jon Barnett Master

                            The message is just a warning saying that it may not be able to restore things if things go wrong. Of course, that is the main point of the EJBs - to add that transactional quality to your system.

                            I'm finding it strange that there is an XA (distributed transaction) - unless the JMS is tangled in it, normally you shouldn't have a problem with the standard JTA as you are not performing anything in a distributed real distributed sense.