Jeez, yet another wonky mssql behavior switch? That things got more flags than the U.N.
I believe that the MSSQL ODBC driver on Windows automatically sets IMPLICIT_TRANSACTION off on connect, whereas FreeTDS likely does not, which is perhaps the source of the problem. Here's what I think the problem might be: - The code the OP posted has no initial .begin(), so the program starts with a random IMPLICIT_TRANSACTION state depending upon ODBC driver + server settings. - For FreeTDS and his server, let's assume IMPLICIT_TRANSACTION is ON - The session.execute() then issues a BEGIN (since the session is not in autocommit mode) - If the server started in IMPLICIT_TRANSACTION mode, the @@TRANCOUNT would now be TWO, not one (one implicit, one explicit) - The next .commit() closes the second tx, now it's @@TRANCOUNT == 1 - Another BEGIN and SELECT finds the inserted data, and returns it (hence the rowcount==1) - Another explicit COMMIT, @@TRANCOUNT again goes from 2 => 1 - Program ends (with a rollback of the starting implicit transaction) - Data is now gone It seems to me that the only time you'd want IMPLICIT_TRANSACTION on is in autocommit mode, where savepoints don't make any sense anyway. Otherwise, since SQLA defaults to explicit transaction semantics everywhere, you'd want IMPLICIT_TRANSACTION turned OFF on initial connect and left off for the duration of the connection. Is there a hook to execute setup SQL on connection establishment, or when a session claims a connection? If so, those might be the places to set that damned flag OFF. It could be tricky getting session autocommit vs. non-session SQL to work right on shared connections. I would even be +1 on disallowing autocommit mode on MSSQL, since it complicates the connection setup vs. session connection claim logic so much. Rick --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
