[solved] Cannot perform this operation on a closed dataset

[marsupilami]

Hello

I finally have my first testproject and have a problem. In most of my Projects I use a structure similar to this:

Code: Select all

  ZConnection1.Connect;
  ZConnection1.StartTransaction;
  try
    [do someting]
    ZConnection1.Commit;
  except
    ZConnection1.Rollback;
  end;
  ZConnection1.Disconnect;

With Zeos 7 this raises an exception on commit: "XQuery: Cannot perform this operation on a closed dataset.", where XQuery is a closed query.
When the program subsequently tries to execute the rollback I get the exception "Invalid operation in AutoCommit mode".
Taking a look at the database, I see that the Transaction has been committed. But I wonder why the Connection would do anything to an inactive Query and why it does this after the commit?

This happens with the current version of http://svn.code.sf.net/p/zeoslib/code-0 ... es/testing as well as with the latest version of http://svn.code.sf.net/p/zeoslib/code-0/trunk on Delphi XE2 and Firebird 2.1. The transaction isolation level is set to tiReadCommitted.

Best regards,

Jan

[marsupilami]

Hello,

The problem seems to have resolved itself somehow.
I am sorry for the trouble...
Best regards,

Jan

[patyit]

Hi all !

Jan is lucky, his problem is resolved itself !
But mine is not !

Lazarus 1.1, FPC 2.6.0, Zeos svn Testng (also Trunc) latest, Firebird 2.5

...
Database.StartTransaction;
try
Query1.ApplyUpdates;
Query2.ApplyUpdates;
Query3.ApplyUpdates;

Database.Commit; // <--- Error message: 'External: SIGSEGV'.
except
Database.Rollback;
... // error handling an back to edit data
Exit;
end;
QPrometz.CommitUpdates;
QPromett.CommitUpdates;
QPromet.CommitUpdates;
...

The full error message is :

Project RobFinPro raised exception class 'External: SIGSEGV'.

In file '/home/patyi/lazarus-svn/zeos7_test/src/component/ZAbstractDataset.pas' at line 641:
CachedResultSet.DisposeCachedUpdates;

I'm developing an ERP like (big) application and this code construction is very important !
I found bugreport for this : http://zeosbugs.firmos.at/view.php?id=206
Any progress ?

Best regards, Patyi

[ism]

Lazarus 1.1 ? What you want ?

On older stable versions of the same ?

[ism]

Try last rev from http://svn.code.sf.net/p/zeoslib/code-0 ... es/testing

[EgonHugeist]

patyit,

Nope i don't think there was any further progress. Like i wrote on the BugReport i wanted to have some testers for the added code. I had no time to add a testcase to the testsuites we have. Maybe you can help me here?

Can you determime the SIGDEV?

Ism, he uses the new testing-branch on the other hand it would not be possible to have procedure DisposeCachedUpdates avialable, i think.

Michael

[patyit]

ism,
I'm using exactly that rev !

Michael,
I can write a small test application with data to demonstrate and test this bug.
I can test the changes, in test application and on production application with live data.
At least I can tell You it is ok or not.

By now : External: SIGSEGV when try to execute CachedResultSet.DisposeCachedUpdates

[patyit]

Some additional information :

I made a test, simulating wrong data witch is cause foreign key violation on ApplyUpdates, the changes is rolled back, and I can edit data correctly to solve the problem. It seams to Rollback is working correctly ...

[EgonHugeist]

patyit,

having a test-case would be fine. For me personally would it be the best help if you are able to add a test to our testsuites directly. So we can test the functionallity allways. However a test case or a patch to fix this issue would be great...

My thougths about the sequence the code should work now:
Take care TZConnection.AutoCommit = False and about the Transaction isolation levels...
try
Query1.ApplyUpdates;
Query2.ApplyUpdates;
Query3.ApplyUpdates; // <--- Error message: 'External: SIGSEGV'. This IS ok for me, if values are missing or the constraint cascade Rules making trouble! Btw. i implemented a TZQuery.Option doUpdateMasterFirst (only available with Zeos) which allows now to update a ForeignKey constraint in the MasterTable first and then all child-Tables.. (Here i allready made some tests and the work nice)

