7 Replies Latest reply on Nov 23, 2005 11:59 AM by adrian.brock

    Wrapped Connection

    funkyhippy

      I have a situation where I need to get a connection which I can commit and rollback as required.

      I have a method that returns a java.sql.connection object from a DataSource:

      DataSource ds = (DataSource) jndiCntx.lookup("java:DSNAME");
      con = ds.getConnection();
      con.setAutoCommit(false);


      However the setAutoCommit isn't wokring as when I look at the connection auto commit is alwasy true. The Datasource is set up as follows:
      <local-tx-datasource>
       <jndi-name>DSNAME</jndi-name>
       <connection-url>jdbc:jtds:sqlserver://DBNAME:1433/rd3;tds=7.0;lastupdatecount=true</connection-url>
       <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
       <user-name>UID</user-name>
       <password>PWD</password>
       <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
       </local-tx-datasource>


      I have tried to set a <Connection-proprty> tag to set auto commit to false, Setting the transaction isolation value and defining the datasource as no-tx-datasource. None of which make any difference. Googling about I notice that you can get a WrappedConnection and return the underlying Connection object which I hope I can then set autocommit on. Unfortunately I can't find the jar that I need to reference to get the WrappedConnection class from?

      This must be something quite simple I just can't seem to find any info at all!
      Can someone point me in the right direction?
      TIA

        • 1. Re: Wrapped Connection
          funkyhippy

          I've managed to figure it out. The needed jar is called jboss-common-jdbc-wrapper.jar and is in <JBOSS-ROOT>/server/all/lib.

          You have to code it in this way:

          con = ds.getConnection();
          WrappedConnection wc = (WrappedConnection) con;
          wc.getUnderlyingConnection().setAutoCommit(false);
          con.setAutoCommit(false);
          


          If you don't include the second setAutoCommit you get errors.



          • 2. Re: Wrapped Connection

            What are you on about?

            There is a "lazy auto-commit" optimization that means it doesn't change the auto-commit
            unless it needs to do so.

            All you are doing is breaking the pooling.

            • 3. Re: Wrapped Connection
              funkyhippy

              If thats so how do I rollback all transactions on a connection. The autocommit property if always set to true unless I sepcifically change it.

              How do this break connection pooling?

              Please elaborate....

              • 4. Re: Wrapped Connection

                http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossHelp
                No, use search if you want elaboration.

                How do this break connection pooling?

                Err... Modifying the state in a way unknown to the container?

                • 5. Re: Wrapped Connection
                  funkyhippy

                  Did not read my original post!?

                  I HAVE searched.
                  I have been searching all day for a way to do this but can find no information about how to do this. (Do you have any links or keywords that would give the results I need).

                  I don't understand what state are you talking about?

                  I am accessing the DAO classes from within a stateless session bean.

                  How do I tell jboss I want a jdbc connection I can call rollback on?

                  What I need is a way to rollback several SQL statements if any fail. I thought that if I get a close a connection that I have got from the datasource that it would override this and put it back in the pool?

                  • 6. Re: Wrapped Connection

                    BEGIN LART

                    "FunkyHippy" wrote:
                    Did not read my original post!?

                    I HAVE searched.

                    So much you didn't even "READ THIS FIRST" at the top of the forum.
                    You certainly didn't read the last link I posted


                    I have been searching all day for a way to do this but can find no information about how to do this. (Do you have any links or keywords that would give the results I need).

                    The ones I already gave you "Lazy" and "autoCommit"


                    I don't understand what state are you talking about?

                    I can't help that. But this probably lies at the bottom of your problem.


                    I am accessing the DAO classes from within a stateless session bean.

                    What you are trying to do is irrelevant, what your are actually doing
                    is what is important.

                    How do I tell jboss I want a jdbc connection I can call rollback on?
                    

                    no-tx-datasource or don't use CMT


                    What I need is a way to rollback several SQL statements if any fail. I thought that if I get a close a connection that I have got from the datasource that it would override this and put it back in the pool?

                    Another FAQ

                    For reference:
                    http://www.jboss.com/index.html?module=bb&op=viewtopic&t=68044

                    I'll pop back next week and see whether you've supplied any useful information.

                    END LART

                    • 7. Re: Wrapped Connection

                      For people that do come across this duplicate question in future.

                      I've copied the description given in this (non-)bug report:
                      http://jira.jboss.com/jira/browse/JBAS-1426
                      to the WIKI
                      http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJCALazyAutoCommit

                      And I would guess those of you that read release notes, would probably have found this?
                      http://jira.jboss.com/jira/browse/JBAS-2115