Recover TADOQuery in State dsInsert after disconnect

Question

We use a Delphi TADOQuery with an explicit connection for inserts.

Summary: When the connection is lost while the query is in State dsInsert, the query seems to enter an inconsistent state with respect to the underlying ADO recordset. As a result, the query cannot be used anymore, even if the connection has been reestablished.

Details:

Assume the following simplified steps:

  quTest.Connection:= ADOConnection1;
  quTest.Open;
  quTest.Insert;
  //Simulate lost connection
  ADOConnection1.Close;

  try
    //quTest.State is still dsInsert
    quTest.Post; //Throws 'Operation is not allowed when the object is closed'. This is the expected beavior.
  except
    //Reconnect (simplified algorithm)
    ADOConnection1.Connected:= true;
  end;

  //quTest.State is still dsInsert
  //So far, so good.
  //Now let's close or abort or somehow reset quTest so that we can use it again. How? 
  quTest.Close //throws 'Operation is not allowed when the object is closed'

The problem is that at the end of the code sample above quTest is still in State dsInsert, but the underlying ADO recordset is disconnected. Any attempt to close or somehow reset quTest fails with an exception 'Operation is not allowed when the object is closed'.

Please note that our goal is not to continue the initial insert operation. We just want to bring the query back to a state where we can open and use it again.

Is this possible?

Since quTest is part of a datamodule with design-time field bindings, we cannot easily free the broken query and create a new query instance.

Edit: Of course, the simulation of the disconnect is not too realistic. However, comparing the stack traces of the production error and the test sample, we see that the test is good enough.

Production stack trace:

================================================================================
Exception class  : EOleException
Exception message: Operation is not allowed when the object is closed
EOleException.ErrorCode : -2146824584
================================================================================
[008B9BD7] Data.Win.ADODB.TCustomADODataSet.InternalGotoBookmark + $17
(0000E290) [0040F290]
[008B9BD7] Data.Win.ADODB.TCustomADODataSet.InternalGotoBookmark + $17
[008B9BF4] Data.Win.ADODB.TCustomADODataSet.InternalSetToRecord + $14
[0081EEBE] Data.DB.TDataSet.InternalSetToRecord + $2
[0081D576] Data.DB.TDataSet.SetCurrentRecord + $62
[0081D9A4] Data.DB.TDataSet.UpdateCursorPos + $10
[0081E378] Data.DB.TDataSet.Cancel + $68
[0081AA49] Data.DB.TDataSet.SetActive + $AD
[0081A841] Data.DB.TDataSet.Close + $9


Test case stack trace:

Data.Win.ADODB.TCustomADODataSet.InternalFirst
Data.DB.TDataSet.SetCurrentRecord(0)
Data.DB.TDataSet.UpdateCursorPos
Data.DB.TDataSet.Cancel
Data.DB.TDataSet.SetActive(???)
Data.DB.TDataSet.Close

In fact, since the query state is still dsInsert, an attempt to .Cancel is made, resulting in subsequent calls to the ADO recordset which fail.

procedure TDataSet.SetActive(Value: Boolean);
begin
...
        if State in dsEditModes then Cancel;
...
end;

Edit 2: The problem is not easy to reproduce, since it seems to be data dependent. That's why I created a console test program. Please run the test program twice and change the test case in the main block. The output of the tests at my machine is shown below.

Console test program:

program Project2;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  Data.DB,
  Data.Win.ADODB,
  ActiveX;

procedure Setup(aConnection: TADOConnection; aEmpty: Boolean);
var
  query: TADOQuery;
begin
  query:= TADOQuery.Create(nil);
  try
    query.Connection:= aConnection;

    //Create test table
    try
      query.SQL.Add('create table test3 (a int)');
      query.ExecSQL;
      WriteLn('Table created.');
    except
      on e: Exception do
        Writeln(e.Message);
    end;

    //Clear test table
    query.SQL.Clear;
    query.SQL.Add('delete test3');
    query.ExecSQL;

    if not aEmpty then begin
      //Create a row
      query.SQL.Clear;
      query.SQL.Add('insert into test3 values (0)');
      query.ExecSQL;
    end;
  finally
    query.Free;
  end;
end;

var
  con: TADOConnection;
  query: TADOQuery;
begin
  CoInitialize(nil);
  try
    con:= TADOConnection.Create(nil);
    query:= TADOQuery.Create(nil);
    try
      con.ConnectionString:= 'Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Data Source=10.0.0.11,1433;Initial Catalog=TestDB';
      con.Connected:= true;

      //Test case 1: With data
      Setup(con, false);

      //Test case 2: No data
      //Setup(con, true);

      query.Connection:= con;
      query.SQL.Add('select * from test3');
      query.Open;
      query.Insert;
      con.Close;
      WriteLn('query.Active: ' + BoolToStr(query.Active));
      WriteLn('query.State: ' + IntToStr(Ord(query.State)));
      query.Close;
      WriteLn('Test ran without exception.');
    except
      on E: Exception do
        Writeln('Exception: ' + E.ClassName, ': ', E.Message);
    end;
  finally
    ReadLn;

    query.Free;
    con.Free;
  end;
end.

Test-Environment:

Delphi 10 Seattle Version 23.0.21418.4207
Console test program platform: Win32
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

Tested on:

Windows 8.1 Pro in IDE
Windows 8.1 Pro
Windows Server 2008 R2 Standard, 6.1.7601 SP1 Build 7601
Windows Server 2008 R2 Standard

Output of test case 1:

There is already an object named 'test3' in the database
query.Active: 0
query.State: 0
Test ran without exception.

Output of test case 2:

There is already an object named 'test3' in the database
query.Active: -1
query.State: 3
Exception: EOleException: Operation is not allowed when the object is closed

nang · Accepted Answer

The reason for the observed behavior is a change in or before Delphi XE6, which I think is a bug.

https://quality.embarcadero.com/browse/RSP-15545

Summary:

The problem does not occur in Delphi 2007 and Delphi XE.
The problem occurs in Delphi 10.1
The problematic code change has been introduced in or before XE6 in TDataSet.SetActive, where a new call to Cancel has been added.
This call fails in the described scenario leading to the described effects.

Recover TADOQuery in State dsInsert after disconnect

Answers (2)

Related Questions