title: Auto-Recover when DBNETLIB ConnectionWrite General network error causes ADO connections to go offline in Delphi applications?
body: <p>Googling this ADO error message indicates that it is commonly encountered in ASP.NET development, but I have not found much mention of when it occurs in Delphi applications. We have some customer sites which are experiencing transient network problems, and this is the symptomatic error message. We can duplicate it in office testing easily; Just shut down an MS SQL Server service while your delphi TADOConnection object is connected to a database on that server instance and you get this exception:</p>
  
  <pre><code>   [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation.
  </code></pre>
  
  <p>Yes, catch this exception, and you know (or do you?) that this error has occurred. Except that this is an 800 KLOC+ application with over 10,000 try-except blocks around database actions, any one of which might fail with this error.  </p>
  
  <p><code>TADOConnection</code> has some error events, none of which fire in this case.  However, the ADO Connection itself is faulted once this occurs, even if you restart the SQL database, TADOConnection.Connected remains true, but it's lying to you. It's really in a faulted state.</p>
  
  <p><strong>So then, my question is:</strong></p>
  
  <p><em>Can you detect this faulted state, and recover from it, in any way that is less work than going into 10,000 individual try-except blocks and setting some global "reconnect ADO global variable"?</em></p>
  
  <p>I am hoping there is a way to go into TADOConnection.ConnectionObject (the underlying raw OLEDB COM ADO object) and detect this fault condition exists when we are starting a new query, so that we can reset the ADOConnection and continue the next time we run a query.  Since our code is organized in a way that would allow us to detect this "after the failure" much more easily than it would allow us to do it the way I would do this in a 10 line demo application.</p>
  
  <p><a href="https://stackoverflow.com/questions/2784391/sql-server-2005-and-general-network-error">This other SO question</a> asks why it happens,  that is <strong>not</strong> what I'm asking, please don't give me "prevention" answers, I know about them already, I'm looking for a recovery and detection-of-stalled-ADO-connection technique other than catching the exceptions. In fact, this is a good example of exceptions gone wrong;  ADO is a schrodingers-cat object in this failure mode.</p>
  
  <p>I am aware of the MS Knowledgebase articles, and the various solutions floating around the internet. I'm asking about RECOVERING without losing customer data, once the error condition (which is often transient in our situations) has cleared.  That means we freeze our app, show the exception to the customer, and when the customer clicks Retry or Continue, we attempt to repair and continue. note that our existing code does a million try-except-log-and-continue code, that is going to get in our way, so I'm expecting someone to answer that an Application handler for unhandled exceptions is the best way, but sadly we can't use it. I really hope however that it is possible to detect a frozen/faulted/dead ADO connection object.</p>
  
  <p>Here's what I have:</p>
  
  <pre><code>try
    if fQueryEnable and ADOConnection1.Connected then begin
      qQueryTest1.Active := false;
      qQueryTest1.Active := true;
      Inc(FQryCounter);
      Label2.Caption := IntToStr(qQueryTest1.RecordCount)+' records';
  
    end;
  except
        on E:Exception do begin
           fQueryEnable := false;
           Memo1.Lines.Add(E.ClassName+' '+E.Message);
           if E is EOleException and Pos('DBNETLIB',E.Message)&gt;0 then begin
              ADOConnectionFaulted := boolean; { Global variable. }
           end;
           raise;
        end;
  end;
  </code></pre>
  
  <p>The problem with the above solution is that I need to copy and paste it about 10,000 places in my application.</p>