Geek Answers Handbook

How to resume / redo broken TADOConnection through application?

I have a data module with global TADOConnection ( KeepConnection set to true by default). My existing application has many datasets and queries that use this global TADOConnection .

I was wondering if there is any smart way to resume / retry ado connection in case of a short network outage? (this situation sometimes happens with clients who have not very stable connections).

Easy to reproduce what I need. just open TADOConnection at startup. open several TADODataSet and then disconnect and enable "Local Area Connection". if you try to update the dataset, an EOleException

"Connection error"

or

"[DBNETLIB] [ConnectionWrite (send ()).] General network error. Of your network documentation"

If I restart the application, everything will be fine.

During a network outage, TADOConnection events are not triggered. and TADOConnection.Connected remains true

Of course, I could use try / catch for each TDataSet.Open or Execute , but I am looking for some “centralized” solution for my large application. therefore, in the event of a “connection failure”, I could know which dataset is trying to open and try again.

+5
source share
2 answers

Never shoot during network outages. But you can check the connection before each command. Therefore, if AdoConnection is disabled, you can reconnect and run your command after that.

If you want a centralized solution and you have 1 Connection, you can do it like this:

 Const ConnectionTestString=' '; //Yes, it work on Sql Server. If doesnt your db, you can use 'Select 1'

Procedures;

 Procedure TDM.GetCommandResult_Conn(CText:String;Connection : TAdoConnection); var Ado_Ds_Tmp:TAdoCommand; Begin Ado_Ds_Tmp:=TAdoCommand.Create(self); try Ado_Ds_Tmp.Connection:=Connection; Ado_Ds_Tmp.ParamCheck := False; Ado_Ds_Tmp.CommandText:=CText; try Ado_Ds_Tmp.Execute; except DM.RaiseExceptionCreate('Error ! Command, ('+StrToList(CText, ' ')[0]+')'); end; finally Ado_Ds_Tmp.Destroy; end; end; procedure TDM.ADOConnection1WillExecute(Connection: TADOConnection; var CommandText: WideString; var CursorType: TCursorType; var LockType: TADOLockType; var CommandType: TCommandType; var ExecuteOptions: TExecuteOptions; var EventStatus: TEventStatus; const Command: _Command; const Recordset: _Recordset); var ErrorLogFileName : string; ErrorFile : TextFile; ErrorData : string; Msg : String; begin try if (CommandText<>ConnectionTestString) then begin DM.GetCommandResult_Conn(ConnectionTestString, Connection); end; except try try Connection.Connected := False; except end; try Connection.ConnectionString := AdoConnectionString; Connection.Mode:=cmShareDenyNone; finally try Connection.Connected := True; // If you wanna log for frequency ErrorLogFileName := ChangeFileExt(Application.ExeName,'.error.log'); AssignFile(ErrorFile, ErrorLogFileName); if FileExists(ErrorLogFileName) then Append(ErrorFile) else Rewrite(ErrorFile); try ErrorData := Format('%s : %s : %s (%s / %s)',[DateTimeToStr(Now), 'Disconnected but we reconnect.', '', 'UserName : '+DBUser, 'Client : '+GetComputerNetName]); WriteLn(ErrorFile,ErrorData); finally CloseFile(ErrorFile) end; except DM.RaiseExceptionCreate('ReConnection Failed!'); end; end; except end; end; end;

Any question?

+1
source

The idea might be to catch a connection error, and then manage it with attempts. Sentence:

This function returns a description of the exception.

 function GetStrException(ExceptObject: TObject; ExceptAddr: Pointer):String; var Buffer: array[0..1023] of Char; begin ExceptionErrorMessage(ExceptObject, ExceptAddr, Buffer, SizeOf(Buffer)); Result:=Buffer; end;

This is a simple idea to test!

 procedure TForm1.Button2Click(Sender: TObject); var s,error:String; begin //a select as an example S := 'SELECT COUNT(*) FROM MyTable'; TRY WITH ADOQuery1 DO BEGIN SQL.Clear; SQL.Add(s);OPEN;END; Memo1.Lines.ADD(ADOQuery1.Fields[0].AsString); EXCEPT error:=(GetStrException(ExceptObject,ExceptAddr)); //using MySql my error case is when the connection is lost, so the error is "Server has gone away" if pos(error,'has gone away')>0 then begin Showmessage('Connection Error, please try again'); try sleep(1000) AdoConnection1.close; AdoConnection1.open; except Showmessage('The connection Error persists, please contact the support'); end; end; END; end;

A good solution is to centralize the insert / update and select procedures, then catch the error and try to adjust the situation once or twice before showing the message to the user

0
source

More articles:


All Articles