Oracle UPDATE command with multiple conditions in Java

Question

Hello

I'm having issues with the Java code below. It is supposed to update certain records in a table where the ID is given, and where the STATUS column is 'good' (this is only one row at any given time). However, when I run the code below, it seems to be ignoring the AND STATUS = 'good' part, and updating all NUMRECS wherever the ID matches.

static void insertNumRecs()
    {
        PreparedStatement insert = null;
        try
        {
            String insertNumRecsCommand = "UPDATE FILESTATUS SET NUMRECS = ? " +
                    "WHERE ID = ? AND STATUS = 'good'";
            insert = Main.con.prepareStatement(insertNumRecsCommand);
            insert.setInt(1, Main.numRecs);
            insert.setString(2, Main.docID);
            insert.executeUpdate();
        }
        catch (Exception ex) {ex.printStackTrace();}
        finally {close(null, insert);}
    }

I've tried searching for this everywhere, but I couldn't find any answers. When I run the command directly from the database, it works fine, which confuses me even more.

Thanks in advance.

Answer 1

Try to write

"WHERE ID = ? AND STATUS = ?"

and use

insert.setString(3, "good");

Answer 2

This doesn't explain the problem, but I'd wonder why you don't do this:

static void insertNumRecs() 
    { 
        PreparedStatement insert = null; 
        try 
        { 
            String insertNumRecsCommand = "UPDATE FILESTATUS SET NUMRECS = ? " + 
                    "WHERE ID = ? AND STATUS = ?"; 
            insert = Main.con.prepareStatement(insertNumRecsCommand); 
            insert.setInt(1, Main.numRecs); 
            insert.setString(2, Main.docID); 
            insert.setString(3, "good");
            insert.executeUpdate(); 
        } 
        catch (Exception ex) {ex.printStackTrace();} 
        finally {close(null, insert);} 
    } 

Can't see your data, so I can't tell if it's a case issue ("GOOD" != "good")

Sure you're connecting to the database you think you are? If the connection string points to one database, and you run your test against another, that would explain why you don't see the change.