[Monetdb-developers] Auto-commit
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?
Fabian wrote:
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?
In ODBC functions can generally return SQL_SUCCESS,
SQL_SUCCESS_WITH_INFO, or SQL_ERROR. In the latter two cases one or
more messages can be retrieved with SQLGetDiagRec. I don't know
off-hand what *should* happen in the case you describe, but what
certainly *could* happen (but this is not based on intimate knowledge of
the description of SQLExecute) is that the SQLExecute call returns
SQL_SUCCESS_WITH_INFO.
--
Sjoerd Mullender
Sjoerd Mullender wrote:
In ODBC functions can generally return SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, or SQL_ERROR. In the latter two cases one or more messages can be retrieved with SQLGetDiagRec. I don't know off-hand what *should* happen in the case you describe, but what certainly *could* happen (but this is not based on intimate knowledge of the description of SQLExecute) is that the SQLExecute call returns SQL_SUCCESS_WITH_INFO.
Hmm, PostgreSQL does like I suggested, the update is not committed, because the select throws an exception. My guess is that SQL_SUCCESS_WITH_INFO is the equivalent of warnings produced during the setup/execution (like changed cursors, etc). Does ODBC know about warnings for Statements, ResultSets and Connections? Need sync with Niels on the standard I think.
Fabian wrote:
Sjoerd Mullender wrote:
In ODBC functions can generally return SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, or SQL_ERROR. In the latter two cases one or more messages can be retrieved with SQLGetDiagRec. I don't know off-hand what *should* happen in the case you describe, but what certainly *could* happen (but this is not based on intimate knowledge of the description of SQLExecute) is that the SQLExecute call returns SQL_SUCCESS_WITH_INFO.
Hmm, PostgreSQL does like I suggested, the update is not committed, because the select throws an exception.
My guess is that SQL_SUCCESS_WITH_INFO is the equivalent of warnings produced during the setup/execution (like changed cursors, etc). Does ODBC know about warnings for Statements, ResultSets and Connections?
I don't have the spec available at the moment, so I can't tell you what it says. I know with every function there is a long list of possible error and warning conditions. I don't know whether this particular one is covered.
Need sync with Niels on the standard I think.
As far as Niels is concerned, My guess is he'll view it as two SQL
statements, which is exactly what they are.
--
Sjoerd Mullender
participants (2)
-
Fabian -
Sjoerd Mullender