ZMemTable 'Operation is not allowed in READ ONLY mode' exception

[brunodelmondes]

Hi guys!

I just installed Zeos SVN 7315 on Delphi 2010 for testing ZMemTable, then I wrote some code:

ZQuery.ReadOnly := false;
ZQuery1.Open;
ZMemTable1.EmptyDataset;
ZMemTable1.CloneDataFrom(ZQuery1);
ZMemTable1.Open;
ZQuery.Close;

I have a DataSource linked in a DBGrid and a DBNavigador, after doing some changes just clicked on button Post. then I got 'Operation is not allowed in READ ONLY mode' exception.

Did I do something wrong here?

There's one more question... Will ZMemTable be able to persist changes on a ZQuery for commiting them on the database?

Thank's in advance!

[aehimself]

Hello Brunodelmondes,

I'll answer your second question first. In theory yes, creating a snapshot from a dataset and modifying a dataset afterwards has no effect whatsoever on the snapshot. I made a small test project:

Code: Select all

 ZQuery1.Open;
 Memo1.Lines.Add('Query records: ' + ZQuery1.RecordCount.ToString);
 ZMemTable1.CloneDataFrom(ZQuery1);
 ZMemTable1.Open;
 Memo1.Lines.Add('MemTable records: ' + ZMemTable1.RecordCount.ToString);
 ZQuery1.Append;
 // Filling some required fields...
 ZQuery1.ApplyUpdates; // Applyupdates will post the changes to the database immediately
 Memo1.Lines.Add('Query records: ' + ZQuery1.RecordCount.ToString);
 Memo1.Lines.Add('MemTable records: ' + ZMemTable1.RecordCount.ToString);

This results:

Query records: 7
MemTable records: 7
Query records: 8
MemTable records: 7

Now, I'll look into the read-only error you get. Stay tuned.

[aehimself]

So the difference is, if you clone a dataset, the ResultSetConcurrency property of the statement is untouched, leaving it on rcReadOnly.
It creates the normal statement via TZMemResultSetPreparedStatement.Create and the cached resultset via TZVirtualResultSet.CreateFrom; none of these actually touch ResultSetConcurrency.

If you add FieldDefs and .Open the MemTable, it goes into TZAbstractMemTable.CreateResultSet, which will update this to rcUpdatable in line 282.

Now, read only after cloning can be bypassed, just add the line

Code: Select all

  Self.ResultSetConcurrency := rcUpdatable;

somewhere in TZVirtualResultSet.CreateFrom. A quick scan in the code says that currently only ZMemTable is using this so it should not affect any other functionality.

The question is... should we, or is it by design? I guess @EgonHugeist will have to tell that.

Edit: Fixed terminology

[brunodelmondes]

Thank you for that explanation, aehimself!!!

Maybe I didn't made myself clear.

What I'm wondering is, if it's possible to persist changes made in ZMemTable to a ZQuery and the ZQuery commit those changes to the database.

I'd like to be able to do that because, I intend to load the data from a ZQuery to a ZMemTable, close ZQuery, close ZConnection, do the work on ZMemTable and load those changes to the ZQuery and then commit them to a database. With that, I'd be able to be disconnected from de database and only connect when there is the need to commit changes. I know it is possible to almost do that with ZQuery in CachedUpdates mode, but if the connection fails (for any reason) I have to disconnect and connect again, that leads to closing ZQuery and all the changes are gone.

[aehimself]

That is an interesting idea... I like it! Keep the connection on only until you need it.

Are you using Delphi or Lazarus?

[brunodelmondes]

Keep the connection on only until you need it.

That's it Man!!!

Are you using Delphi or Lazarus?

Both of them, Delphi 2010 and Lazarus 2.0.10.

[aehimself]

In Delphi 10.4.1 and Zeos 8 I am able to copy field values (regardless of type) as binary data using the .AsBytes method:

Code: Select all

DestinationDataSet.Append;
For a := 0 To SourceDataSet.Fields.Count - 1 Do
 DestinationDataSet.FieldByName(SourceDataSet.Fields[a].FieldName).AsBytes := SourceDataSet.Fields[a].AsBytes;
DestinationDataSet.Post;

Again, this works on boolean, string, integer, float, datetime and blob fields too!

First, check if this works for you. If yes, we might be able to work something out (I remember Michael could not use this method to load data back to fields under Lazarus).
My idea is:
- Open the query, clone FieldDefs to a local ZMemTable
- Copy all data from query to local TZMemTable
- Close the query
- Make (and log) changes in TZMemTable
- Finally, reopen query
- Replay all changes (if ZMemTable.UpdateStatus works, we are blessed; no logging is needed whatsoever)
- Save query changes and close it
- Save changes with ZMemTable.CommitUpdates

Also, this suspiciously sounds similar to the behavior of a proxy driver... I personally never used this, but @Jan, what do you think? Can that be utilized somehow?

[EgonHugeist]

Thanks for the feedback!

Keep the connection on only until you need it.

That's it Man!!!

