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?
