Delphi uses ADO to connect network database, and then reconnect the problem after network disconnection

Original article: 11737699#

Using the Tadoconnection object to connect to a network database (for example, MySQL), when the local network is disconnected, the connected property of the connection object does not change and is always true.

Even if the Keepconnection property of the Connection object is set to False, if the network is disconnected, the connected property is true if you do not attempt to connect to the database, and the connected property becomes false after the attempt to connect to the database fails.

var
   ADOCon: TADOConnection;
begin
   // to do create an ADOCon object and assign a connection string to ADOCon, code omitted
 
   ADOCon.KeepConnection: = false;
   // The network connection is normal at this time
   ADOCon.Open; // or ADOCon.Connected: = true;
   ADOCon.Connected; // The value of this property is true
   // Disconnect the network connection
   ADOCon.Connected; // The value of this property is true
   ADOCon.Open; // At this time, the connection is abnormal
   ADOCon.Connected; // The value of this property is false
end; 

Therefore, you cannot use the connected property in your code to determine whether an ADO object is connected properly, and if you use this connection object to close and open the connection directly after querying the database exception, you cannot successfully connect, you must reopen the running program, or recreate the connection object.

In response to this situation, the general solution on the network is to add a timer timer, create a new test connection object timed to connect to the database, if the connection fails to close the connection object and reconnect, in order to realize the automatic reconnection after the disconnection, the operation of the database after the network does not appear the phenomenon of card.

procedure TForm1.tmr1Timer (Sender: TObject);
   function TestConnected: Boolean;
   var
     aCon: TADOConnection;
   begin
     Result: = False;
     aCon: = TADOConnection.Create (nil); // Create temporary object
     try
       aCon.ConnectionString: = const_DBCON_MYSQL; // connection string
       try
         aCon.Connected: = True;
         Result: = True;
       except
         Exit;
       end;
     finally
       aCon.Free;
     end;
   end;
begin
   if not TestConnected then // Use a temporary object to determine the database connection status
   begin
     FADOCon.Close; // Operate the connection object according to the connection status of the temporary object. If it cannot connect, close the connection object. If the connection is normal, open the connection object.
   end
   else
   begin
     if FADOCon.Connected then
       FADOCon.Connected: = True;
   end;
end; 

However, if the network or database hangs the situation is very small, and the query data after the network has no special requirements, then you can close the connection object after each operation of the database, so that even if the network is disconnected, the connection is also closed, the query database exception will not affect the network after the database operation recovery.

procedure TForm1.btn3Click (Sender: TObject);
begin
   try
     FADOQuery.Close;
     FADOQuery.Connection: = FADOCon;
     FADOQuery.SQL.Text: = ‘select * from fp_kpxx’;
     FADOQuery.Open; // When the connection object is closed, it will automatically connect when querying
     ShowInfoDlg (IntToStr (FADOQuery.RecordCount));
     FADOCon.Close;
     // Disconnect the network at this time
      FADOQuery.Open; // Exception occurred at this time
      FADOCon.Close;
     // Restore the network at this time
      FADOQuery.Open; // At this time the query is normal
      FADOCon.Close;
   except
     ShowInfoDlg (‘Query failed’);
   end;
end; 

Connection object Close after the network is not disconnected, and then use the Tadoquery object to manipulate the database without reconnecting the database, there is no time delay, if the connection object close after the network disconnects, and then use the query object to manipulate the database, the database will be reconnected, there is some data delay. Therefore, although the use of this solution can be re-connected after the network, but the first database operation after the disconnection phenomenon.

Delphi uses ADO to connect network database, and then reconnect the problem after network disconnection