POSTGRES :: Updating when SQLException on insert

Question

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.

Why do you expect your insert statement to fail? Because a record with that primary key already exists? If that's the reason, why wouldn't you check for that first before deciding whether to insert a new record or to update the existing one? — Olaf
– Olaf, Commented Sep 13, 2013 at 16:23

Hiro2k · Accepted Answer · 2013-09-12 15:54:44Z

1

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.

answered Sep 12, 2013 at 15:54

Hiro2k

5,6474 gold badges26 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MatheusOl · Accepted Answer · 2013-09-12 15:57:10Z

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();
 }
}

Collectives™ on Stack Overflow

POSTGRES :: Updating when SQLException on insert

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related