Managing SQLConnection / Datasnap through client-server disconnects

Question

In my Datasnap client application I use 1 TSQLConnection for my methods and ProviderConnection.

The problems arise when the connection is lost. Both TSQLConnection.Connected and TSQLConnection.ConnectionState don't catch this.

When my TSQLconnection is open, but I lose internet connection, or the server stops the Datasnap client application gives lots of errors. (Server Methods or ClientDatasets)

I have created a function to manage my SQL connection for my server methods. but more problems arise when for example is close a ClientDataset that is connected through a TDSProviderConnection.

Q: How do you manage your client application to safely catch any disconnects on the TSQLconnection.
Q: How do you manage downtime, and what do you do with unsaved client state.

Reconnection after downtime is not the problem.

When calling a servermethod: This would throw exception.

tmpM:=TServerMethodsClient.Create(MYTSQLCONNECTION.dbxconnection,true);

So I wrote this next Method to obtain the TSQLCONNECTION from my datamodule with dummy method.

function TDMForm.DSConnection: TSQLConnection;
var
  tmpM:TServerMethodsClient;
begin
  result:=nil;
  if assigned(MYTSQLCONNECTION) then begin
    tmpM:=TServerMethodsClient.Create(MYTSQLCONNECTION.dbxconnection,true);
    try
      try
        tmpM.Ping;
        result:=MYTSQLCONNECTION
      except
        ReconnectForm.ShowModal; // has a reconnect button that tries to reconnect + shutdownbutton
        if ReconnectForm.modalresult=mrOK then
          result:=MYTSQLCONNECTION
        else
          MainForm.Close;
      end;
    finally
      tmpm.Free;
    end;
  end;
end;

I wrote the Method this way because only way to find out if connection is lost is through dummy method which would throw same error... then I can query for reconnect or close program.

Edit: I'm expecting some kind of general answer and guidelines, do's and don't. Not a correction of my code, this is just to show what I am doing at the moment.

Pasta

Answer 1

We struggled with the same issue -- how to detect when the connection is severed, and how to gracefully reconnect. Here's what we ended up doing, which has proven to be very successful for us.

Our users connect to the DataSnap server to retrieve data and make changes. All record creates, updates and deletes are fed immediately to the database via the OnBeforePost and OnBeforeDelete event handlers in the TClientDataSet.

Since you can't detect when the client has been forcibly disconnected from the DataSnap server until you try to communicate with the server and find out that the connection has been severed, we added a TApplicationEvents to the main form in our app and wrote an event handler for the OnException event. When we see an EIdSocketError, we know that the connection is dead, so we show a message to the user saying as much and then call Abort so that the Post or Delete will not happen in the local dataset. The user is able to re-login and then click save or delete again to complete their previous action.

Our global exception handler looks something like this:

procedure TMainForm.AppEventsException(Sender: TObject; E: Exception);
begin
  if E is EIdSocketError then
  begin
    AppEvents.CancelDispatch;
    MessageDlg('The connection to the database was lost.  You must log in before you retry the failed action.  Actual error message:'#13#10#13#10 + E.Message, mtError, [mbOK], 0);
    Abort;
  end;

  if E is TDBXError then
  begin
    AppEvents.CancelDispatch;
    MessageDlg('The database returned an error.  If you cannot correct the issue, please contact customer service with the following database error message:'#13#10#13#10 + E.Message, mtError, [mbOK], 0);
    Abort;
  end;

  // Show any other unhandled exceptions
  Application.ShowException(E);
end;

The TDBXError is another one we trap, since it usually means a DB error occurred, like a foreign key or unique key violation. Since the change wasn't made in the database, we Abort the change locally, which keeps us in sync with the db.

Because of the way we handle the sync with the db (via OnBeforePost and OnBeforeDelete), it doesn't matter that the connection was severed and the user reconnects and gets a new DataSnap session. We can even restart the DataSnap server without users losing their changes, etc. They just log in again and click save. Nothing is ever lost.

All of that answers your first question... Someday, we will implement an offline mode where the TClientDataSet can be saved to the user's HDD, and next time they log in, we will do a sync to apply all the locally saved changes. But we haven't done that yet -- so I don't have a good answer for your second question.

Cervical

You say "The user is able to re-login" but you don't say how you did that? In my own Datasnap app, even a call to TSQLConnection1.Connected := False, raises an exception (10053) since it is trying to tell the Server (which isn't there anymore) something. I am in catch-22 here. How did you get around that? – Workmanlike

@nolaspeaker - Since I know that I'm going to re-authenticate, I don't really care about the exception. So I just use try sqlcon.Close; except end;, which eats any exceptions. Then I can re-authenticate silently or by showing the user the login dialog again for them to manually log in. – Cervical

Yes, that handling occurred to me too - after I'd asked you the question! :-) Thanks for responding. – Workmanlike