Why is my JDBC update not working?

jdbc update query
how to update data in mysql using netbeans
update query in jdbc using preparedstatement
dynamic update query in jdbc
java prepared statement update multiple columns
update query not working in java
jdbctemplate update not working
scala jdbc update example

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.

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.

SQL Update not happening (JDBC and Relational Databases forum , Win a copy of Learning Progressive Web Apps this week in the HTML Pages No exceptions found.. but still the update is not happening. Re-run your code with commmit statement added. your problem may get solved. See the JDBC 2.1 API Specification, section 5.6 for more details.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table.

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.

How to use UPDATE statement using JDBC, driver.OracleDriver"); In the upcoming example, we are going to create a table within the Oracle database using its JDBC driver. My JDBC code isn't doing anything at all. It'll print out the "connecting to database" statement but that's it. I've referenced the JDBC jar file so i'm a little puzzled why my code isn't working. The code i've been trying to get working is below.

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

How to update a record in the database using JDBC?, I just worked up a Java MySQL UPDATE example, using the Java Driver"; String myUrl = "jdbc:mysql://localhost/test"; Class. I am learning how to use SQL with Java. I have installed the JDBC driver successfully and I am able to read the records from a database and print it on the screen. My problem occurs when trying to do either an update or insert statement, where nothing happens. Here is my code: Method where the problem resides

A Java MySQL UPDATE example, Creating a database connection. To create a PostgreSQL database connection from a Java program, you need to have the PostgreSQL JDBC driver. Check it out​  JDBC makes it possible to do establish a connection with a data source, send queries and update statements, and process the results. Simply, JDBC makes it possible to do the following things within a Java application: Establish a connection with a data source; Send queries and update statements to the data source; Process the results

How To Update Data in PostgreSQL Database Using JDBC, The prepareStatement() method accepts various parameters. In this example, you pass in a string which is a SQL statement. String sqlUpdate = "UPDATE  Register the JDBC driver: Requires that you initialize a driver so you can open a communications channel with the database. Open a connection: Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

Updating Data in MySQL Using JDBC PreparedStatement, When using the Microsoft JDBC Driver for SQL Server, some common problems that can occur are not having the right driver version or being  To update your JDBC drivers, complete the following steps: Determine the version of the JDBC drivers that are available for a specific level of your database product. For example, you can determine the version of JDBC drivers that are available for a specific level of DB2 by checking the topic DB2 JDBC driver versions and downloads.

Comments
  • I've never considered that, let me give it a spin
  • Still returning 0 for the update. getConn() returns the connection object created in the class constructor. No pooling is used.
  • Even though this isn't a direct answer, it pointed me to what I needed to investigate to find that answer. Thanks
  • I'd be concerned about SQL Injection as it would pass order_id in with no checking.
  • True. In my case, the identifier was validated on the controller, and then passed to business logic, so there was no problems. Unfortunately, I couldn't get a better approach, as the tokenized query always returned / updated zero results
  • Added a finding I made about this... Hope it helps