Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: How to recover TADOQuery in State dsInsert after disconnect


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 1 - Last Post: Jul 26, 2016 3:05 AM Last Post By: Johannes Riemer Threads: [ Previous | Next ]
Johannes Riemer

Posts: 2
Registered: 8/22/16
How to recover TADOQuery in State dsInsert after disconnect  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 22, 2016 3:44 AM
In certain conditions a TADOQuery enters an inconsistent state, where it cannot be used anymore.
In this situation, subsequent calls to the query, e.g. query.Close, result in an EOleException 'Operation is not allowed when the object is closed'.
Reason: The query is still in State dsInsert, but the underlying ADO recordset is disconnected.

This question, in an earlier state, has also been posted at: http://stackoverflow.com/questions/38499796/recover-tadoquery-in-state-dsinsert-after-disconnect

Question 1: Can anyone reproduce or understand this situation?
Question 2: If so, how can the query be recovered, so that it is usable again?

Thanks!
Johannes.

Steps to reproduce:
(please find a console test program below):

- Create a query with an explicit ADOConnection
- Open query
- The query does not match a row
- Call query.Insert
- Close the ADOConnection of the query
- Expected query state: Active = false, State = dsBrowse
- Observed query state: Active = true, State = dsInsert
- A call to query.Close fails with an EOleException: 'Operation is not allowed when the object is closed'

Test program
To reproduce the error, please run the following program twice. In the main block, toggle between test case 1 and 2.
Also, adapt the connection string to a database in your test environment. A test table is created by the test program.

Test case 1 shows a situation as expected.
Test case 2 shows an unexpected situation.

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

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);
  con:= TADOConnection.Create(nil);
  query:= TADOQuery.Create(nil);
  try
    try
      con.ConnectionString:= 'Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Data Source=10.0.0.2;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.
Johannes Riemer

Posts: 2
Registered: 8/22/16
Re: How to recover TADOQuery in State dsInsert after disconnect  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 26, 2016 3:05 AM   in response to: Johannes Riemer in response to: Johannes Riemer
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.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02