[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1567
  • Last Modified:

Reconnect to database whenever a connection can be made


This question is an addition to:

I'm using a SQL Server 2008 database (windows authentification). The connectionstring is extracted from a UDL-file.
Currently my application makes a connection to the database at startup. If the connection is lost during program execution my users get a connectionerror.

I need a way to open my Delphi application even without any available connection to the SQL Server database.
Whenever a connection can be made (over time), it should automatically (re-)connect to the database.
Also when the connection is lost during program execution, it will try reconnecting again.
If no connection can be made the user will be informed (showing some connect panel) and then has the option to either wait for a valid connection or close the application.

Any queries/stored procedures used in the application that are executed/opened will need to handle connectionloss and basically wait for the connection to become available again or when the user closes the application the query/stored procedures will be canceled.

What I'm looking for is a working concept, preferably a small sample application, that handles the above.
I prefer to use standard (maybe enhanced) Delphi ADO-components as my current application is using these a lot :-)
Stef Merlijn
Stef Merlijn
  • 3
  • 2
  • 2
  • +2
4 Solutions
Just before running EVERY query you should check if your DB in open state (Active=true) to avoid errors and make the connection dialog....
Martyn SpencerConsultantCommented:
Before going down the route of answering directly, I would like to offer some help along a slightly different track.

You say in your previous question and this question that people are suffering from "temporary" disconnections from the database. I am no SQL server expert but from my experience with Oracle databases, there is really no such thing as a "temporary" disconnection. If your client is disconnected from the SQL database for some reason there is a chance that your database session will be destroyed by the server and any resources that it has locked will be freed and open transactions rolled back.

For this reason, offering a totally transparent way of reconnecting to the database would be quite a long-winded exercise, in my mind. To illustrate my point, consider the following:

Transaction started.
Data element A is locked (assuming you are not using optimistic integrity controls)
Data element A is changed in the app.
Data element A is updated
Transaction committed.

If your client loses its connection at any point after the transaction started but before it is committed, there is a chance that the database could roll back the entire transaction and to re-establish state in your application you would need to know at what point in the above sequence you were located and what the old data values were and ensure that no one else has locked or updated the data since you read it.

With this in mind, any component that you were to use would really have to support reconnecting and re-establishing state. I do not use the Delphi ADO components so do not know whether they natively support this but I have my doubts.

My above example is very simple. Now if you expand this over a multi-element update and possibly multiple forms open with multiple database connections, it becomes rather messy unless your data access components support reconnecting natively (and possibly transparently). There is nothing stopping you implementing this yourself but I don't think it will be a menial task.

What I would do for one second is step back and ask the question "Why am I losing a connection to the database?" It could be that the client is not sending the correct keep-alive messages to the server. It could be that the link between the client and the database is unreliable. There are a whole host of reasons why it could happen. Is the PC going into standby? Is the router losing a connection. Is the VPN down? etc etc.

Answer the question as to why the connection is failing and you have actually resolved the problem without resorting to handling the problem in your application.

I am not saying that your approach is wrong or unnecessary. I am just suggesting another approach.

Sorry for not answering the question directly. Someone else may step in and do this for you.
Martyn SpencerConsultantCommented:
As an aside, I would not go down the route of checking the database state before every query is run (as suggested by the first response to your question). I can think of at least two reasons for not doing this.

There is a reason that Delphi provides exceptions and exception handling and it is to avoid writing code to check success or failure. Rather than this, you simply code for situations where the unexpected happens. Why check the database state yourself when it is being done by the components you are using. It is additional code and additional work for your program. It can be avoided.

If you were going down the route of checking each query, you would actually be better off trapping the exception that is raised to say that you are no longer connected to the database and as part of the exception handler you would restore the connection and data component state before carrying out the update.

The other reason is that if you lose the database connection, I am pretty certain that the database component will still return a "I am still connected" status when you check it. This will depend on the component implementation, however. In any case, you could still end up with an exception being raised so you should handle the exceptions as this is how it should be done in Delphi.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Stef MerlijnDeveloperAuthor Commented:
Even when I know why the connection to the database is lost, it still would be a good thing to handle it appropiately.

