Ledion B wrote:
[snip]
> Well, I don't think that conforms with the JDBC
> specifications from SUN, that you can find here
>
http://java.sun.com/products/jdbc/download.html#co
> respec30
> Check out 13.1.2 here they use update count (which
> I think is number of rows that were updated), but
> the next one makes it clearer.
>
> Check out 13.2.4.2, it talks about prepared
> statements, but I think it is clear that
> executeUpdate needs to return the number of
> affected rows (updated rows)
Ledion,
I'm sorry, but I believe you are still wrong, because you are "parsing" what the spec says based on your MySQL knowledge. The JDBC specification is cross-vendor, which means that Connector/J has to have the same semantics (within reason) as all other vendors. This means that the count for "updated rows" is those rows that matched the filter condition and _would_have_been_updated! It does not mean the "mysql-ism" of only updating rows that actually need changing because the database engine has determined that indeed the row _values_ (not the filter condition) are different than what is being specified in the SET command. I'm pretty sure that other vendors such as Oracle also do the optimization to not re-write values that already are correct as per the UPDATE statement, as it reduces I/O.
Here's a quick example, that I've taken the liberty of running on both Oracle and Derby (which will have the same semantics as DB2), considering that both are JDBC-compliant, I'd like to assume that their behavior is authoritative:
new OracleDriver();
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
String url = "jdbc:oracle:thin:@//192.168.29.160:1521/orcl";
Connection conn = DriverManager.getConnection(url, "scott", "tiger");
System.out.println("Connected to " + url);
Statement stmt = conn.createStatement();
try {
// Not all DBs have DROP IF EXISTS
stmt.executeUpdate("DROP TABLE updTable");
} catch (SQLException sqlEx) {
// ignore
}
stmt.executeUpdate("CREATE TABLE updTable(id INT, locked INT)");
stmt.executeUpdate("INSERT INTO updTable VALUES (1, 1)");
// Please notice here that the table already _has_ the values 1,1 in it, and we're setting
// the values to 1 _twice_ in a row!
System.out.println("stmt.executeUpdate(\"UPDATE updTable SET locked=1 WHERE ID=1\") returns the value: " + stmt.executeUpdate("UPDATE updTable SET locked=1 WHERE ID=1"));
System.out.println("stmt.executeUpdate(\"UPDATE updTable SET locked=1 WHERE ID=1\") returns the value: " + stmt.executeUpdate("UPDATE updTable SET locked=1 WHERE ID=1"));
Here's the output from Oracle:
Connected to jdbc:oracle:thin:@//192.168.29.160:1521/orcl
stmt.executeUpdate("UPDATE updTable SET locked=1 WHERE ID=1") returns the value: 1
stmt.executeUpdate("UPDATE updTable SET locked=1 WHERE ID=1") returns the value: 1
Here's the output from Derby:
Connected to jdbc:derby:testUpdate;create=true
stmt.executeUpdate("UPDATE updTable SET locked=1 WHERE ID=1") returns the value: 1
stmt.executeUpdate("UPDATE updTable SET locked=1 WHERE ID=1") returns the value: 1
Regards,
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html