Link to home
Start Free TrialLog in
Avatar of Stef Merlijn
Stef MerlijnFlag for Netherlands

asked on

How to recover database connection after hibernation


At program startup my application will make the connection to the database and open some tables for general usage.
MS SQL Server will however shutdown when a computer (standalone PC or laptop) goes into hibernation. My application therefore looses the DB-connection (ADOConnection) too. When the PC wakes up from hibernation, the SQL Server is started again, but my application still has lost it's connection.
This doesn't apply to customer who work on a Windows Server.

An enduser makes updates to a certain table (within my application) without saving it yet. The fields on the form are NON-DB-components. If during some time the computer goes into hibernation and after a while wakes up from it, the enduser will press the save-button to store his changes. But then the connection is lost and an errormessage will be shown like:
- Connectionerror
- dbnetlib connectionwrite (send()). general network error. check your network documentation

I'm already using the ADOConnectionExt solution posted in EE, but that doesn't seem to help in these cases.

Is there some way to recover from this?
Avatar of Sinisa Vuk
Sinisa Vuk
Flag of Croatia image

I use simple method:

1. check for database time (simple fast query):
select Now(*) as CurrentTime

Open in new window

2. if connectiom is lost before - exception will pop up - handle it
  qry.Connection := Adoconn;
  qry.Text := 'select Now(*) as CurrentTime';
  on E: Exception do
    if E is conn error then DoReconnect;

Open in new window

3. on exception is run DoReconnect procedure which will close AdoConncetion and start new one. (Maybe is good to free olde AdoConnection instance and create new one). Pass new AdoConncetion as connection when needed:
qry.Connection := Adoconn;

Open in new window

you 'll have to encompass every select/update/delete/insert in a loop with a try except
(it's easiest doing that with a descendant component and overriding those methods)

in the loop with exception handler you'll have to program a reconnect
and off course a button with "I gave up on saving"

something like this:

var  // somewhat var
  giveupsaving: boolean; // threadsafe variable > access with a critical section or other syncro object

  success, connecterror, syntaxerror: boolean;
  reconnectattempts: integer;
  reconnectattempt := 0;
  giveupsaving := false;
  syntaxerror := false;
    connecterror := false;
    success := true;
      on e: exception do 
        succes := false;
        if pos('CONNECTION', upper(E.Message)) > 0 then 
          connecterror := true;
          // attempt reconnect
            on ec: exception do 
              // catch errors
              // limit connection attempts ?
              // Ask for reconnect to user ... 
              // if cancel then giveupsaving  := true;
            syntaxerror := true;
            // Check for any non connection problems 
         end ;
  until success or syntaxerror or giveup or (connecterror and (reconnectattempts > 1000)) ;

Open in new window

Avatar of Stef Merlijn


Hi Geert,

Thank you for the answer, but some details are not completely clear to me.

Suppose I want to execute the following update:
with Query1 do
  SQL.Add('INSERT INTO dbo.Mytable ');
  SQL.Add('( ID, Name ) ');
  SQL.Add('VALUES (');
  SQL.Add(', ' + QuotedStr(Name.Text));
    Prepared := True;
      on E: exception do  //
        if not (AnsiPos('PRIMARY KEY', UpperCase(E.Message)) > 0) then

Open in new window

How would I integrate your code in this situation?
no parameters ?
giving millions of queries with no parameters to a database is a performance killer !
SQL.Text := 'INSERT INTO dbo.Mytable ( ID, Name ) VALUES (:PARAM_ID, :PARAM_NAME)';
ParamByName('PARAM_ID').Value := ID.Text;
ParamByName('PARAM_NAME').Value := Name.Text;

why are you encapsulating a single insert in a transaction ?
probably just this sample ?

when using a transaction the loop has to go around the complete transaction
no point in only executing a part of the transaction as the transaction will be implicitly rolled back when the connection is lost.
so there is no point in doing a reconnect within a transaction either
@Geert: The SQL is indeed only an example.
In other word how the get from my code to your:
      qry.update;  <= I guess here my code must be integrated.

Open in new window

If I pass the content of any SQL-string to your procedure and then handle a global variable "SQLSuccess" (your var "success").
I might create a function out of it, to pass if the SQL must executed as SQL.Open or SQL-ExecSQL. The returnvalue can be SQLSuccess.
Would that do the trick or do I miss out on something?
@Geert: I've done some testing with this solution. Here are some findings.

If connection is lost + SQL instance is still not running:
When the SQL is run (ExecSQL or Open), it will take quite some time before it will recognize that the connection is lost. This might be due to some settings???
When using DBLookupComboBoxes then underlying lookup listsource is inactive too. When I want to use an EditValue from such a control to fill some parameter for the SQL, the program wil hang. So it doesn't get to the point to reconnect. F.e.:
  SQL.Add(', MyID      = ' + IntToStr(ILookupFieldMyID.EditValue));

Open in new window

If connection is lost + SQL instance is running again:
Reconnecting to DB still needs to be done. Controls seem to have forgotten which connection to use.
As you said, for all SQL-statements reconnection need to be done before they are executed, otherwise an error will occur.
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you Geert.
If your wife says you're not the fastest man on the planet, than that could also be a benifit :-)

Devart does have reconnect built in, but having a quick look at their forum, it still doesn't seem to work in all cases.
Also switching from ADO to DevArt components is not a thing to take lightly. I've made a start a few years back, but there were all kinds of compatibility-issues to handle. It would be great to start with Devart if you build your application from scratch or if it is a small application.

Of course one could choose not to use DB-components, but load data into NON-DB components. This would bypass all those related problem. Most of my detail-form are already setup like that, except for lookupcomboboxes and such. But all these can be exchanged by NON-DB equivalants too.
Any additional suggestions?
Avatar of Mike McCracken
Mike McCracken

I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
This is a very complex issue and therefore not easily solved by a little bit of code.
The unit Geert Gruwez provided provides a good startingpoint, but it is still a lot of work to implement.
The easiest way to implement this is to check for a connection every time something needs to be done with the database. But even then there are a lot of situations where this isn't enough.
Looking for a component that supports connection recovery might seem the ultimate solution, but even then lot's of situations can ocur that are not covered.
This issue is to big to expect a full working solution as it needs lot's of customization dependent on the application and database involved.