Why is my JDBC prepared statement update not updating the database?
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.
Ben Flynn
"I mean, James, the Internet is a place where absolutely nothing happens. You need to take advantage of that." - Strong Bad
Updated on June 05, 2022Comments
-
Ben Flynn almost 2 years
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?