CODEWITHSUNDEEP
javapostgresqljdbc
JBalaguero
JBalaguero

Reputation: 201

POSTGRES :: Updating when SQLException on insert

I'm new on Postgres. I'm trying to perform the following code (that works with MySQL) using java / postgres jdbc:

for (int i = 0; i < arr.size(); i++)
{       
 dtoIdent ident = arr.get(i);

 stIns.setLong(1, idInterface);
 stIns.setString(2, tipo);

 try { stIns.executeUpdate(); } 
 catch (SQLException sqe) 
 { 
  stUpd.setString(1, ident.getTipo());
  stUpd.executeUpdate();
 }
}

The connection is in autcommit = false. 'stIns' and 'stUpd' are 'PreparedStatements'.

I'm getting a 25P02.

Is it possible to do this with Postgres? Any workaround?

Many thanks,

Joan.

Upvotes: 1

Views: 135

Answers (2)

MatheusOl
MatheusOl

Reputation: 11875

In order to handle an exception and be able to ignore it and execute another statement, you need to embrace your handling with SAVEPOINTS. For instance:

Savepoint sv = null;
for (int i = 0; i < arr.size(); i++)
{       
 dtoIdent ident = arr.get(i);

 stIns.setLong(1, idInterface);
 stIns.setString(2, tipo);

 try
 {
     // create the savepoint
     sv = your_conn_object.setSavepoint();
     stIns.executeUpdate();
     // release the savepoint, as we don't need it anymore
     your_conn_object.releaseSavepoint(your_conn_object);
 } 
 catch (SQLException sqe) 
 { 
     // We caught an exception, so let's rollback to the savepoint
     your_conn_objectrollback(sv);
     stUpd.setString(1, ident.getTipo());
     stUpd.executeUpdate();
 }
}

Upvotes: 1

Hiro2k
Hiro2k

Reputation: 5587

Since your autocommit is false, and your statement was aborted you must rollback the connection since it's now in an invalid state.

In your catch block simply do this before you use the executeUpdate:

conn.rollback();

However this will rollback ALL changes that had previously been done in the transaction. If this is a problem then you should create a Savepoint with conn.setSavepoint() right before the try statement, and then in the catch block rollback to that Savepoint.

Upvotes: 1

Related Questions