Re: ODBC -> MSSQL transactions (HACK)

This is a multi-part message in MIME format.
--------------040504090703090009060002
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Whoops, forgot to attach the patch.

Nathan Bird wrote:
> I had some problems with transactions, here is a hack around that works
> for me. It is based on the fact that apparently mssql requires the
> "transaction" keyword in "commit transaction" when it is over odbc. 
> ODBC actually has a different scheme for transactions based on turning
> off autocommit, and then doing the commit manually with SQLEndTran or
> somesuch, that looked more difficult and with this all the tests pass. :-)
>
> clsql -> unixodbc -> freetds (0.63) -> mssql 2000
>
> Nathan Bird
>
> _______________________________________________
> CLSQL-Devel mailing list
> [email protected]
> http://lists.b9.com/mailman/listinfo/clsql-devel
>   


--------------040504090703090009060002
Content-Type: text/plain;
 name="mssql-transactions.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="mssql-transactions.patch"

Thu Jun  7 17:01:00 EDT 2007  Nathan Bird <[email protected]>
  * HACK for transactions on mssql over odbc
diff -rN -u old-clsql/sql/transaction.lisp new-clsql/sql/transaction.lisp
--- old-clsql/sql/transaction.lisp	2007-06-15 16:48:34.000000000 -0400
+++ new-clsql/sql/transaction.lisp	2007-06-15 16:48:34.000000000 -0400
@@ -54,11 +54,16 @@
         (:mssql (execute-command "BEGIN TRANSACTION" :database database))
         (t (execute-command "BEGIN" :database database))))))
 
+;;ODBC should potentially be using it's scheme for transactions:
+;; turn off autocommit for begin. then use sqlendtran  (or maybe sqltransact
+;; whatever is appropriate for this version of odbc.
 (defmethod database-commit-transaction ((database database))
   (with-slots (transaction transaction-level autocommit) database
     (if (plusp transaction-level)
         (when (zerop (decf transaction-level))
-	  (execute-command "COMMIT" :database database)
+	  (case (database-underlying-type database)
+	    (:mssql (execute-command "COMMIT TRANSACTION" :database database))
+	    (t (execute-command "COMMIT" :database database)))
 	  (setf autocommit (previous-autocommit transaction))
           (map nil #'funcall (commit-hooks transaction)))
         (warn 'sql-warning
@@ -71,7 +76,9 @@
     (if (plusp transaction-level)
         (when (zerop (decf transaction-level))
           (unwind-protect
-               (execute-command "ROLLBACK" :database database)
+	       (case (database-underlying-type database)
+		 (:mssql (execute-command "ROLLBACK TRANSACTION" :database database))
+		 (t (execute-command "ROLLBACK" :database database)))
 	    (setf autocommit (previous-autocommit transaction))
             (map nil #'funcall (rollback-hooks transaction))))
         (warn 'sql-warning


--------------040504090703090009060002
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
CLSQL-Devel mailing list
[email protected]
http://lists.b9.com/mailman/listinfo/clsql-devel

--------------040504090703090009060002--