Consider the following multi query statement:
(1) update x set somecol = 'newval'; select * form x;
(mind the explicit typo in the second query (from))
Now consider this whole statement being sent at once with auto_commit disabled. The second query will fail due to a syntax error and the whole connection will have to be rollbacked. If the error would be reported, and the --- theoretical --- update count would be ignored, there would be no problem at all, I think.
However, suppose auto_commit is switched on. Now the scenario brings some problems: 1. the first update is executed, and committed; thus an update count should be returned. 2. the second query fails and generates an error, however this error can't be displayed if the update count is displayed, the statement executes or fails, not partial.
As it looks now, using JDBC, a statement has a mutually exclusive result; either success, or failure. The current implementation of auto_commit, commits after each query, I think it should commit after each statement, so the statement as in (1) gets rollbacked as a whole and results in an error, instead of in a result and an error.
Is this a reasonable thought of mine, or does it clash with other things? How does ODBC handle this situation?