Database.Commit; //IF all Updates are done then this is not neccessary with Zeos if AutoCommit is True on the other hand it should work like expected.. I think this is the issue you want ot point me?!
except
Database.Rollback; //Roleback should dispose all done updates of the RowAccessor and calls DisposeUpdatesCh
... // error handling an back to edit data
Exit;
end;
QPrometz.CommitUpdates; // here i don't understand you... Database.Commit should do this before.
QPromett.CommitUpdates;
QPromet.CommitUpdates;


Michael

[patyit]

Hi all !

procedure TZAbstractConnection.Commit;
var
ExplicitTran: Boolean;
i: Integer;
begin
CheckConnected;
CheckNonAutoCommitMode;

ExplicitTran := FExplicitTransactionCounter > 0;
if FExplicitTransactionCounter < 2 then
//when 0 then AutoCommit was turned off, when 1 StartTransaction was used
begin
ShowSQLHourGlass;
try
try
// ***** rem by patyi *****
// for i := 0 to FDatasets.Count -1 do
// THack_ZAbstractDataset(FDatasets).DisposeCachedUpdates;

FConnection.Commit;
finally
FExplicitTransactionCounter := 0;
if ExplicitTran then
AutoCommit := True;
end;
finally
HideSQLHourGlass;
end;
DoCommit;
end
else
Dec(FExplicitTransactionCounter);
end;

I modyfied this procedure and seams to work good with fallowing program construction :
...
Database.StartTransaction;
try
Query1.ApplyUpdates;
Query2.ApplyUpdates;
Query3.ApplyUpdates;

Database.Commit; // earlier I got Error message: 'External: SIGSEGV'.
except
Database.Rollback;
... // error handling an back to edit data
Exit;
end;
Query1.CommitUpdates; // this clear cached buffer,
Query2.CommitUpdates; // need because I'm modyfied the
Query3.CommitUpdates; // TZAbstractConnection.Commit procedure
...

As I said it works good, but I'm not shore about memory leaks ...

[EgonHugeist]

patyit,

ok this a sloution and an example to check the issue. I hope i'll find the time next week to find out why THack_ZAbstractDataset(FDatasets).DisposeCachedUpdates; does raise exceptions.

[EgonHugeist]

patyit,

I can do what i want. No way to reproduce the issue.

i use this code:

Code: Select all

procedure TZTestMasterDetailCase.TestClientDatasetWithForeignKey_ApplyUpdates;
var
  SQLMonitor: TZSQLMonitor;
  I: Integer;

  procedure SetTheData(Index: Integer);
  begin
    MasterQuery.Append;
    MasterQuery.FieldByName('dep_id').AsInteger := TestRowID + Index;
    if Connection.UTF8StringsAsWideField or ( Connection.DbcConnection.GetEncoding = ceAnsi) then
        MasterQuery.FieldByName('dep_name').AsString := 'öäüüäö'
    else
      MasterQuery.FieldByName('dep_name').AsString := Utf8Encode(WideString('öäüüäö'));
    MasterQuery.FieldByName('dep_shname').AsString := 'abc';

    if Connection.UTF8StringsAsWideField or (Connection.DbcConnection.GetEncoding = ceAnsi) then
       MasterQuery.FieldByName('dep_address').AsString := 'A adress of öäüüäö'
    else
      MasterQuery.FieldByName('dep_address').AsString := Utf8Encode(WideString('A adress of öäüüäö'));

    CheckEquals(True, (MasterQuery.State = dsInsert), 'MasterQuery Insert-State');
    MasterQuery.Post;

    DetailQuery.Append;
    DetailQuery.FieldByName('p_id').AsInteger := TestRowID + Index;
    DetailQuery.FieldByName('p_dep_id').AsInteger := TestRowID + Index;

    if Connection.UTF8StringsAsWideField or (Connection.DbcConnection.GetEncoding = ceAnsi) then
        DetailQuery.FieldByName('p_name').AsString := 'üäööäü'
    else
      DetailQuery.FieldByName('p_name').AsString := Utf8Encode(WideString('üäööäü'));

    DetailQuery.FieldByName('p_begin_work').AsDateTime := now;
    DetailQuery.FieldByName('p_end_work').AsDateTime := now;
    DetailQuery.FieldByName('p_picture').AsString := '';
    DetailQuery.FieldByName('p_resume').AsString := '';
    DetailQuery.FieldByName('p_redundant').AsInteger := 5;
    DetailQuery.Post;
    CheckEquals(True, (DetailQuery.State = dsBrowse), 'MasterQuery dsBrowse-State');
    CheckEquals(True, (MasterQuery.State = dsBrowse), 'MasterQuery dsBrowse-State');
  end;
