FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> 
> Hi,
>       I got problems when I was trying to do SQLExecute and 
> the connection
> to server is down. The error log looks like:
>       [FreeTDS][SQL Server]Write to SQL server failed. 08S01
>       
>       I am trying to reproduce problem by disconnecting the 
> cable on the
> SQL server, but I can only reproduce following error
>       [FreeTDS][SQL Server]Read from SQL server failed. 08S01
> 
>       My code is somewhat like this:
> --------------------------------------------------------------
> --------------
>       Conn = SQLAllocHandle(...); // Allocate connection 
> handle, at this
> time the connection is ok
>       
>       Stmt = SQLAllocHandle(...); // Allocate statement 
> handle, at this
> time the connection is ok
> 
>       // Use the stmt to do some querying, also ok.
> 
>       // Now I disconnect the cable
> 
> /* The latest FreeTDS asks us to totally close a statement 
> handle before we
> fetch another resultset */
> #ifdef USE_FREETDS
>       if (m_hstmt != NULL)
>       {
>               SQLFreeStmt(Stmt, SQL_CLOSE);
>               SQLFreeStmt(Stmt, SQL_UNBIND);
>               SQLFreeStmt(Stmt, SQL_RESET_PARAMS);
>       }
> #endif
> 
>       /* Maybe the cursor is not closed yet, anyhow we close it */
>       iRet = SQLCloseCursor(Stmt);    
> 
>       /* Set the CommandTimeOut property */
>       iRet = SQLSetStmtAttr (Stmt, SQL_ATTR_QUERY_TIMEOUT, ...);
>                       
>       /* Bind all Paramters */
>       // code removed ...
> 
>       SQLPrepare (Stmt, (UCHAR *) SQLText, SQL_NTS);
>       SQLExecute (Stmt);
> --------------------------------------------------------------
> --------------
> 
>       Now no matter what error I got, if I tried to close the 
> statement
> handle by SQLCloseHandle(Stmt) I always got a 
>       [FreeTDS][SQL Server]Attempt to initiate a new SQL 
> Server operation
> with results pending. 07005
> 
>       My questions are as follows:
>       1. Why I can't close the statement handle
>       2. How can I reproduce the " Write to SQL server failed". (This
> error always happens when nobody is in the room) One thing 
> may help is that
> the "Write to SQL server failed" error appears immediately 
> after I call the
> SQLExecute, but if I disconnected the cable it will took 8 
> minutes for the
> "Read from SQL server failed". (The poll() call on Solaris 
> should fail after
> 4 minutes 30 seconds, am I wrong?)
>       3. The problem may lead to freeze of our application. 
> The code above
> is shared among 3 threads each having their own data. From 
> the log we found
> that when the connection keeps up and down, the application 
> sometimes will
> totally freeze. The thing I can't understand is that we have 
> more than 15
> threads in our application and most of them have nothing to 
> do with database
> thread (even not sharing a single variable) and they all 
> seemed freezed. But
> it's very difficult to reproduce. I found in the old email 
> somebody mention
> something similar on May/22/2004:
> ------------------------------------------------
> Hi,
>  
>   I am writing a program to copy data from 10 MSSQL server  
> to one central
> DB2 server using unixODBC 2.2.8, FreeTDS 0.62.1 and DB2 8.1.5 
> ODBC under
> RHEL AS 3.0
>  
>   The program is multi threaded and each thread connect to 
> certain MSSQL
> server, it works fine until one of the sever is unreachable ( 
> i cannot ping
> it from Linux command line) at this point i get an error from 
> FreeTDS :
>    server 192.122.122.12   connect time out
>  
>   at this point the program freeze and all threads freeze totally
>  
>   any ideas ?
> 

It seems a problem related to state of socket. In write.c we have a code
like

                if (retval <= 0) {
                        tdsdump_log(TDS_DBG_NETWORK, "TDS: Write failed
in tds_write_packet\nError: %d (%s)\n", sock_errno,
strerror(sock_errno));
                        tds_client_msg(tds->tds_ctx, tds, 20006, 9, 0,
0, "Write to SQL Server failed.");
                        tds->in_pos = 0;
                        tds->in_len = 0;
                        tds_close_socket(tds);
                        return TDS_FAIL;
                }

Change in 

                if (retval <= 0) {
                        tdsdump_log(TDS_DBG_NETWORK, "TDS: Write failed
in tds_write_packet\nError: %d (%s)\n", sock_errno,
strerror(sock_errno));
                        tds_client_msg(tds->tds_ctx, tds, 20006, 9, 0,
0, "Write to SQL Server failed.");
                        tds->state = TDS_DEAD;
                        tds->in_pos = 0;
                        tds->in_len = 0;
                        tds_close_socket(tds);
                        return TDS_FAIL;
                }

this should solve your problem. I didn't try to reproduce it. 8 minutes
should be the time to detect disconnection (by keep-alive flag). I don't
understand why you are using 0.62.1 instead of 0.62.4. We closed 0.62
trunk, we are 3 developer so it's very hard to update 3 versions (0.62,
0.63 and 0.64). Give a try to 0.63. If you don't like the idea of using
a too young version use 0.62 version from CVS (BRANCH0_62) it contains
0.62.4 with sligth fixes. If you want I can send (privately) a post
0.62.4 patch.

freddy77