[freetds] Implicit Transactions Problem Revealed

Federico Alves sales at minixel.com
Sun May 11 01:07:26 EDT 2008


Implicit Transactions Problem Debugged
Freetds changes the "autocommit" behavior according to one fact: if it
thinks is connecting to Sybase, then it turns "implicit transactions" ON,
because Sybase apparently requires such thing. If it thinks that it is
connecting to MS SQL Server, then it turns "implicit transactions" OFF. How
does it know one case from another? It looks whether the freetds.conf has an
entry with the name of the server and "tds version = 8.0". If this is true,
then it must be Microsoft SQL Server, and the implicit transactions are off.
I know because I proved it, using only Tsql. I connected once without the
"tds version = 8.0" and the SQL debugger showed "implicit transactions ON".
I disconnected, put back the option in freetds.conf and reconnected,
straight from tsql, no odbc, and there it was, "implicit transactions off".
Therefore, being that I can reproduce it at will, I offered Frediano Ziglio
to come into my machine and see with his own eyes, but I still think that
anybody with access to SQL server and the profiler tools can reproduce it.
In fact, Microsoft showed me the problem after many hours of research in my
SQL server. I spent $3000 in support fees to come to this knowledge. I chose
to remove that "tds version = 8.0" in my freetds.conf for one valid reason.
If it is there, any perl script that uses Stored Procedure parameters
(in-out), does not work. For example, this perl code stops working if the
option "tds version = 8.0" is included:
do {
      while(my $dat = $sth->fetch) {
print "TYPE $sth->{syb_result_type}\n";
print "Data @$dat[0] , @$dat[1] , @$dat[2] \n";
         if($sth->{syb_result_type} == 4042) { # it's a PARAM result
           print "Number:  $dat->[0]  \n";
            print "Varpar:  $dat->[1]  \n";
         }
      }
   } while($sth->{syb_more_results});

Any parameters syb_result_type=4042 are ignored if "tds version = 8.0", and
that is wrong because SQL 2005 actually must be using the same
"syb_type_number", since it works fine. I think that we need to address this
and have it both ways: parameter passing and "tds version = 8.0" I offer to
show this to anybody who wants to come into my box. I will start the
debugger on the right side of the screen, and connect using Tsql on the left
side. You will see what happens immediately. It is not the server, or the
app, it is freedts who chooses. But that is fine, because we can fix it.










More information about the FreeTDS mailing list