Ben Flynn Ben Flynn - 6 days ago 6
Java Question

Why is my JDBC prepared statement update not updating the database?

I am using JDBC to update a row in my MySQL database:

pConnection.setAutoCommit(true);

PreparedStatement pstmt = pConnection.prepareStatement("update mg_build_queue " + //
"set buildsetid=?,locale=?,areacode=?,distversionid=?,platformid=?,version=?," + //
"priority=?,buildstatus=?,computername=?,buildoutput=?,started=?,finished=?, packageid=?, lockcounter=0 where buildid=?" //
);

pstmt.setInt(1, mBuildSet.getId());
pstmt.setString(2, Locale.localesToString(mLocales, ","));
pstmt.setString(3, mAreaCode.toString());
pstmt.setInt(4, mDistVersionId);
pstmt.setInt(5, mPlatform);
pstmt.setInt(6, mVersion);
pstmt.setLong(7, mPriority);
pstmt.setInt(8, mBuildStatus);
pstmt.setString(9, mComputerName);
pstmt.setString(10, mBuildOutput);
pstmt.setTimestamp(11, timeToTimestamp(mStarted));
pstmt.setTimestamp(12, timeToTimestamp(mFinished));
pstmt.setInt(13, mResultPackageId);
pstmt.setInt(14, mBuildId);

LOGGER.debug("Updating data for mg_build_queue: " + pstmt);
pstmt.execute();
LOGGER.debug("Updated " + pstmt.getUpdateCount() + " rows.");


This is generating the following output:

2012-05-24 09:54:33,211 [Thread-1] DEBUG com.buildmaster.BuildQueueEntryImpl - Updating data for mg_build_queue: com.mysql.jdbc.JDBC4PreparedStatement@35e09eab: update mg_build_queue set buildsetid=201,locale='FR',areacode='XX',distversionid=95,platformid=4604,version=65807,priority=33652480,buildstatus=1,computername='MY_COMPUTER-C:\\BUILDS',buildoutput='',started='2012-05-24 09:54:33',finished='2012-05-24 19:45:27', packageid=0, lockcounter=0 where buildid=122418
2012-05-24 09:54:33,214 [Thread-1] DEBUG com.buildmaster.BuildQueueEntryImpl - Updated 1 rows.


I see no exception. If I query for the entry in DBVisualizer, I see only the old value. If I run the command by hand in DBVisualizer (copied and pasted from above), I can see the updated value.

Why is this happening?

Answer

The issue appears to be that DBVisualizer is doing some result caching. I disconnected and reconnected using DBVisualizer and can see the update. Thanks to all for their suggestions.

Thanks to a Hans Bergsten on the DBVisualizer forums, here is how to prevent my issue:

Tools -> Tool Properties -> Database -> MySQL -> Physical Connection

Change Transaction Isolation to TRANSACTION_READ_COMMITTED.

NOTE I also restarted my MySQL database, which I don't think affected things, but is worth mentioning.

Comments