Chris Waterson wrote:
You probably already know this, but... you can mix tables from different storage engines in a single *statement*.
Oh, no, I thought it was per database. Horrors!
...when a ROLLBACK has been issued against a non-transactional table... I could raise an error... perhaps this is preferable?
John Goerzen wrote:
...what happens in the ODBC case depends on what the user puts in odbc.ini... either the full-on error treatment, or the ignoring the problem
It does make sense for this to be configurable. In addition, you might want even more than the current "full-on error" mode: As Chris pointed out, throwing an exception at commit or rollback is a bit late - your data may already be destroyed by then. There should be a "safe" mode that raises an exception any time you try make a change to a non-transactional table, even before commit or rollback. That way, you would be able to write code like this: Start doing A in a new transaction. If any operation tries to make a change in a non-transactional table, roll back A and do B instead. Transactional "safe" mode could still allow reads of non-transactional tables. Is this possible for MySQL and/or ODBC?
If you feel that the HDBC API needs to become more rich, I'm quite happy to entertain suggestions there
Well, this makes a major difference in the meaning of a program. So it does seem that it should be possible at least to detect what is happening, or even control it programatically. In the case of a connection with less than guaranteed full support for transactions, you would want to be able to choose between the two behaviors available in the ODBC driver, or "safe" mode. Right now, the meaning of the dbTransactionSupport flag is ambiguous. Does True mean that full support for transactions is guaranteed? Or only that it might be supported for some tables? If not supported for some table, will the driver revert to irrevocable immediate changes, or raise an exception? Will silently ignore commit and rollback requests, or raise an exception? Even before the API becomes richer in this respect, the documentation should more clearly indicate the meaning of this flag (and drivers should then comply). Thanks, Yitz