1 Reply Latest reply on Feb 9, 2006 5:19 PM by Michael Moran

    local-tx-datasource and autocommit

    Jonathan Voxendal Newbie

      Hi,

      In some parts of my code I need to handle the transactions manually.

      Dont pay too much attention to the actual example, just want to show the flow.

      This is an example:

      InitialContext ctx = new InitialContext();
      DataSource ds = (DataSource)ctx.lookup("java:qpDS");
      Connection conn = ds.getConnection();
      Savepoint sp = conn.setSavepoint();
      Statement stmnt = conn.createStatement();
      stmnt.execute("insert into someTable (id, name) values(99, 'Text..')");
      // Do something critacal that might fail here...
      if(failed) {
       conn.rollback(sp);
      }
      stmnt.close();
      conn.close();
      


      Ofcourse this will fail if my connections autocommit property is true - which will be the case when using a local-tx-datasource.

      I know I can use no-tx-datasource to get it work, but other parts of the application requires local-tx-datasource.

      What do you suggest? Should I use two diffrent datasources? one local-tx-datasource and one no-tx-datasource?

      Doesn't sound like a good solution to me though.

        • 1. Re: local-tx-datasource and autocommit
          Michael Moran Newbie

          Jobo,

          I cannot help you on this post but am looking for information on setting autocommit.

          I'm using the mysql jdbc driver with a local-tx-datasource, Hibernate and cannot turn off auto commit by calling connection.setAutocommit(false). My rollbacks fail because you cannot rollback with autocommit set to true.

          If I issue the command SET AUTOCOMMIT=0 at the command line it works just fine.

          any help is greatly appreciated.