asked on

How to recover database connection after hibernation

Hi,

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.

Example:
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.
https://www.experts-exchange.com/questions/26833210/SQL-Server-2008-Connection-error-EOleException-II.html

Is there some way to recover from this?

Sinisa Vuk

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

try
  ...
  qry.Connection := Adoconn;
  qry.Text := 'select Now(*) as CurrentTime';
  qry.Open;
exception
  on E: Exception do
  begin
    if E is conn error then DoReconnect;
  end;
end;

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

Geert G

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

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

Open in new window

Stef Merlijn

ASKER

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
begin
  Close;
  SQL.Clear;
  SQL.Add('INSERT INTO dbo.Mytable ');
  SQL.Add('( ID, Name ) ');
  SQL.Add('VALUES (');
  SQL.Add(QuotedStr(ID.Text));
  SQL.Add(', ' + QuotedStr(Name.Text));
  SQL.Add(')');
  try
    ADOConnectionSQL.BeginTrans;
    Prepared := True;
    try
      ExecSQL;
    except
      on E: exception do  //
      begin
        if not (AnsiPos('PRIMARY KEY', UpperCase(E.Message)) > 0) then
          Raise;
      end;
    end;
    ADOConnectionSQL.CommitTrans;
  except
    ADOConnectionSQL.RollbackTrans;
  end;
end;

Open in new window

How would I integrate your code in this situation?

Geert G

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

Stef Merlijn

ASKER

@Geert: The SQL is indeed only an example.
In other word how the get from my code to your:

    try
      qry.update;  <= I guess here my code must be integrated.
    except

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?

Stef Merlijn

ASKER

@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.

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stef Merlijn

ASKER

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.

Stef Merlijn

ASKER

Any additional suggestions?

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".

Stef Merlijn

ASKER

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.