Autogenerated PK problem in BMP (just in one case, not alway
milesif Feb 7, 2002 5:26 AMI 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