update in mysql console does not show in application
Hi,
I have a question. I made a complete J2EE application on top of a mysql database. When I delete or insert or update a table in my application the changes show instantly. In my application I use PreparedStatement.
The problem is when I want to edit some data in the database using the mysql command window outside my application. Then the changes are not shown in my application untill I restart tomcat.
Does the data retrieved through JDBC get buffered or something?
Thanks
[509 byte] By [
jposthuma] at [2007-11-27 11:38:59]
# 1
I would say to ask at www.mysql.org, since they wrote both the DB and the JDBC Driver for it.
# 2
Hi,
I did. noboddy has answered so I figured I als ask here as well. Any help is welcome.
Thanks
# 3
Well, someone may answer here, but I doubt it, as it will have to with the MySQL DB, the MySQL tool you used to update the data, and the JDBC Driver (which is also from MySQL), as all involved JDK parts of JDBC are only interfaces.
I will ask one stupid question though, are you commiting the changes made in the other tool, and are you, in your code, not the JDBC itself, caching anything?
# 4
Hi,
This is the code in my application
public boolean removeTrackedItem(int itemId, String userName) throws SQLException {
boolean removed = false;
String query = "DELETE " +
"FROM trackitem " +
"WHERE itemid = ? " +
"ANDusername = ? ";
Connection connection = null;
try {
connection = dBConnectionManager.getConnection();
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(query);
ps.setInt(1, itemId);
ps.setString(2, userName);
int updatedRows = ps.executeUpdate();
if (updatedRows > 0) {
removed = true;
connection.commit();
}
} catch (SQLException e) {
connection.rollback();
System.err.println("Class: "+this.getClass().getName() + ", " + "Method: removeTrackedItem(int itemId, String userName)"+", Error: "+e);
} finally {
if (connection != null) {
dBConnectionManager.freeConnection(connection);
}
}
return removed;
}
When the above code gets executed the changes are visible right away in my app.
When I execute the query in the standard mysql command line tool, the changes are not visible. I have the standard installation of mysql 5.1 and I did not configure anything after the install.
Thanks
# 5
Hi,
I do not commit the changed in the command line tool. When I execute a SELECT query right away in the command line tool the changes do show.
In my code I do not cache anything.
Thanks
# 6
That doesn't say anything about the rest of your code though. And that code will not display anything (as it is obviously not meant to). Seeing as how you talked about tomcat, I assume this is a web project. After deleting the item with your MySQL tool, did you try simply reloading the page (another stupid question I know, but I gotta ask)? Also, you did not say whether you committed the change in the MySQL tool.
You are also aware of the fact that if the delete statement is successful, but nothing is deleted (i.e. there were no records with that id and name), that the connection is returned to the pool with a unresolved action, right? i.e. there will have a statement run, but no commit and no rollback. This probably (at least in this case) will not hurt anything, but it is not clean, and if the same sort of logic is used in other places, it may cause problems.
# 7
> Hi,
>
> I do not commit the changed in the command line tool.
> When I execute a SELECT query right away in the
> command line tool the changes do show.
>
> In my code I do not cache anything.
>
> Thanks
Well, of course they will show right away in the MySQL tool, as that is operating in the same session. But that does not mean that the change is visible from other sessions/connections.
# 8
I did reload the page but that did not do the trick.
# 9
So try committing the changes in the MySQL console. Get back to us when you've done that.
# 10
Yep thats it. Sorry for wasting your time but thanks for all the replies.
I have set autocommit to true in the command line tool and now the changes directly show.
I thought that since the changes directly got shown in the command line tool the changes were already committed and therefore the jdbc somehow cached the data.
Thanks
Jarno
# 11
> I thought that since the changes directly got shown
> in the command line tool the changes were already
> committed and therefore the jdbc somehow cached the
> data.
Not sure if this was just a mis-assumption about the autocommit mode, or if you're new to transactions. If the latter, I recommend reading up about them - they're important.
The Wikipedia article is pretty good: http://en.wikipedia.org/wiki/Database_transaction