There are some thirdparty components that can handle connectionerrors (like: SDAC from DevArt and AnyDAC).
I even integrated DevArt into my application, but needed to change a lot of code to get it working as they use different names for certain commands, and do not support all features (like connecting through a UDL file in the connectionstring.
Therefore I prefer to handle it with the standard ADO-components in Delphi.

Let's see where our collegs come up with. I gues many developers have the same issue.
Martyn SpencerConsultantCommented:
Yes. It is a good idea to wait and see. However, this type of thing is often quite application specific so there is only a limited way in which the components can help and you still need to consider the issues that I outlined above.

It is very easy to write a component derived from the base classes that reconnects in case of an error. It is a whole new ball game handling re-establishing state. It is certainly achievable but not trivial, as I am sure you have worked out :)

When I had this problem, I went down the route of the user losing their changes but handling it gracefully with a reconnect. Not ideal, but since I also worked to minimise connection problems, the number of times this happens can be counted on one hand over a six month period.

Good luck!
I am curious about the idea of "[a]ny queries/stored procedures used in the application that are executed/opened will need to handle connectionloss and basically wait for the connection to become available again."  

How do you plan to "handle" queries/stored procs in a disconnected mode?  

You may want to consider doing a bit of research on the "Briefcase Model" applications.  These are designed to operate either with a database connection or in a local mode (either with a local database, which is the approach I prefer or just with local XML files).  If your users are suffering chronic disconnection issues, then using the Briefcase Model will allow them to pull a set of data from the Master Database to their local world, disconnect from the Master Database, work on it, and then apply any changes to the Master Database upon command (and assuming that there is a connection at that moment).

As a general comment, if the users are getting disconnected due to time-outs, you may want to look at extending the command time-outs for the queries, stored procs, and database connections.  (The default is something like 30 seconds, if I remember correctly.)
Geert GruwezOracle dbaCommented:
i always smile when people look amazed at a desktop computer loosing connection to a oracle database

consider a laptop computer on a forklift
and the forklift driver gets it's instructions on the display of the laptop

and now ... just imagine him driving with his pallet from 1 building to the next
1 with wireless available and nothing in between and then again in the building next door

if you can write a app with that in mind (periodically loosing (or having) connection to the database)
you have solved your problem

as mdspencer states ... it's a lot of work
from my experience ... 6 months to build the basic framework
and another 6 or so to filter out all the bugs

i have given you the complete library which can do that
it's up to you to do all the testing and setting up and programming ... etc
Geert GruwezOracle dbaCommented:
basically you only need this to test:
uses uRoot;

  TForm1 = class(TForm)
    Timer1: TTimer;
    Memo1: TMemo;
    procedure OnTimer1Timer(Sender: TObject); 
    fQuery: TDacQuery;
    procedure AddMsg(Msg: string);
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
procedure TForm1.AddMsg(Msg: string);
  Memo1.Lines.Add(FormatDateTime('dd/mm/yyyy hh:nn:ss') + ' ' + Msg);

procedure TForm1.OnTimer1Timer(Sender: TObject); 
var T: boolean;
  AddMsg('Before open query');
  T := False;
    if fQuery.IsActive then 
      T := True;
    AddMsg('Query opened, ' + fQuery.FieldByName('N').AsString + ' employees counted');
    on e: Exception do
  if not T then addMsg('Query open failed');

constructor TForm1.Create(AOwner: TComponent);
  inherited Create(AOwner);
  fQuery := TDacQuery.Create(Self, 'testlink', 'provider= ... Datasource= etc');
  fQuery.SQL.Text := 'SELECT COUNT(*) N from EMPLOYEE');

destructor TForm1.Destroy; 
  inherited Destroy;

Open in new window

you will have to start with a small app first and the reconnect working
converting a complete app will take too long to know all the trouble involved with reconnect
Stef MerlijnDeveloperAuthor Commented:
Thank you all for participating in this question.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now