Fw: FreeTDS patch for MSSQL autocommit
"James K. Lowden" <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[email protected]> |
Hi Freddy, I received the attached AUTOCOMMIT patch by private mail. I don't know how/if it would be merged with CVS HEAD and/or 0.82. What do you think? --jkl Begin forwarded message: [...] I have tested the patch for over 6 months now in production environment and to the best of my knowledge it helps to fix the problem stated in the comment without breaking anything else. [...] _______________________________________________ FreeTDS mailing list [email protected] http://lists.ibiblio.org/mailman/listinfo/freetds
freetds-0.64-autocommit.patch
(text/x-patch, 2.5 KB)
diff -ruN freetds-0.64-orig/src/odbc/odbc.c freetds-0.64/src/odbc/odbc.c --- freetds-0.64-orig/src/odbc/odbc.c 2006-06-29 14:38:13.000000000 -0700 +++ freetds-0.64/src/odbc/odbc.c 2007-08-29 16:35:08.000000000 -0700 @@ -187,6 +187,51 @@ * mssql: SET IMPLICIT_TRANSACTION ON * sybase: SET CHAINED ON */ + /* + * [email protected]: on MS SQL you cannot get away with just one statement to change autocommit setting, + * as you have to commit all the outstanding transactions. The Sybase case won't work as expected for MS + * either, because the first commit after begin tran would return the session into the previous mode, + * so you HAVE to use IMPLICIT_TRANSACTIONS. Also to make a rollback behavior more consistent, it is + * recommended to have XACT_ABORT setting also ON. In this case the rollback would cause the entire + * transaction rolled back regardless of error severity, which otherwise may cause just one statement to be + * rolled back with the rest of the transaction being processed. + * + * To summarize actions needed for MS SQL: + * - autocommit OFF: + * SET IMPLICIT_TRANSACTIONS ON + * SET XACT_ABORT ON + * - autocommit ON: + * WHILE @@TRANCOUNT > 0 COMMIT + * SET IMPLICIT_TRANSACTIONS OFF + * (this also helps to address the issue with @@TRANCOUNT incremented twice after the first + * BEGIN TRAN in IMPLICIT_TRANSACTIONS mode). + */ + if (TDS_IS_MSSQL(tds)) { + if ((state == SQL_AUTOCOMMIT_ON)) { + strcpy(query, "WHILE @@TRANCOUNT > 0 COMMIT"); + tdsdump_log(TDS_DBG_INFO1, "change_autocommit: executing %s\n", query); + if (tds_submit_query(tds, query) != TDS_SUCCEED) { + odbc_errs_add(&dbc->errs, "HY000", "Could not change transaction status"); + ODBC_RETURN(dbc, SQL_ERROR); + } + if (tds_process_simple_query(tds) != TDS_SUCCEED) { + odbc_errs_add(&dbc->errs, "HY000", "Could not change transaction status"); + ODBC_RETURN(dbc, SQL_ERROR); + } + } + else { + strcpy(query, "SET XACT_ABORT ON"); + tdsdump_log(TDS_DBG_INFO1, "change_autocommit: executing %s\n", query); + if (tds_submit_query(tds, query) != TDS_SUCCEED) { + odbc_errs_add(&dbc->errs, "HY000", "Could not change transaction status"); + ODBC_RETURN(dbc, SQL_ERROR); + } + if (tds_process_simple_query(tds) != TDS_SUCCEED) { + odbc_errs_add(&dbc->errs, "HY000", "Could not change transaction status"); + ODBC_RETURN(dbc, SQL_ERROR); + } + } + } /* implicit transactions are on if autocommit is off :-| */ if (TDS_IS_MSSQL(tds))