1 Reply Latest reply on Oct 1, 2002 2:39 PM by Paul Cleary

    MSSQL JDBC Commit problem...

    Paul Cleary Newbie

      Downloaded latest and greatest MSSQL Type 4 driver, am running JBoss 3.0 under tomcat.

      I have no problems selecting data from the database, I am having problems inserting rows into the database. I execute an INSERT INTO, and I don't receive any errors and the app runs just fine. However, when I go into Enterprise Manager or Query Analyzer I get hung up when trying to look into the data in the tables, it's almost like JBOSS has a lock on the table and the INSERTs are still in the process of happening. Here is some sample code, I attached the mssql-service.xml file.

      I am going to try downloading and trying everything under JBoss 3.0.3, but maybe I am doing something else wrong.

      Thanks for you help,
      pC

      public PalletItem createPalletItem(String palletNumber) {
      PalletItem ret = null;
      PreparedStatement ps = null;
      Connection conn = null;
      try{
      // Connect to the database
      conn = DBHelper.getConnection(DSN);

      // Create a Statement
      ps = conn.prepareStatement("INSERT INTO dbo.PalletItem " +
      "(PalletNum, PalletItemNum) " +
      "VALUES (?, ?)");

      //set the parameters...
      //the create date is right now...
      Date rightNow = DBHelper.genDate();
      String primaryKey = DBHelper.genPrimaryKey();

      ps.setString(1, palletNumber);
      ps.setString(2, primaryKey);

      int rows = ps.executeUpdate();

      //create the pallet item...
      ret = new PalletItem();
      ret.setPalletItemNumber(primaryKey);


      } catch (Exception ex) {
      ex.printStackTrace();
      } finally {
      //release the resources...
      try {
      if (ps != null) ps.close();
      if (conn != null) conn.close();
      } catch (Exception ex) { /* don't do anything right now, we should be ok */ }
      }

      return ret;
      }


      HERE IS THE GETCONNECTION METHOD...
      public static java.sql.Connection getConnection(String jndiName) throws java.sql.SQLException {
      Connection conn = null;
      try {
      DataSource ds = ServiceLocator.getInstance().getDataSource(jndiName);
      conn = ds.getConnection("sa", "dpex");
      //conn.setAutoCommit(true);
      } catch (ServiceLocatorException sle) {
      sle.printStackTrace();
      }

      return conn;
      }