• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problems with sentence "SELECT MAX(KEY_NAME_TABLE) FROM NAME_TABLE" in MDB (WAS)

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a MDB (works with a Queue) working in WAS 6.1. This MDB have the next sentence SQL (ORACLE) in the method onMessage():



//GET THE KEY VALUE TO INSERT
statement = connection.prepareStatement("SELECT MAX(KEY_NAME_TABLE) FROM NAME_TABLE");

...
//INSERT INTO DATA BASE WITH THE RECOVERED KEY VALUE
...

if I send several messages sometimes the result is same (KEY VALUE).This generates a error when I going to insert to data base: ORA-00001: unique constraint violated

I have tried in Jboss and works fine.



I need help!!
 
author & internet detective
Posts: 41915
910
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Carlos,
Are you running those two SQL statements within a transaction? If not, the error you described is likely to occur under load.
 
Carlos Andres Holguin Arboleda
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, Jeanne. My onMessage() invoke the next method:

public void insert( InfoOperacion operacion) throws AuditoriaException
{
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;





String sql="INSERT INTO COMPONENTE_AUDITORIA ( COMPONENTE_AUDITORIA_ID_OP"+
",COMPONENTE_AUDITORIA_USUARIO,COMPONENTE_AUDITORIA_US_BD"+
",COMPONENTE_AUDITORIA_F_HORA,COMPONENTE_AUDITORIA_ACCION"+
",COMPONENTE_AUDITORIA_OBJETO," +
"COMPONENTE_AUDITORIA_ANTES,COMPONENTE_AUDITORIA_DESPUES" +
",COMPONENTE_AUDITORIA_OID, COMPONENTE_AUDITORIA_IP) " +
"values (?,?,?,?,?,?,?,?,?,?)";
try
{
connection = DBLocator.getConnection ();
//Buscar ultimo id
statement = connection.prepareStatement("SELECT MAX(COMPONENTE_AUDITORIA_ID_OP) FROM COMPONENTE_AUDITORIA");
rs = statement.executeQuery();
if(rs.next()) {
operacion.setIdOperacion(rs.getInt(1)+1);
} else {
operacion.setIdOperacion(1);
}
rs.close();
statement.close();
statement = connection.prepareStatement ( sql );

statement.setInt(1, operacion.getIdOperacion());
statement.setString(2,operacion.getUsuario());
statement.setString(3,operacion.getUsuarioBD());
statement.setString(4,operacion.getFechaHora());
statement.setString(5,operacion.getAccion());
statement.setString(6,operacion.getObjeto());
statement.setString(7,operacion.getAntes().toString());
statement.setString(8,operacion.getDespues().toString());
statement.setString(9,operacion.getOid());
statement.setString(10,operacion.getIp());
statement.executeUpdate ( );
}
catch(Exception e)
{
throw new AuditoriaException(e.getMessage(),e);
}
finally
{
DBLocator.closeDBObject(rs);
DBLocator.closeDBObject(statement);
DBLocator.closeDBObject(connection);

}
}
Do you have any suggestion for this problem??, thanks

[ September 21, 2008: Message edited by: Carlos Andres Holguin Arboleda ]
[ September 21, 2008: Message edited by: Carlos Andres Holguin Arboleda ]
 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

The best pattern is not to write the Transaction related stuff into MDB. It should be delegated to Session bean which is meant for that (Message Facade). I was doing similar your same requirement while ago and I couldn't see any problem.

Try this and let me know the results


Originally posted by Carlos Andres Holguin Arboleda:
Hi, Jeanne. My onMessage() invoke the next method:

public void insert( InfoOperacion operacion) throws AuditoriaException
{
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;





String sql="INSERT INTO COMPONENTE_AUDITORIA ( COMPONENTE_AUDITORIA_ID_OP"+
",COMPONENTE_AUDITORIA_USUARIO,COMPONENTE_AUDITORIA_US_BD"+
",COMPONENTE_AUDITORIA_F_HORA,COMPONENTE_AUDITORIA_ACCION"+
",COMPONENTE_AUDITORIA_OBJETO," +
"COMPONENTE_AUDITORIA_ANTES,COMPONENTE_AUDITORIA_DESPUES" +
",COMPONENTE_AUDITORIA_OID, COMPONENTE_AUDITORIA_IP) " +
"values (?,?,?,?,?,?,?,?,?,?)";
try
{
connection = DBLocator.getConnection ();
//Buscar ultimo id
statement = connection.prepareStatement("SELECT MAX(COMPONENTE_AUDITORIA_ID_OP) FROM COMPONENTE_AUDITORIA");
rs = statement.executeQuery();
if(rs.next()) {
operacion.setIdOperacion(rs.getInt(1)+1);
} else {
operacion.setIdOperacion(1);
}
rs.close();
statement.close();
statement = connection.prepareStatement ( sql );

statement.setInt(1, operacion.getIdOperacion());
statement.setString(2,operacion.getUsuario());
statement.setString(3,operacion.getUsuarioBD());
statement.setString(4,operacion.getFechaHora());
statement.setString(5,operacion.getAccion());
statement.setString(6,operacion.getObjeto());
statement.setString(7,operacion.getAntes().toString());
statement.setString(8,operacion.getDespues().toString());
statement.setString(9,operacion.getOid());
statement.setString(10,operacion.getIp());
statement.executeUpdate ( );
}
catch(Exception e)
{
throw new AuditoriaException(e.getMessage(),e);
}
finally
{
DBLocator.closeDBObject(rs);
DBLocator.closeDBObject(statement);
DBLocator.closeDBObject(connection);

}
}
Do you have any suggestion for this problem??, thanks

[ September 21, 2008: Message edited by: Carlos Andres Holguin Arboleda ]

[ September 21, 2008: Message edited by: Carlos Andres Holguin Arboleda ]

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic