CODEWITHSUNDEEP
javaoracle-databasejdbcprepared-statement
user3189663
user3189663

Reputation: 211

PreparedStatement executeUpdate() updates wrong record

I am using an Oracle database and after sometime I receive the following Exception:

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

I analysed my code and I seem to be closing all the ResultSet. This Exception only happens sometimes. Due to this error, I decided to change my code a little bit. Below is the code:

public class Audit {

    private Connection connection;
    private PreparedStatement insertAuditPreparedStatementSent;
    private static int counter;
    private static int JDBC_COUNTER;

    public Audit() throws Exception {
        connection = DriverManager.getConnection("url", "username", "password");
    }

public int insertAudit(String message, java.util.Date sent) throws Exception {
     PreparedStatment preparedStatement = prepareStatement(new String("INSERT INTO Audit (message, sent) VALUES (?, ?)");
     if(JDBC_COUNTER == 0) {
            // this is required to be executed so that ORA-08002 SQLException is not thrown
            connection.createStatement().executeQuery(new String("SELECT AUDIT_SEQUENCE.NEXTVAL FROM DUAL"));
        }
     ResultSet resultSet = connection.createStatement().executeQuery(new String("SELECT AUDIT_SEQUENCE.CURRVAL FROM DUAL"));
     resultSet.next();
     primaryKey = resultSet.getInt(new String("CURRVAL"));
     resultSet.close();
     return primaryKey;
}

public void executeUpdateAudit(int id, java.util.Date sent) throws Exception {
    if(updateAuditPreparedStatement == null) {
         updateAuditPreparedStatement = connection.prepareStatement(new String("UPDATE AUDIT SET SENT = ? WHERE AUDIT_ID = " + id));
    }
    updateAuditPreparedStatement.setTimestamp(1, new java.sql.Timestamp(sent.getDate());
    int i = updateAuditPreparedStatement.executeUpdate();
    connection.commit();   
}

public static void main(String[] args) throws Exception {
     Audit audit = new Audit();
     int primaryKey = audit.insertAudit("message", new java.util.Date());
     audit.executeUpdateAudit(primaryKey, new java.util.Date());
     int primaryKey2 = audit.insertAudit("message2", new java.util.Date());
     audit.executeUpdateAudit(primaryKey2, new java.util.Date());  
}
}

When inserting record 2 and updating record 2 only the updateAuditPreparedStatement.executeUpdate() returns 1, but the database updates the first record rather than the second record.

The reason to change the code is because I believe the PreparedStatement creates a new cursor each time. So, I want the insertAuditPreparedStatementSent to be there on many inserts without closing. I have tried insertAuditPreparedStatementSent.clearBatch() and insertAuditPreparedStatementSent.clearParameters().

I am not sure why it is updating record 1 on the primaryKey of record 2. The SQL is fine.

Any ideas?

Upvotes: 0

Views: 731

Answers (3)

PT_STAR
PT_STAR

Reputation: 505

Please check the oracle parameter

open_cursors

You will find this in the enterprise manager or by executing the following SQL:

select * from v$parameter a
where a.NAME = 'open_cursors';

If this parameter is very low (e.g. < 300) and you have lots of processes/users working at the same time, this error can happen.

Upvotes: -1

Alex Poole
Alex Poole

Reputation: 191275

In executeUpdateAudit you prepare the statement once using the first ID seen:

if(updateAuditPreparedStatement == null) {
     updateAuditPreparedStatement = connection.prepareStatement(
         new String("UPDATE AUDIT SET SENT = ? WHERE AUDIT_ID = " + id));
}
updateAuditPreparedStatement.setTimestamp(1,
  new java.sql.Timestamp(sent.getDate());

In the second call the ID from the first call is still being used, since it's effectively hard-coded in the SQL.

You should be using a parameter for the ID as well:

if(updateAuditPreparedStatement == null) {
     updateAuditPreparedStatement = connection.prepareStatement(
         new String("UPDATE AUDIT SET SENT = ? WHERE AUDIT_ID = ?"));
}
updateAuditPreparedStatement.setTimestamp(1,
    new java.sql.Timestamp(sent.getDate());
updateAuditPreparedStatement.setInt(2, id);

Not sure why you're using new String explicitly everywhere; it would be a little simpler as:

if(updateAuditPreparedStatement == null) {
     updateAuditPreparedStatement = connection.prepareStatement(
         "UPDATE AUDIT SET SENT = ? WHERE AUDIT_ID = ?");
}
updateAuditPreparedStatement.setTimestamp(1,
    new java.sql.Timestamp(sent.getDate());
updateAuditPreparedStatement.setInt(2, id);

This is nothing to do with your ORA-01000, but this seems to be the main thrust of this question - you shouldn't really ask about two things at once, particularly if they aren't directly related...

Upvotes: 1

Pavel Horal
Pavel Horal

Reputation: 18194

You are not closing this:

if(JDBC_COUNTER == 0) {
    // this is required to be executed so that ORA-08002 SQLException is not thrown
    connection.createStatement().executeQuery(new String("SELECT AUDIT_SEQUENCE.NEXTVAL FROM DUAL"));
}

Also prepared statements needs to be closed as well and you are missing try/catch/finally in all these methods to prevent resource leakage. I strongly discourage using JDBC directly as it is very hard to work with.

Upvotes: 1

Related Questions