Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: Problem with "incomplete" opening of TFDQuery?


This question is not answered. Helpful answers available: 0. Correct answers available: 1.


Permlink Replies: 8 - Last Post: Oct 23, 2017 10:28 PM Last Post By: Elias Sabbagh
Elias Sabbagh

Posts: 11
Registered: 2/28/00
Problem with "incomplete" opening of TFDQuery?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 20, 2017 2:53 PM
Hello all--

Using Delphi Rad Studio 10 Seattle 23.0.21418.4207.

Our application switches between three different SQL SELECT statements, each of which is represented by a TFDQuery. Any of the three TFDQueries may be linked to a DevExpress grid control at run time by a user's choice. Two of the TFDQueries behave as expected: when the user switches from one to the other, the correct records are displayed in the grid. When our team recently added the third TFDQuery, it simply did not display records in the grid when opened. We know that the SQL statement is correct, since it executes correctly on the server. We also know that the third TFDQuery has records in its buffer, since we are able to set a breakpoint in an AfterOpen event and inspect the field values of the first record. We even wrote some test code in the AfterOpen even handler where we stepped through the recordset using First and Next, and we were able to count all fifty-five expected records. However, the grid will not display any data, and this is the case even after checking the TDataSource connection between the third TFDQuery and the grid. The RecordCount property of the first two queries reflects the total number of records pulled off of the server -- three and fifty-five, respectively. The RecordCount of the third query is returning fifty, even though the server sent over fifty-five records. Finally, by using the FireDAC monitor, we noticed that the chatter between FireDAC and the server was different for the third query: FireDAC stopped fetching records after the fiftieth, and apparently hasn't closed the cursor on the server.

The property settings of the three TFDQuery components are exactly the same, except for their names and their SQL statements. What could cause the difference in the way the third query fetches records and makes them available to other controls?

Thanks,

Elias Sabbagh
Victor Technologies, LLC
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Problem with "incomplete" opening of TFDQuery?
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 20, 2017 10:18 PM   in response to: Elias Sabbagh in response to: Elias Sabbagh
Elias Sabbagh wrote:

[snip]

Finally, by using the FireDAC monitor, we noticed that the chatter
between FireDAC and the server was different for the third query:
FireDAC stopped fetching records after the fiftieth, and apparently
hasn' t closed the cursor on the server.

The property settings of the three TFDQuery components are exactly
the same, except for their names and their SQL statements. What
could cause the difference in the way the third query fetches records
and makes them available to other controls?

You should investigate more closely the Fetchoptions property. There is
the Mode subproperty with default values fmOnDemand change it to
fmAll. With the default values the query reads the first Rowsetsize
records from the cursor (you can guess the default value for this
subproperty is 50).

Unfortunately I cannot comment about DevExpress grid control, maybe
somebody else can give you hint about that control.

Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Problem with "incomplete" opening of TFDQuery?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 20, 2017 10:22 PM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Another subproperty of Fetchoptions you should investigate on the third
query is the RecordCountMode the default value is cmVisible try to set
it to cmTotal.
Elias Sabbagh

Posts: 11
Registered: 2/28/00
Re: Problem with "incomplete" opening of TFDQuery?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 20, 2017 10:26 PM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Lajos Juhasz wrote:
Elias Sabbagh wrote:

[snip]

Finally, by using the FireDAC monitor, we noticed that the chatter
between FireDAC and the server was different for the third query:
FireDAC stopped fetching records after the fiftieth, and apparently
hasn' t closed the cursor on the server.

The property settings of the three TFDQuery components are exactly
the same, except for their names and their SQL statements. What
could cause the difference in the way the third query fetches records
and makes them available to other controls?

You should investigate more closely the Fetchoptions property. There is
the Mode subproperty with default values fmOnDemand change it to
fmAll. With the default values the query reads the first Rowsetsize
records from the cursor (you can guess the default value for this
subproperty is 50).

Unfortunately I cannot comment about DevExpress grid control, maybe
somebody else can give you hint about that control.


Yes, we've played around with all of the different fetch options, and tampered with the RowSetSize values on the third query (and on the connection as well). We've also tried to force the query to fetch everything by calling FetchAll() and other methods. Nothing helps. In fact, using the default values on the other two queries works fine, so we've reluctantly given up on changing any of those settings on the third after testing them.

The DevExpress grid is not the issue, since we've already determined that the data is only partially loaded into the third query by the time of the AfterOpen event, which is far before we connect the grid to the third query's TDataSource.

Any other ideas? It really looks like some sort of internal bug.

Edit: I should mention that we're connecting to an embedded Firebird 2.5 client.

Edited by: Elias Sabbagh on Sep 20, 2017 10:28 PM
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Problem with "incomplete" opening of TFDQuery?
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 21, 2017 12:02 AM   in response to: Elias Sabbagh in response to: Elias Sabbagh
Since Delphi XE8 I have to change some queries when two queries are executed in a form with one attached to a grid. More and more I add the "with NO LOCK" inside the select query to prevent the query locking the table which stopped other processes. I also see this with a combination of a display (select) query and update queries on the same table. (this count also for update triggers on the table itself) I'm also looking into this Fetchoptions property.
Elias Sabbagh

