Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: FDQuery->RecordCount Incorrect


This question is answered.


Permlink Replies: 4 - Last Post: Sep 4, 2016 6:33 AM Last Post By: Dmitry Arefiev
Earl Staley

Posts: 99
Registered: 4/9/07
FDQuery->RecordCount Incorrect  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 26, 2016 1:26 PM
I have a C++ Berlin database program using FireDAC components to an SQLite database. I have a DBGrid using a DataSource looking at the the FDQuery. The key elements look like this:

FDTable1->"TableXXX";
DBGrid1->DataSource=DataSource1;
DataSource1->DataSet=FDQuery1;
FDQuery1->SQL->Text="SELECT * FROM TableXXX";


The above is not the actual code because the values were assigned using the Object Inspector.

My problem is that FDQuery1->RecordCount will only return the correct record count if TableXXX has 50 or fewer records. If TableXXX has more than 50 records, FDQuery1->RecordCount returns 50 regardless of the number of records in TableXXX.

Even though FDQuery1->RecordCount returns 50, the correct number of records is displayed in the DBGrid.

I wrote a little troubleshooting program with only the minimum amount of objects and code to demonstrate the problem and FDQuery1->RecordCount still maxs out at 50. Here is all of the written code in the demo program:

FDTable1->Open();
Edit1->Text=FDTable1->RecordCount;
Edit2->Text=FDQuery1->RecordCount;


The component configurations and SQL->Text are identical to the setup for my original program shown above.

When the program runs, Edit1->Text shows 175 and Edit2->Text shows 50.

Why does FDQuery1->RecordCount max out at 50?

Is it possibly some quirk with SQLite databases or FireDAC or an error in my program?

Thank you...
Earl Staley

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: FDQuery->RecordCount Incorrect
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 27, 2016 3:59 PM   in response to: Earl Staley in response to: Earl Staley
Earl Staley wrote:
I have a C++ Berlin database program using FireDAC components to an SQLite database. I have a DBGrid using a DataSource looking at the the FDQuery. The key elements look like this:

FDTable1->"TableXXX";
DBGrid1->DataSource=DataSource1;
DataSource1->DataSet=FDQuery1;
FDQuery1->SQL->Text="SELECT * FROM TableXXX";


The above is not the actual code because the values were assigned using the Object Inspector.

My problem is that FDQuery1->RecordCount will only return the correct record count if TableXXX has 50 or fewer records. If TableXXX has more than 50 records, FDQuery1->RecordCount returns 50 regardless of the number of records in TableXXX.

Even though FDQuery1->RecordCount returns 50, the correct number of records is displayed in the DBGrid.

I wrote a little troubleshooting program with only the minimum amount of objects and code to demonstrate the problem and FDQuery1->RecordCount still maxs out at 50. Here is all of the written code in the demo program:

FDTable1->Open();
Edit1->Text=FDTable1->RecordCount;
Edit2->Text=FDQuery1->RecordCount;


The component configurations and SQL->Text are identical to the setup for my original program shown above.

When the program runs, Edit1->Text shows 175 and Edit2->Text shows 50.

Why does FDQuery1->RecordCount max out at 50?

Is it possibly some quirk with SQLite databases or FireDAC or an error in my program?

Thank you...
Earl Staley


Record counts are rarely able to be given from the server side on select SQLs.
This means RecordCount tends to represent the total number of records fetched so
far not the total number of records in the result set (server side).

So either do a FetchAll before the record count or run a separate SQL that does
a count(*) with the same where clause to get the RecordCount. Depending on the
query this can be time expensive which is why most component sets do not do this
by default, you know when it is safe and not safe to do this method.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Earl Staley

Posts: 99
Registered: 4/9/07
Re: FDQuery->RecordCount Incorrect  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 27, 2016 5:20 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff,

Thank you for your reply.