begin
  //Connection.AutoCommit := False;
  Connection.TransactIsolationLevel := tiReadCommitted;
  Connection.StartTransaction;
  SQLMonitor := TZSQLMonitor.Create(nil);
  SQLMonitor.Active := True;
  MasterQuery.SQL.Text := 'SELECT * FROM department ORDER BY dep_id';
  MasterQuery.CachedUpdates := True;
  MasterQuery.Open;

  DetailQuery.SQL.Text := 'SELECT * FROM people';
  DetailQuery.MasterSource := MasterDataSource;
  DetailQuery.MasterFields := 'dep_id';
  DetailQuery.LinkedFields := 'p_dep_id';
  DetailQuery.CachedUpdates := True;
  DetailQuery.Open;
  SetTheData(0); //Add 1 line/DataSet
  try
    try
      DetailQuery.ApplyUpdates; //-> crash because of the cascade rules
      MasterQuery.ApplyUpdates;
      Connection.Commit;
      Fail('Wrong ApplayUpdates behavior!');
    except
      SetTheData(1); //Add some more lines
      DetailQuery.Options := DetailQuery.Options + [doUpdateMasterFirst];
      MasterQuery.ApplyUpdates;
      DetailQuery.ApplyUpdates;
      Connection.Commit;
      CheckEquals(True, (MasterQuery.State = dsBrowse), 'MasterQuery Browse-State');
      CheckEquals(True, (DetailQuery.State = dsBrowse), 'DetailQuery Browse-State');
      MasterQuery.SQL.Text := 'delete from people where p_id = '+IntToStr(TestRowID+1);
      MasterQuery.ExecSQL;
      MasterQuery.SQL.Text := 'delete from department where dep_id = '+IntToStr(TestRowID+1);
      MasterQuery.ExecSQL;
    end;
  finally
    MasterQuery.SQL.Text := 'delete from people where p_id = '+IntToStr(TestRowID);
    MasterQuery.ExecSQL;
    MasterQuery.SQL.Text := 'delete from department where dep_id = '+IntToStr(TestRowID);
    MasterQuery.ExecSQL;
    SQLMonitor.Free;
  end;
end;

Waht i'm doing wrong?

Edit:
issue confirmed and is not solvable (Each second cast of the AbstractDataSet uses a pointer to a IZCachedResultSet except DXE2 up) so i've added a difine to exclude this code.

Issue is gone. Patch done 1837
Confirmed?

[patyit]

Hi EgonHugeist !

I confirmed the issue is gone, at list on Lazarus 1.1, FPC 2.6.1, on XUbuntu,
Win XP and Win 7 tested with my live multiplatform application.
"Unfortunately" I don't have Delphi (fortunately have Lazarus) that I can't test under Delphi.

So now I don't need to call CommitUpdates after Commit ?

Thank for help ! Patyi.

[EgonHugeist]

patyit,

thank you for confirm the commit behavior.

So now I don't need to call CommitUpdates after Commit ?

Yes that's right. CommitUpdates did check the BrowseMode and Clear the Buffer.

Thought the Sequence should be handled like you and others did proprose and in this case CommitUpdates is a deprecated procedure.
Can you post something on Mantis to close this issue?