Why is my JDBC update not working?

Question

I've been migrating from PostgreSQL 9.1 to Oracle 11gR2 and have run into an odd issue.

I've been running code that updates a table when a customer has been added to our ERP system. This code has been running on PostgreSQL with no issues since March. Now that I'm switching to Oracle the same code is no longer updating.

Original Code

update = "UPDATE store SET added_customer = 'y' WHERE order_id = ?";
try {
    PreparedStatement pStmnt = getConn().prepareStatement(update);
    pStmnt.setString(1, orderId);               
    results = pStmnt.executeUpdate();

    if (results > 0) {
        added = true;                   
    }       
} catch (SQLException ex) {
    LOGGER.error(ex.toString());
}

No exceptions were thrown, but no data changed so I thought "autocommit must not be working, lets commit manually":

New Code

update = "UPDATE shop_ca_orders SET added_customer = 'y' WHERE order_id = ?";
try {                 
    getConn().setAutoCommit(false); //Added
    PreparedStatement pStmnt = getConn().prepareStatement(update);
    pStmnt.setString(1, orderId);               
    results = pStmnt.executeUpdate();

    if (results > 0) {
        added = true;
        getConn().commit(); //Added
        getConn().setAutoCommit(true); //Added
    }       
} catch (SQLException ex) {
    LOGGER.error(ex.toString());
}

Still no luck so I added LOGGER.debug("Update to order returned {}",results); after the executeUpdate statement and found I am returning 0, so no records are being updated.

Interesting, so I tried the query via SQL Developer and it updated correctly. This brings me to my question:

Why am I unable to update my database via JDBC?

Essential Data:

• order_id is type VARCHAR(255 BYTE).
• Java 7
• Oracle 11GR2 running on Amazons RDS
• Migrated from PostgreSQL 9.1 running on Heroku
• On PostgreSQL order_id is a character varying(255)

EDIT

A small schema change was undetected and resulted in a bug where the order ID was actually the name of the person, and not the order ID. Bonehead error on my end. Anyways, now that I have that resolved and pulling the correct order ID I have found that I am hanging on executeUpdate. Currently working on that issue. I'll likely create a new question if I am unable to resolve.

Answer 1

What does getConn() return ? I suspect it's a different (pooled?) connection each time.

This:

getConn().setAutoCommit(false); //Added
PreparedStatement pStmnt = getConn().prepareStatement(update);

should likely read:

Connection c = getConn();
c.setAutoCommit(false); //Added
PreparedStatement pStmnt = c.prepareStatement(update);

i.e. if getConn() returns a different connection each time, then you've got problems.

Answer 2

So my issue was two fold:

First I had an issue with my initial result set. During the migration one column was removed, I thought all the referenced columns were changed in code, however one was missing. Once this issue was resolved the order ID worked accurately and it attempted to update the database.

The second issue was a problem with the database hanging on the update. It turns out that the hang was caused by SQL Developer holding a lock on the database (or table, not sure which) - once I closed SQL Developer the update immediately completed and things went as expected.