asked on

How do I connect Delphi Rio to remote SQL Server with ADO

Helo I am using Delpi Rio and the ADO componemts to connect to remote SQL server located in a data centre. It generally all connects and all works, however I have an issue if the connection gets interupted (as it does over the internet) I seem unable to reconnect. I have tried opening and closing the ADOconnection again but still does not work, invariable the program needs reloading. Which are the best events I can use in the ADO components to trap a disconnection and then how can I restore it. The ONDisconnect event does not work for this.

Ferruccio Accalai

Without speaking about FireDAC, as you're using Delphi Rio, your problem could be a connection timeout or internet lack or something else.
In any case should be better to catch any ado exception to understand what's going wrong.

A very first solution to keep the connection live is to use a TTimer (so any execution is threaded) with an interval let's say of 30000 (every 30 secs) with a very simple query call to any table
Something like

procedure TMainForm.Timer1Timer(Sender: TObject);
begin
  
  if not AdoConnection1.InTransaction then
    AdoConnection1.Execute('SELECT 1 FROM TABLE');
end;

Open in new window

cliveMM

ASKER

Thank you Ferruccio, I am trying that as we speak.
How would you recommend I catch and ADO exception, I have tried using Try/Except blocks and also the OnDisconnect event but never managed to catch one.

Ferruccio Accalai

For any exception you could simply handle them by code like

procedure TForm1.FormCreate(Sender: TObject);
begin
  Application.OnException := HandleException;
end;

procedure TForm1.HandleException(Sender : TObject;  E : Exception);
begin
  ShowMessage(Format('%s'+#13+'%s', [E.ClassName, E.Message]));
end;

Open in new window

cliveMM

ASKER

Hi Ferruccio,

I added the exception handler and that works, when i break the internet connection i get the message:-
EOleException - [DBNETLIB][ConnectionWrite(Send().]General Network Error. - Check your network documentation.
Then wnen the timer kicks in with the Select 1.. I get a similar error
EleException - Connection Failure

My Question is how do I recover from it, should I add a loop in the exception haldler when it gets one of those messges to go into a loop doing maybe:-
ADOConnection.Connected := False;
ADOConnection1.Connected := True

I have tried similar in past but I never got it to work, your advice would be much appriciated?

ASKER CERTIFIED SOLUTION

Ferruccio Accalai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cliveMM

ASKER

Thanks Ferruccio, that works well, I needed to also open all the TAbles/Query components as well and restore a few record positions but its working now. Thank you for helping me clarify my thoughts and working through this with me.

Ferruccio Accalai

Glad to have helped you