FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

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.
[...]
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
 		 */
+		/*
+		 * alex AT ingrian.com: 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))