0 Replies Latest reply on Feb 7, 2002 5:26 AM by francesco milesi

    Autogenerated PK problem in BMP (just in one case, not alway

    francesco milesi Newbie

      I have to confess this is a repost, in the hope anybody has pity on me.

      I am trying to port an application from BAS 4.5 to JBoss.
      The database is MS Sql Server 7.0 and I use autogenerated PK. The entiy beans are BMP.

      Th ejbCreate of a bean called EUnita 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) {
      }
      }


      where

      id = QueryHelp.getLastID(connection, tableName);

      simply makes a "select @@identity from 'tablename'" on the given connection (it is a static method).

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

      public void ejbPostCreate(String nome, String descrizione, Boolean disabilitato, int keyLivello) throws CreateException, NomeDuplicatoException, RelazioneDuplicataException, RelazioneNonAmmessaException {
      ELivelliRemote eLivello = null;
      try {
      eLivello = HomeHelp.getELivelliHome.create(getId(), getLevel())
      }
      catch (Exception e) {
      throw new CErrorX(this.getClass(), "ejbPostCreate", CCodeX.eErrore, e);
      }
      }



      where the call to "HomeHelp.getELivelliHome.create(getId(), getLevel())" does the same of the above ejbCreate, and use the same technique to retrieve the autogenerated PK.

      If I log all this I can see that after executing correctly the three methods (the first create, the ejbPostCreate and the second create), that means open a connection, do the insert, retrieve the new generated id (that is always correct, if I check the last in the database), close the connection, I get an exception saying that I am trying to insert an already existing bean EUnita with id equal to that I got from the select @@identity (that is correct).
      If I comment the call in the ejbPostCreate all is fine.

      I do not understand why this happens.
      1. I do not call the create twice.
      2. If I comment ejbPostCreate all is ok
      3. There is something wrong with the way I retrieve the autogenerated PK? or there is some configuration parameter I should set ? May be isolate the select @@identity in a requires new transaction? or some container parameter ?
      4. Especially I do not understand why the error says I am trying to insert another bean with the same id, when I completely ignore it in my insert statements. I think the container does some checks I am not aware of.

      The problem is I have a lot of entities that do similar things.

      Thanks to anybody helping me. I am in great need. I really would like to port the application to jBoss.

      I also apologize for the long mail.

      Ciao Francesco