QueryRecCount

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Zoner
Posts: 6
Joined: Thu 03 Sep 2009 13:51

QueryRecCount

Post by Zoner » Thu 03 Sep 2009 14:40

Hello,

1. According to the "TMSDataSetOptions.QueryRecCount Property" topic in help, QueryRecCount should work with these options:
FetchAll=True
NonBlocking=True
But it does not. It only works with FetchAll=False.
Did I misunderstood something?

2. Like QueryRecCount, we want to execute a query before opening the dataset to find some aggregates.
E.g: SELECT SUM(COL1), SUM(COL2), MIN(COL3) FROM (MSQuery.BaseSQL) AS T1

I think QueryRecCount runs a similar SQL:
SELECT COUNT(*) FROM (MSQuery.BaseSQL) AS T1

We want to execute a single SQL and get all the results in a single run: SELECT COUNT(*), SUM(COL1).....

Is there a way to set the RecordCount property manually like QueryRecCount does?
Or is it possible to implement a custom TMSQuery to modify the QueryRecCount's SQL text and behavior?

3. With FetchAll=False and QueryRecCount=True,
how can we find the number of fetched rows so far?

Thanks.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 04 Sep 2009 09:46

1. The QueryRecCount property works if FetchAll is False or NonBlocking is True. If FetchAll is True, you get control only after fetching all records, and in this case you get a record count without any additional query.

2. If FetchAll is True, TCustomDADataSet performs additional query to get record count for the opened SQL query and you don't need to do it yourself.

3. If QueryRecCount is True, you can't get the number of fetched rows.

Zoner
Posts: 6
Joined: Thu 03 Sep 2009 13:51

Post by Zoner » Sat 05 Sep 2009 15:46

Thanks for the reply, but your answers did not help.

As far as we see, QueryRecCount has some problems.
For example, when you delete a record with the Dataset.Delete method the recordcount remains same if QueryRecCount is True.

To solve the problem we derived a custom TMSQuery and override
the GetRecordCount event of TDataSet. It works even with FetchAll=True and NonBlocking=True !

We have tested this in many different cases and it works OK.
Could there be any side effects of this implementation?

Thanks.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 07 Sep 2009 07:18

We don't know what problems your implementation can cause. Can you give us the overridden QueryRecCount method?

Zoner
Posts: 6
Joined: Thu 03 Sep 2009 13:51

Post by Zoner » Mon 07 Sep 2009 16:45

The GetRecordCount is overriden and it is simple:

Code: Select all

  if FgioQueryRecCount then
    Result := FgioRecordCount
  else
    Result := inherited GetRecordCount;
In the DoBeforeOpen method, FgioRecordCount is set by the result of a query.
Then in AfterDelete and AfterPost events FgioRecordCount is decreased or increased.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 08 Sep 2009 08:19

Zoner wrote:As far as we see, QueryRecCount has some problems.
For example, when you delete a record with the Dataset.Delete method the recordcount remains same if QueryRecCount is True.
I can not reproduce the problem. Please, try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

Post Reply