Posts: 11
Registered: 2/28/00
Re: Problem with "incomplete" opening of TFDQuery?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 21, 2017 7:27 AM   in response to: Robert Triest in response to: Robert Triest
Robert Triest wrote:
Since Delphi XE8 I have to change some queries when two queries are executed in a form with one attached to a grid. More and more I add the "with NO LOCK" inside the select query to prevent the query locking the table which stopped other processes. I also see this with a combination of a display (select) query and update queries on the same table. (this count also for update triggers on the table itself) I'm also looking into this Fetchoptions property.

I'll try fiddling around with locking, but I'm using Firebird, and I haven't had to explicitly handle locks -- everything's done at the transaction level, and the select statements are under READ COMMITTED transactions with no other updates happening. Still, this might be a fruitful avenue to pursue.
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Problem with "incomplete" opening of TFDQuery? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 21, 2017 1:54 AM   in response to: Elias Sabbagh in response to: Elias Sabbagh
Elias Sabbagh wrote:

Any other ideas? It really looks like some sort of internal bug.

Edit: I should mention that we're connecting to an embedded Firebird
2.5 client.

I doubt that it's a bug in FD. If it's a bug why the other 2 queries
not affected by it. Maybe you could try to place a stock dbgrid and
check if it can display the data.

But it can be a bug, I am still using XE5 so has no real experience
with Seattle.
Elias Sabbagh

Posts: 11
Registered: 2/28/00
Re: Problem with "incomplete" opening of TFDQuery? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 21, 2017 7:36 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Lajos Juhasz wrote:
Elias Sabbagh wrote:

Any other ideas? It really looks like some sort of internal bug.

Edit: I should mention that we're connecting to an embedded Firebird
2.5 client.

I doubt that it's a bug in FD. If it's a bug why the other 2 queries
not affected by it. Maybe you could try to place a stock dbgrid and
check if it can display the data.

But it can be a bug, I am still using XE5 so has no real experience
with Seattle.

It's precisely because the two other queries aren't showing this behavior that I fear a bug :). Since only the SQL is different between the three TFDQuery components, perhaps there's some weird corner case in the Firebird/FireDAC interaction that's being exposed. But the SQL is hardly exotic: it's selecting fields from a view with a where clause that takes a single parameter, and there's an order by clause. You'd think that that SQL would be simple to prepare and manage, and as it turns out we do get some records -- just the first 50, but they don't seem to be available for consumption through the TDataSource. When monitoring the first two queries, FireDAC monitor reports that each record was fetched, and then prints a log message saying that ".EOF reached," whereupon further client-side events such as the TDataSource's OnDataChange event is fired. With the third query, the ".EOF reached" diagnostic is never logged by the monitor and the OnDataChange event never fires. The fetching just stops, incomplete. It's very strange...
Elias Sabbagh

Posts: 11
Registered: 2/28/00
Re: Problem with "incomplete" opening of TFDQuery?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 23, 2017 10:28 PM   in response to: Elias Sabbagh in response to: Elias Sabbagh
OK folks, I have gathered some more information to see if this issue can be resolved. The data, stored in a .zip file, is [here|http://www.sabbagh.com/example.zip] .

I have three FireDAC monitor logs that capture the moment one of the three queries of interest is opened, up through the firing of an AfterOpen event handler, where we pause the FireDAC monitor. The three different queries are switched by a user clicking a radio button, and I also provide three screenshots showing the results of the queries as displayed in a DevExpress grid control.

The three query choices are meant to show different ways of inspecting "products" that our electromagnetics modeling software computes. The details are unimportant, but briefly, "products" are associated with different parameter combinations used to generate them, and each parameter combination is called a "datapoint." The user can select some datapoints on a different form, and a query will gather the products associated with that set of datapoints on the form shown in the screenshots. Call this scenario case 1. Alternatively, the user can simply ask for the products associated with all of the datapoints to be retrieved without selecting any of the datapoints. Call this scenario case 2. Finally, there is a way to view products organized by what is known as a "product context," which we call case 3.

Each of these cases is represented by a standard FDQuery component pulled off of the deign palette and dropped onto a data module. There have been no properties manipulated other than the usual setting of connection, transaction, and SQL statement. This is why it is so confusing to see cases 1 and 3 behave as expected, but case 2 fail.

I have demonstrated what I think is a bug by working with my software to create some products and display them in the three different ways. The three cases are logged and screenshotted under the names SelectedDatapoints.log, AllDatapoints.log, and SelectedContexts.log. For cases 1 and 3, the products are fetched and the first record is chosen, and additional processing of other slave queries which rely on the master product query are opened, as can be seen in the log. We see in the corresponding screenshots that the DevExpress grids are loaded with the expected 63 and 8 records, respectively. These datasets are filled to the brim with all of the available records, without playing around with different fetch modes or rowset sizes (the default value of which has been left alone at 50 for all three cases). Breaking with the debugger in the AfterOpen handler allows us to inspect the field values, and all is well. For case 2, some (?) products are fetched (probably 50), but the dataset looks empty to the DevExpress grid, and the first record is apparently not set, since no slave queries are reset and reopened. Confusingly, inspecting the dataset with the debugger shows that the fields contain data which looks like the log's first record fetched, so something must have happened.

The only difference between the three queries is the SQL, which are doing plain-vanilla select statements with simple where clauses, or selecting from views with where clauses.

Can anyone glean any clues as to what the problem with case 2 might be from inspecting the FireDAC log?
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02