CODEWITHSUNDEEP
javaoracle-databasejdbc
Robert H
Robert H

Reputation: 11730

Why is my JDBC update not working?

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:

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.

Upvotes: 5

Views: 13821

Answers (3)

Robert H
Robert H

Reputation: 11730

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.

Upvotes: 2

Cristian Meneses
Cristian Meneses

Reputation: 4041

I went through this issue a few years ago, and when I used a String value on a WHERE clause, using tokens, it didn't worked. BTW, it was also an Oracle database (10g)

Finally, I gave up, and changed the code to concatenate the value instead of tokenize it

From

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

To

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

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

That did the trick for me... This is not the most elegant solution, but is a workaround

====================== UPDATE 1 ============================

I've done a little more research, since I also faced this problem and still didn't get a valid answer

Looks like Oracle drivers expects you to pass a String with similar length to the table definition. This happens only to fixed length columns (like CHAR), VARCHAR columns doesn't seem to be affected by this.

It means that, if order_id is a CHAR(10) , then you should pad your String to complete the column length. A better approach would be to trim the database value to match the token value.

update = "UPDATE shop_ca_orders SET added_customer = 'y' " + 
         " WHERE LTRIM(RTRIM(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());
}

Upvotes: 1

Brian Agnew
Brian Agnew

Reputation: 272277

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.

Upvotes: 2

Related Questions