Solved

Reconnect to database whenever a connection can be made

Posted on 2011-02-23
9
1,250 Views
Last Modified: 2013-11-23
Hi,

This question is an addition to:
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_26502495.html#33776495

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 :-)
0
Comment
Question by:Delphiwizard
[X]
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
9 Comments
 
LVL 2

Expert Comment

by:LelikInside
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....
0
 
LVL 2

Assisted Solution

by:mdspencer
mdspencer earned 375 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.
0
 
LVL 2

Assisted Solution

by:mdspencer
mdspencer earned 375 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.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Delphiwizard
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.
0
 
LVL 2

Assisted Solution

by:mdspencer
mdspencer earned 375 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!
0
 
LVL 22

Expert Comment

by:8080_Diver
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.)
0
 
LVL 37

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
0
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 125 total points
ID: 34979389
basically you only need this to test:
uses uRoot;

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

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

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

destructor TForm1.Destroy; 
begin
  fQuery.Free;
  inherited Destroy;
end;

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
0
 

Author Closing Comment

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

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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