Are you using Delphi or Lazarus?

Both of them, Delphi 2010 and Lazarus 2.0.10.

I'll chek what going on if a connection is assigned.

What I'm wondering is, if it's possible to persist changes made in ZMemTable to a ZQuery and the ZQuery commit those changes to the database.

I'd like to be able to do that because, I intend to load the data from a ZQuery to a ZMemTable, close ZQuery, close ZConnection, do the work on ZMemTable and load those changes to the ZQuery and then commit them to a database. With that, I'd be able to be disconnected from de database and only connect when there is the need to commit changes. I know it is possible to almost do that with ZQuery in CachedUpdates mode, but if the connection fails (for any reason) I have to disconnect and connect again, that leads to closing ZQuery and all the changes are gone.

no that's not planned from my side. IMO MEM means MEM. Ever thought about how error prown such a behavior can be? I don't think it's possible to find the right comparision of what have been changed in the MemTable vs. DataBase. i.e. Changed fieldtype whith same name and so on...

What i'm thinking about is complete different. Let's start from the premisse the TZDataSets can emulate the obsolate TClientDataSets. Such emulation would be possible if all rows have been loaded to the client and no Streamed Lob's are used. Of course Calculated-Fields/Lookups may be considered too. Anyway this approch sounds more logical and should be easy to implement for Zeos. That behavior needs a new Flag like

Code: Select all

TZDataSet.KeepAliveIfLoaded

oslt. I'm open for proposals. To not forget: CachedUpdates + a "loaded" dataset should do the trick.
Guys i don't think we need to think about neither moving Data from TZMemTable -> TZDataSet nor implementing a TZClientDataSet just because others do need that technique. Keep life simple, do it smart.

[aehimself]

I don't think it's possible to find the right comparision of what have been changed in the MemTable vs. DataBase. i.e. Changed fieldtype whith same name and so on...

That is a very valid point, however the same rule applies if you want this functionality to be built-in to TZQuery / TZAbstractRODataSet. The answer is easy though using MemTable + Query, just enumerate TZMemTable.FieldDefs and compare .DataType of each.
But you are right, there are several cases which have to be manually handled, e.g.: how to know if the data was changed somewhere else, which is the valid one at the time of writing it back?

That behavior needs a new Flag like

Code: Select all

TZDataSet.KeepAliveIfLoaded

oslt.

Imo, when you close the Connection it enumerates and closes all connected datasets. This is what you'd like to override and keep datasets active if the flag is set?
Interesting idea, I guess we just have to see what happens if we do this...? :)

Guys i don't think we need to think about neither moving Data from TZMemTable -> TZDataSet nor implementing a TZClientDataSet just because others do need that technique. Keep life simple, do it smart.

I implemented something like this in one of my applications but because of a different reason; the logic is similar though. I copy the fielddefs of a TZQuery and monitor changes to a selected record. If any field has a different value than before, I append a new row to my TZMemTable, copy all data and mark the differences. This way I can roll back one (or all) changes, reverting the record back to a previous state.

rw.png

[brunodelmondes]

DestinationDataSet.Append;
For a := 0 To SourceDataSet.Fields.Count - 1 Do
DestinationDataSet.FieldByName(SourceDataSet.Fields[a].FieldName).AsBytes := SourceDataSet.Fields[a].AsBytes;
DestinationDataSet.Post;

I'll try this aehimself, thanks for the tip!

I'll chek what going on if a connection is assigned.

Thanks EgonHugeist!

no that's not planned from my side. IMO MEM means MEM. Ever thought about how error prown such a behavior can be? I don't think it's possible to find the right comparision of what have been changed in the MemTable vs. DataBase. i.e. Changed fieldtype whith same name and so on...

Well, actually I didn't. I don't have the experience and knowledge that you guys have.

What i'm thinking about is complete different. Let's start from the premisse the TZDataSets can emulate the obsolate TClientDataSets. Such emulation would be possible if all rows have been loaded to the client and no Streamed Lob's are used. Of course Calculated-Fields/Lookups may be considered too. Anyway this approch sounds more logical and should be easy to implement for Zeos. That behavior needs a new Flag like

Code: Select all

TZDataSet.KeepAliveIfLoaded

Oh man, that would be really really awesome!!! I can be available for testing if you want.

Guys, I appreciate so much your attention, thank you!!!

[marsupilami]

I'd like to be able to do that because, I intend to load the data from a ZQuery to a ZMemTable, close ZQuery, close ZConnection, do the work on ZMemTable and load those changes to the ZQuery and then commit them to a database. With that, I'd be able to be disconnected from de database and only connect when there is the need to commit changes. I know it is possible to almost do that with ZQuery in CachedUpdates mode, but if the connection fails (for any reason) I have to disconnect and connect again, that leads to closing ZQuery and all the changes are gone.

For me that sounds like a vallid use case for copying the data into an embedded database like Firebird Embedded, IBToGo, SQLite or even the Jet Engine (if one likes that masochistic thing for some reason).