Solved

Reconnect to database whenever a connection can be made

Posted on 2011-02-23
9
1,141 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 36

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 36

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now