FreeTDS Development Group

[Peter Giorgilli <pgiorgilli AT theage.fairfax.com.au>]

Hi,

I recently installed FreeTDS (0.61.2) in combo with unixODBC (2.2.6), Perl 
DBI 
and Perl DBD::ODBC for the same reason that many of you may have. Namely, to 
access an Microsoft SQL database from Linux; in this case, from a Perl 
application.

The application involves a daemon updating the database periodically for 
which 
I decided to keep the database connection open for as long as possible to 
avoid the overhead of opening/closing connections for each transaction. The 
problem is is that there's a firewall in between the client and server which 
likes to drop idle connections after a pre-defined period.

What I started out to do was to catch these situations by "alarming" the call 
in the application to the SQL execute such that the application gracefully 
closes the connection and issues a rollback etc. if the execute does not 
return within 10 secs, for example.

That worked OK, except that the process would enter a loop some of the times. 
After working my way through debug logs, and with one eye on the source, I 
came to "odbc.c". Basically, what was happening was that the "_SQLExecute" 
function was not trapping the "TDS_FAIL" return code (generated as a result 
of the connection dying) and returning "SQL_SUCCESS" by default. The 
"SQLMoreResults" was in turn being called, when strictly it shouldn't have 
been, the end result being that the process would end up in a tight loop. 
See excerpt from sample log below.

2003-10-14 11:49:03.142324 processing result tokens.  marker is  0()
2003-10-14 11:49:03.142370 inside tds_process_default_tokens() marker is 0()
2003-10-14 11:49:03.142393 leaving tds_process_default_tokens() connection 
dead
SQLExecute: bad results
2003-10-14 11:49:03.142837 processing result tokens.  marker is  0()
2003-10-14 11:49:03.142867 inside tds_process_default_tokens() marker is 0()
2003-10-14 11:49:03.142890 leaving tds_process_default_tokens() connection 
dead
2003-10-14 11:49:03.142912 processing result tokens.  marker is  0()
2003-10-14 11:49:03.142934 inside tds_process_default_tokens() marker is 0()
2003-10-14 11:49:03.142956 leaving tds_process_default_tokens() connection 
dead
2003-10-14 11:49:03.142978 processing result tokens.  marker is  0()
2003-10-14 11:49:03.143000 inside tds_process_default_tokens() marker is 0()
2003-10-14 11:49:03.143022 leaving tds_process_default_tokens() 
connection2003-10-14 11:49:03.142324 processing result tokens.  marker is  0()

After all of that, the patch below seems to have done the trick.

Thanks to everyone for making FreeTDS what it is.

Peter

*** odbc.c      Sun Aug  3 01:20:01 2003
--- /home/peterg/aoldev/src/odbc.c      Wed Oct 15 15:41:51 2003
***************
*** 274,281 ****
--- 274,283 ----
        for (;;) {
                switch (tds_process_result_tokens(tds, &result_type)) {
                case TDS_NO_MORE_RESULTS:
                        return SQL_NO_DATA_FOUND;
+               case TDS_FAIL:
+                       return SQL_ERROR;
                case TDS_SUCCEED:
                        switch (result_type) {
                        case TDS_COMPUTE_RESULT:
                        case TDS_ROW_RESULT:
***************
*** 1040,1048 ****
                        return SQL_SUCCESS_WITH_INFO;
                return result;
        } else {
                tdsdump_log(TDS_DBG_INFO1, "SQLExecute: bad results\n");
!               return result;
        }
  }
  
  SQLRETURN SQL_API
--- 1042,1051 ----
                        return SQL_SUCCESS_WITH_INFO;
                return result;
        } else {
                tdsdump_log(TDS_DBG_INFO1, "SQLExecute: bad results\n");
!               /* whatever's brought us 'ere, treat as an error */
!               return SQL_ERROR;
        }
  }
  
  SQLRETURN SQL_API



*********************************************************************************
The information contained in this e-mail message and any accompanying files 
is or may be confidential.  If you are not the intended recipient, any use, 
dissemination, reliance, forwarding, printing or copying of this e-mail or 
any attached files is unauthorised. This e-mail is subject to copyright. No 
part of it should be reproduced, adapted or communicated without the written 
consent of the copyright owner. If you have received this e-mail in error, 
please advise the sender immediately by return e-mail, or telephone and 
delete all copies. Fairfax does not guarantee the accuracy or completeness of 
any information contained in this e-mail or attached files. Internet 
communications are not secure, therefore Fairfax does not accept legal 
responsibility for the contents of this message or attached files.
*********************************************************************************