0 Replies Latest reply on Feb 5, 2002 4:21 AM by milesif

    porting from BAS 4.5: problem with autogenerated PK in BMP

    milesif

      I am trying to port an application (not particularly well written) from BAS 4.5 to JBoss.


      I have a BMP called EUnita that in its create method do the following:

      // creazione dell'unità
      Connection connection = null;
      PreparedStatement statement = null;
      try {
      connection = dataSource.getConnection();
      statement = connection.prepareStatement("INSERT INTO " + tableName + " (Nome, Descrizione, Disabilitato, F_Deposito, F_ContenitoriUnita, F_Calendari) VALUES (?, ?, ?, ?, ?, ?)");
      QueryHelp.SetParam( statement, 1, getNome() );
      QueryHelp.SetParam( statement, 2, getDescrizione() );
      QueryHelp.SetParam( statement, 3, getDisabilitato() );
      QueryHelp.SetParam( statement, 4, getFDeposito() );
      QueryHelp.SetParam( statement, 5, getFContenitoreUnita() );
      QueryHelp.SetParam( statement, 6, getFCalendario() );
      if (statement.executeUpdate() != 1) {
      throw new CreateException( CBaseX.getExtendedMessage(this.getClass(), "ejbCreate", CCodeX.eEntityInsert ) );
      }
      statement.close();
      statement = null;
      //
      id = QueryHelp.getLastID(connection, tableName);
      //
      connection.close();
      connection = null;
      //
      return id;
      }
      catch(SQLException e) {
      throw new CErrorX( this.getClass(), "ejbCreate", CCodeX.eEntityInsert, e);
      }
      finally {
      try {
      if (statement != null) {
      statement.close();
      }
      }
      catch(SQLException e) {
      }
      try {
      if (connection != null) {
      connection.close();
      }
      }
      catch(SQLException e) {
      }
      }


      in the post create it get the home of another entity and call its create, to insert a record in a relation table (that also has an autoincrementing PK).

      If I log all this I can see that after executing correctly both methods ( correctly means open a connection, do the insert, close the connection) I get an exception saying that I am trying to insert an already existing bean EUnita and it shows me the new id that is correct. If I comment the call in the postCreate all is fine.
      I use autogenerated PK in MS SQLServer 7 and after insert I retrieve them with a call to

      id = QueryHelp.getLastID(connection, tableName);
      that make a "select @@identity from tablename" on the given connection.

      Here a few question:
      1. Why I get the error after my insert has executed correctly both the create and postcreate: I open insert and close all connection regularly, only in the end I get the error?
      2. If for some reason the bean was to be inserted twice, the database should not give me an error on the name field that is unique?

      In the code there are things like that everywhere, so I would find out where the problem is.

      Thanks to anybody helping me.


      Ciao Francesco