The DataModule1->FDQuery1->FetchAll(); statement corrected the RecordCount problem. However I now have another problem. Here is my code:

	DataModule1->FDQuery1->Open();
	DataModule1->FDQuery1->FetchAll();
	DataModule1->FDQuery1->First();
	int TimesLooped=0;
	for (int i = 0; i < DataModule1->FDQuery1->RecordCount; i++) {
		if (!VarIsNull(DataModule1->FDQuery1->Fields->FieldByName("TRNAMT")
			->Value)) {
			DataModule1->FDQuery1->Edit();
			RunningBal = RunningBal + DataModule1->FDQuery1->Fields->FieldByName
				("TRNAMT")->Value;
			DataModule1->FDQuery1->Fields->FieldByName("Balance")->Value =
				RunningBal;
			DataModule1->FDQuery1->Post();
			DataModule1->FDQuery1->Next();
		}
		TimesLooped++;
	}
	DataModule1->FDConnection1->StartTransaction();
	int iErrors = DataModule1->FDQuery1->ApplyUpdates(2);
 
	if (iErrors == 0) {
		DataModule1->FDQuery1->CommitUpdates();
		DataModule1->FDConnection1->Commit();
	}


I placed the TimesLooped variable in to check how many times the loop was run. The loop ran 175 times when DataModule1->FDQuery1->RecordCount was 50 (prior to inserting DataModule1->FDQuery1->FetchAll()) and it ran the same number of times (175) after DataModule1->FDQuery1->FetchAll() was inserted.

I get an error here if the RecordCount exceeds 50:

int iErrors = DataModule1->FDQuery1->ApplyUpdates(2);


Is there a way to remove the apparent 50 record limitation on ApplyUpdates?

Thank you...
Earl Staley
Earl Staley

Posts: 99
Registered: 4/9/07
Re: FDQuery->RecordCount Incorrect  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 28, 2016 1:21 PM   in response to: Earl Staley in response to: Earl Staley
Earl Staley wrote:
Jeff,

Thank you for your reply.

The DataModule1->FDQuery1->FetchAll(); statement corrected the RecordCount problem. However I now have another problem. Here is my code:

	DataModule1->FDQuery1->Open();
	DataModule1->FDQuery1->FetchAll();
	DataModule1->FDQuery1->First();
	int TimesLooped=0;
	for (int i = 0; i < DataModule1->FDQuery1->RecordCount; i++) {
		if (!VarIsNull(DataModule1->FDQuery1->Fields->FieldByName("TRNAMT")
			->Value)) {
			DataModule1->FDQuery1->Edit();
			RunningBal = RunningBal + DataModule1->FDQuery1->Fields->FieldByName
				("TRNAMT")->Value;
			DataModule1->FDQuery1->Fields->FieldByName("Balance")->Value =
				RunningBal;
			DataModule1->FDQuery1->Post();
			DataModule1->FDQuery1->Next();
		}
		TimesLooped++;
	}
	DataModule1->FDConnection1->StartTransaction();
	int iErrors = DataModule1->FDQuery1->ApplyUpdates(2);
 
	if (iErrors == 0) {
		DataModule1->FDQuery1->CommitUpdates();
		DataModule1->FDConnection1->Commit();
	}


I placed the TimesLooped variable in to check how many times the loop was run. The loop ran 175 times when DataModule1->FDQuery1->RecordCount was 50 (prior to inserting DataModule1->FDQuery1->FetchAll()) and it ran the same number of times (175) after DataModule1->FDQuery1->FetchAll() was inserted.

I get an error here if the RecordCount exceeds 50:

int iErrors = DataModule1->FDQuery1->ApplyUpdates(2);


Is there a way to remove the apparent 50 record limitation on ApplyUpdates?

Thank you...
Earl Staley

The problem was not in the ApplyUpdates method. The problem was in the ModifySQL statement. See the following thread:

https://forums.embarcadero.com/thread.jspa?threadID=229247&tstart=0

Regards...
Earl Staley
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FDQuery->RecordCount Incorrect  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 4, 2016 6:33 AM   in response to: Earl Staley in response to: Earl Staley
1) http://docwiki.embarcadero.com/Libraries/Berlin/en/FireDAC.Stan.Option.TFDFetchOptions.RecordCountMode
2) Instead of
for (int i = 0; i < DataModule1->FDQuery1->RecordCount; i++)

you should use
while (! DataModule1->FDQuery1->Eof)


--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02