Log Error in OnConnectionLost

[FredS]

When MSSQL restarts the original Error would be something like: "TCP Provider: An existing connection was forcibly closed by..", how can I log this inside OnConnectionLost?
The Exception passed in as Sender has no InnerException nor contains any relevant info in its Message.

Code: Select all

procedure TDataDM.ConConnectionLost(Sender: TObject; Component: TComponent; ConnLostCause: TConnLostCause; var RetryMode: TRetryMode);
var e : EUpdateError absolute Sender;
const cConnectionLost = 'Connection Lost';
begin
  Log(cConnectionLost + ': ' + e.Message);
  RetryMode := TRetryMode.rmReconnectExecute;
end;

[FredS]

In the 'FailOver' demo the Kill Session SQL does not work for FB3.

[Stellar]

The exception object is not passed to the OnConnectionLost Event. To capture the server connection break, you can try processing the OnError event for TUniConnection.
In the demo project, the session deletion method is implementedf for InterBase. You can learn more about how to delete a session in FireBird 3 in the documentation:
https://firebirdsql.org/file/documentat ... ttach-kill

[FredS]

..you can try processing the OnError event for TUniConnection.

I already have that set and nothing is being logged.
The obvious issue here is that if another error occurs we get support requests with something like

Code: Select all

 'Dataset was not open'

without knowing what really occurred.

[FredS]

You can learn more about how to delete a session in FireBird 3 in the documentation:
https://firebirdsql.org/file/documentat ... ttach-kill

I managed to achieve disconnection using:

Code: Select all

      dbFirebird:  begin
         CurConID := Con.QuerySingle('SELECT CURRENT_CONNECTION FROM RDB$Database');
         KillCon.ExecSQL('DELETE FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = :ATTACHMENT_ID', [CurConID]);
       end;

However this did NOT trigger 'ConnectionLost' but ended in an unhandled exception:

Code: Select all

raised exception class EIBCError with message 'connection shutdown'.

Restarting the FB3 Server gave me this instead, and still no 'ConnectionLost' :

Code: Select all

 raised exception class EIBCError with message 'Error reading data from the connection.'.

EDIT:
Turns out the above errors occur with FB3 only if 'DisconnectedMode=FALSE'. The dbs I've tested MSSQL, MARIADB, MYSQL all work fine in this state. Bug or architecture?

Another thing that popped up is that one cannot use the default connection in 'FailOver' to connect to FB3. With 'Pooling' Active it raises:

Code: Select all

Error: Can't perform operation on connected connection. 

DisconnectedMode and no Pooling?

[ViktorV]

1. The OnConnectionLost event occurs only when the following conditions are fulfilled:
- a fatal error occurs;
- there are no opened transactions in a connection that are not ReadOnlyReadCommitted (if connection has at least one opened transaction, which is not ReadCommitedReadOnly, FailOver does not execute. All ReadCommitedReadOnly transaction are restored with FailOver operation);
- there are no opened and non-fetched datasets;
- there are no explicitly prepared datasets or SQLs.
In the UniDAC FailOver demo, the sample does not use the ReadOnlyReadCommitted, but the ReadCommitted transaction, so the OnConnectionLost event does not work. You can change the transaction type for TUniConnection to ReadOnlyReadCommitted, and use a separate transaction of type ReadCommitted to change the data of all datsets. To do this, you can use the TUniQuery.UpdateTransaction property.
2. We will investigate the cause of the error "Can't perform the operation" and let you know the result.

[FredS]

During today's testing I found a couple of inconsistencies, the fix was to sync the checkbox settings with the DM before connect:

Code: Select all

procedure TMainForm.ConnectionBeforeConnect(Sender: TObject);
begin
  FShouldNotUpdateControls := True;
  try
    DM.Connection.Options.LocalFailover := cbFailover.Checked;
    DM.Connection.Pooling := cbPooling.Checked;
    DM.quDetail.CachedUpdates := cbCachedUpdates.Checked;
    DM.quDetail.Options.LocalMasterDetail := cbLocalMasterDetail.Checked;
    DM.quDetail.SpecificOptions.Values['FetchAll'] := BoolToStr(cbFetchAll.Checked, True);
    DM.Connection.Options.DisconnectedMode := cbDisconnectedMode.Checked;
    DM.quMaster.Debug := cbDebug.Checked;
    meLog.Lines.Add(Format('Failover: %d, Pooling: %d, CachedUpdates: %d, LocalMaster: %d, FetchAll: %d, DisconnectedMode: %d',
                     [Ord(cbFailover.Checked), Ord(cbPooling.Checked), Ord(cbCachedUpdates.Checked),
                      Ord(cbLocalMasterDetail.Checked), Ord(cbFetchAll.Checked),Ord(cbDisconnectedMode.Checked)]));
  finally
    FShouldNotUpdateControls := False;
  end;
end;

After that I reran my tests and below are the log entries. For my purpose only the top row of the master was altered and posted.

Code: Select all

Failover: 1, Pooling: 1, CachedUpdates: 0, LocalMaster: 0, FetchAll: 1, DisconnectedMode: 0
Provider: SQL Server
Transaction.IsolationLevel: 0
11:15:32 AM Session was killed
11:15:38 AM  - Connection lost during SQL execution
Provider: SQL Server
Transaction.IsolationLevel: 0
==========================================

Failover: 1, Pooling: 1, CachedUpdates: 0, LocalMaster: 0, FetchAll: 1, DisconnectedMode: 0
Provider: MySQL
Transaction.IsolationLevel: 0
11:16:31 AM Session was killed
11:16:36 AM  - Connection lost during SQL execution
Provider: MySQL
Transaction.IsolationLevel: 0
==========================================

Failover: 1, Pooling: 0, CachedUpdates: 0, LocalMaster: 0, FetchAll: 1, DisconnectedMode: 0
Provider: InterBase
Transaction.IsolationLevel: 0
11:18:51 AM Session was killed
connection shutdown

As you can see the only change in settings is Pooling due to the error reported before.
Yet Interbase errors with connection shutdown without firing the ConnectionLost Event.

[ViktorV]

This is the correct behavior of UniDAC. As we have already written the OnConnectionLost event does not generate if the sample does not use the ReadOnlyReadCommitted, but the ReadCommitted transaction. To solve the issue please use the ReadOnlyReadCommitted transaction in your sample.

[FredS]

..please use the ReadOnlyReadCommitted transaction in your sample.

OK, that of course is a ReadOnly transaction and completely alters the behavior in comparison to the other dbs.

But I'm still not clear how to use it, I get this when I try to assign it:

Code: Select all

DM.Connection.DefaultTransaction.IsolationLevel := iblReadOnlyReadCommitted;
[dcc32 Error] Main.pas(402): E2010 Incompatible types: 'TCRIsolationLevel' and 'TIBCIsolationLevel'

From https://www.devart.com/ibdac/docs/devar ... nlevel.htm:
iblReadOnlyReadCommitted : Enables the transaction to see all committed data in the database with read-only access mode.

[ViktorV]

Since UniDAC is a universal set of components designed to work with various DBMS, the property TUniTransaction.IsolationLevel requires the implementation of transaction isolation levels that are common to various DBMS.
You can use the following code to solve your task:

Code: Select all

DM.Connection.DefaultTransaction.IsolationLevel := ilReadCommitted;
DM.Connection.DefaultTransaction.ReadOnly := True;

The TCRIsolationLevel is defined in the CRAccess.pas module. To solve the issue you can add the CRAccess unit to the USES clause of your unit.