[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Reconnect to database whenever a connection can be made

Posted on 2011-02-23
Medium Priority
Last Modified: 2013-11-23

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 :-)
Question by:Delphiwizard
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2

Expert Comment

ID: 34968577
Just before running EVERY query you should check if your DB in open state (Active=true) to avoid errors and make the connection dialog....

Assisted Solution

mdspencer earned 1500 total points
ID: 34968582
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.

Assisted Solution

mdspencer earned 1500 total points
ID: 34968617
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.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.


Author Comment

ID: 34968838
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.

Assisted Solution

mdspencer earned 1500 total points
ID: 34968915
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!
LVL 22

Expert Comment

ID: 34971542
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.)
LVL 38

Expert Comment

by:Geert Gruwez
ID: 34979314
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
LVL 38

Accepted Solution

Geert Gruwez earned 500 total points
ID: 34979389
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

Author Closing Comment

ID: 35005500
Thank you all for participating in this question.

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question