TMSQuery always gets closed and open when used as a source to a ClientDataSet via Provider

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jachguate
Posts: 3
Joined: Tue 22 Aug 2017 15:16

TMSQuery always gets closed and open when used as a source to a ClientDataSet via Provider

Post by jachguate » Wed 29 Aug 2018 16:15

I'm facing a behavior which, at first glance, looks incorrect (to me, at least).

I have a TMSQuery which is used to capture data in a form. Doing modifications, I'm adding a Provider associated to this TMSQuery and a ClientDataSet associated to this provider.

The TMSQuery is active and have updates in cache. Under some circumstances, I'm trying to pull data from the TMSQuery to the ClientDataSet calling Close/Open over it.

The result is the underlying TMSQuery gots closed and re-opened, losing all the updates that was introduced since the last refresh.

I've used this approach in the past using different data access component, and it worked, except on SDAC (and maybe other sister components).

I've narrowed the problem to the implementation of TCustomDADataSet.PSReset method. Comparing the implementation to the one of other TDataSets components included in Delphi like TCustomSQLDataSet, I can see why this is happening.

I'm looking for recommendations to avoid this. I'm not sure to call this a bug, so I'm also interested to see if this is seen as bad here before making a bug report.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: TMSQuery always gets closed and open when used as a source to a ClientDataSet via Provider

Post by Stellar » Thu 30 Aug 2018 10:47

When closing TClientDataSet, Post for TMSQuery is automatically executed, if the poNoReset option is set for TDataSetProvider. Accordingly, when closing TClientDataSet, all changes in TMSQuery will be saved. But since you are using cached update of data in TMSQuery, it is not enough to execute the Post command to apply the changes. Please try, before closing ClientDataSet, apply the changes in TMSQuery by executing the ApplyUpdates method, for example:

Code: Select all

DataSetProvider1.DataSet := MSQuery1;
ClientDataSet1.ProviderName := 'DataSetProvider1';

Code: Select all

MSQuery1.ApplyUpdates;
ClientDataSet1.Close;
ClientDataSet1.Open;

jachguate
Posts: 3
Joined: Tue 22 Aug 2017 15:16

Re: TMSQuery always gets closed and open when used as a source to a ClientDataSet via Provider

Post by jachguate » Thu 30 Aug 2018 16:52

Stellar wrote: Thu 30 Aug 2018 10:47 But since you are using cached update of data in TMSQuery, it is not enough to execute the Post command to apply the changes. Please try, before closing ClientDataSet, apply the changes in TMSQuery by executing the ApplyUpdates method, for example:
Thanks for your suggestion, but I don't want to post data or apply updates. In fact, the ClientDataSet is ReadOnly.

What I'm after is to have a copy of the data available while the TMSQuery is being edited to perform calculations and validations that the customer is asking for that depends on other records present in the dataset (that may or may not be present in the database, since the TMSQuery is caching updates and the user is in control on when to post this data to the database).

The TMSQuery is linked to visual components in a VCL application where a user is performing manual input.

I've used this technique in the past with other data access components without much trouble. It may not be the best solution, so I'm open to new ideas.

The idea behind is to mirror the dataset, in this case the TMSQuery data to a ClientDataSet at some points (which I know and control) using the provider. Every time I need to update the mirror, I just close and re-open the ClientDataSet, which in my point of view, doesn't have to close and re-open the TMSQuery, and in fact, other components works properly.

The idea I've grown is that if you pull data from a provider and the underlying dataset is closed, it opens the dataset, pulls the data and then closes it.

But if the provider find the underlying dataset already opened, it works with the dataset and leaves it open when finish, without closing and re-opening it.

Am I wrong with this?

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: TMSQuery always gets closed and open when used as a source to a ClientDataSet via Provider

Post by Stellar » Thu 06 Sep 2018 11:38

When the PSReset method is executed, the dataset is automatically closed and opened. Unfortunately, we cannot disable dataset closing and opening, because it changes the existing behavior. If you have SDAC source code, you can comment dataset closing and opening in the DBAccess unit, for example:

Code: Select all

procedure TCustomDADataSet.PSReset;
begin
  inherited PSReset;

//  if Active then begin
//    Close;
//    Open;
//  end;
end;
If you do not have SDAC source code, you can create a TMSQuery descendant, where you can override the PSReset method without calling the descendant method.

Post Reply