Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: FireDAC & nested queries in Delphi 10 Seattle


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 17 - Last Post: Dec 21, 2016 1:59 AM Last Post By: Dmitry Arefiev
Mathias Burbach

Posts: 42
Registered: 12/8/99
FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 3, 2016 4:59 PM
Hello Folks!

We are about to migrate a rather large DataSnap server using FireDAC & MS SQL Server from Delphi XE7 to Delphi 10 Seattle. We have many test cases that are failing in Delphi 10 Seattle because the nested DataSet handling has changed in FireDAC in Delphi 10 Seattle. We used to use the fetch option Unidirectional for all our TFDQueries and we have a lot of nested datasets. In the past (e.g. Delphi XE7) the query for the nested dataset was executed for every master record. In Delphi 10 Seattle the DataSnap server only returns detail records for the first master record. Here is a sample:

qryDCATSCategory:
Select DCATSCategoryID, Description, [LineNo], UpdNo
From DCATSCategory
Order By [LineNo]

qryDCATSItem:
Select DCATSItemID, DCATSCategoryID, Description, [LineNo], UpdNo
From DCATSItem
Where DCATSCategoryID = :DCATSCategoryID
Order By [LineNo]

In between of the two TFDQueries sits a TDataSource pointing to qryDCATSCategory and qryDCATSItem.MasterSource is set to the TDataSource.

If I remove the fetch option Unidirectional from the detail query all is fine in Delphi 10 Seattle but we really don't want the overhead of a memory block to navigate inside the TFDQuery if all we need is to move to the next record. So Unidirectional served us quite well in Delphi XE7.

Here is the detail query as it is stored in the DFM file:
object qryDCATSItem: TFDQuery
  MasterSource = dscDCATSCategory
  MasterFields = 'DCATSCategoryID'
  DetailFields = 'DCATSCategoryID'
  Connection = conAIMS
  FetchOptions.AssignedValues = [evUnidirectional]
  FetchOptions.Unidirectional = True
  SQL.Strings = (
      'Select DCATSItemID, DCATSCategoryID, Description, [LineNo], UpdNo ' +
    'From DCATSItem'
    'Where DCATSCategoryID = :DCATSCategoryID'
    'Order By [LineNo]')
  Left = 296
  Top = 96
  ParamData = <
    item
      Name = 'DCATSCATEGORYID'
      DataType = ftInteger
      ParamType = ptInput
      Value = 1
    end>
  object qryDCATSItemDCATSItemID: TIntegerField
    FieldName = 'DCATSItemID'
    Origin = 'DCATSItemID'
    ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
    Required = True
  end
  object qryDCATSItemDCATSCategoryID: TIntegerField
    FieldName = 'DCATSCategoryID'
    Origin = 'DCATSCategoryID'
    Required = True
  end
  object qryDCATSItemDescription: TStringField
    DisplayWidth = 45
    FieldName = 'Description'
    Origin = 'Description'
    Required = True
    Size = 45
  end
  object qryDCATSItemLineNo: TSmallintField
    FieldName = 'LineNo'
    Origin = '[LineNo]'
    Required = True
  end
  object qryDCATSItemUpdNo: TIntegerField
    FieldName = 'UpdNo'
    Origin = 'UpdNo'
  end
end


Thanks for a short answer in advance.

Salut,
Mathias

Edited by: Mathias Burbach on Apr 4, 2016 10:00 AM
Mathias Burbach

Posts: 42
Registered: 12/8/99
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 3, 2016 5:19 PM   in response to: Mathias Burbach in response to: Mathias Burbach
Hello Folks!

If I change my TFDConnection settings and don't use fetch option Unidirectional=True but set fetch option CursorKind = ckForwardOnly it works as expected in Delphi 10 Seattle.

Can anyone help me to understand what is wrong with Unidirectional=True in Delphi 10 Seattle?

Salut,
Mathias
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 7, 2016 12:39 AM   in response to: Mathias Burbach in response to: Mathias Burbach
This sounds like a bug. If so, then please register it at
https://quality.embarcadero.com/secure/Dashboard.jspa

--
With best regards,
Dmitry
Mathias Burbach

Posts: 42
Registered: 12/8/99
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 7, 2016 1:53 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
This sounds like a bug. If so, then please register it at
https://quality.embarcadero.com/secure/Dashboard.jspa
Thanks, Dmitry. I have done exactly this. Here is the URL for the Quality Central entry:
https://quality.embarcadero.com/browse/RSP-14116

Salut,
Mathias

Edited by: Mathias Burbach on Apr 7, 2016 6:53 PM, changed wrong URL
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 7, 2016 7:41 AM   in response to: Mathias Burbach in response to: Mathias Burbach
Thank you. Reproduced and fixed.
If required and you have FireDAC sources, then I can provide patch.

--
With best regards,
Dmitry
Mathias Burbach

Posts: 42
Registered: 12/8/99
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 7, 2016 1:32 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Thank you. Reproduced and fixed.
If required and you have FireDAC sources, then I can provide patch.

Hello Dmitry,

Thanks for your quick reply. Yes, I do have the source code of FireDAC as I use RAD Studio Architect. Do you want to attach the patch to the JIRA issue in Quality Central?

Salut,
Mathias
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 8, 2016 1:23 AM   in response to: Mathias Burbach in response to: Mathias Burbach
To fix the issue:
* open Data.DB.pas
* locate there TDataSet.First method
* find there lines:
  if IsUniDirectional then
  begin
    if not BOF then

and replace them with:
  if IsUniDirectional then
  begin
    if not BOF or IsEmpty then

* add path to Data.DB.pas to your IDE Library Path

I will appreciate if you will post here your feedback regarding this patch.

--
With best regards,
Dmitry
Mathias Burbach

Posts: 42
Registered: 12/8/99
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 8, 2016 1:42 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
I will appreciate if you will post here your feedback regarding this patch.

Hello Dimirty,

That change fixed our problems and we can revert to FetchOption Unidirectional, which keeps the overhead in our DataSnap server low.

Thanks a lot!

Salut,
Mathias

Edited by: Mathias Burbach on Apr 8, 2016 6:43 PM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 8, 2016 2:45 AM   in response to: Mathias Burbach in response to: Mathias Burbach
Great.

But please keep your eyes open, because it is too generic change,
which may affect other areas.

--
With best regards,
Dmitry
Mathias Burbach

Posts: 42
Registered: 12/8/99
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 8, 2016 3:32 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
But please keep your eyes open, because it is too generic change, which may affect other areas.
No worries, Dmitry. We have a DUnit test project with with 670 test cases for our application server & 711 test cases for the client-side. If there were any side effects, they would surface. I just executed the all app server tests again and none of the test cases had a problem. Even the test cases for all the nested datasets passed.

I think this really settled the problem.

Salut,
Mathias

Edited by: Mathias Burbach on Apr 8, 2016 8:33 PM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 8, 2016 5:21 AM   in response to: Mathias Burbach in response to: Mathias Burbach
You have assured me, thank you.

PS: Due to different reasons, at moment I cannot test this change on Embarcadero test farm.

--
With best regards,
Dmitry
Vladimir Ulchenko

Posts: 248
Registered: 1/12/00
Re: FireDAC & nested queries in Delphi 10 Seattle [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 8, 2016 5:47 AM   in response to: Mathias Burbach in response to: Mathias Burbach
On Fri, 8 Apr 2016 03:33:14 -0700, Mathias Burbach <> wrote:

No worries, Dmitry. We have a DUnit test project with with 670 test cases for our application server & 711 test cases for the client-side. If there were any side effects, they would surface. I just executed the all app server tests again and none of the test cases had a problem. Even the test cases for all the nested datasets passed.

that's quite impressive
could you please describe those tests, at least to some extent?
thank you

--
Vladimir Ulchenko aka vavan
Mathias Burbach

Posts: 42
Registered: 12/8/99
Re: FireDAC & nested queries in Delphi 10 Seattle [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 8, 2016 1:41 PM   in response to: Vladimir Ulchenko in response to: Vladimir Ulchenko
Vladimir Ulchenko wrote:
that's quite impressive
could you please describe those tests, at least to some extent?
thank you

Hello Vladimir,

the principles of our DUnit testing approach are explained in some slides & source code I used for a presentation at the Australian Developer Conference organised by Embarcadero Australia in October 2015. You will find the slides here:
http://ausdd.embarcadero-info.com/data/DUnit_DUnitX.pdf

The source code is available in a public Git repository here:
https://bitbucket.org/mathias_burbach/dunit-on-db-apps

In a nutshell the idea is to test the server methods separately from the client-side datamodules and have a test helper class, which can create a fixture quickly in your test DB. Strictly speaking this is no longer a unit test but rather an integration test since the client-side tests not only test the datamodules but also the TCP connection to the DataSnap server and the DB connection of the DataSnap server to the DB (here: Firebird).

Download the sample projects from BitBucket and have a look at the use of TdmoTestHelper throughout the test projects.

In our real world DataSnap project, for which I can't share the source code, we do have tests for all CRUD operations against DataSetProviders and any public method of a server module. Most of the test cases are then mirrored on the client-side as the DataSnap client will at some stage consume the DataSnap server methods & exported DataSetProviders.

Don't hesitate to ask here or send me an e-mail. You will find my e-mail in the slides.

Salut,
Mathias
Vladimir Ulchenko

Posts: 248
Registered: 1/12/00
Re: FireDAC & nested queries in Delphi 10 Seattle [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 11, 2016 4:32 AM   in response to: Mathias Burbach in response to: Mathias Burbach
On Fri, 8 Apr 2016 13:41:05 -0700, Mathias Burbach <> wrote:

the principles of our DUnit testing approach are explained in some slides & source code I used for a presentation at the Australian Developer Conference organised by Embarcadero Australia in October 2015. You will find the slides here:
http://ausdd.embarcadero-info.com/data/DUnit_DUnitX.pdf

The source code is available in a public Git repository here:
https://bitbucket.org/mathias_burbach/dunit-on-db-apps

thanks for your answer, that's really interesting

In a nutshell the idea is to test the server methods separately from the client-side datamodules and have a test helper class, which can create a fixture quickly in your test DB. Strictly speaking this is no longer a unit test but rather an integration test since the client-side tests not only test the datamodules but also the TCP connection to the DataSnap server and the DB connection of the DataSnap server to the DB (here: Firebird).

I've been using midas (in its original incarnation via DCOM and/or rewritten socket transport as I keep using rs2007 and not that fond of
"modern" datasnap implementation) since 1999 but never got to the point of making unit tests for all those projects

In our real world DataSnap project, for which I can't share the source code, we do have tests for all CRUD operations against DataSetProviders and any public method of a server module. Most of the test cases are then mirrored on the client-side as the DataSnap client will at some stage consume the DataSnap server methods & exported DataSetProviders.

Don't hesitate to ask here or send me an e-mail. You will find my e-mail in the slides.

thank you again

--
Vladimir Ulchenko aka vavan
Mathias Burbach

Posts: 42
Registered: 12/8/99
Re: FireDAC & nested queries in Delphi 10 Seattle  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 12, 2016 8:29 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Hello Dmitry,

we just converted our big DataSnap project to Delphi 10.1 Berlin Update 2 and the UniDirectional fetch option is still suffering from the same problem. I removed the old copy of Data.DB.pas from the project to get whatever is in Delphi 10.1 Berlin Update 2. But then the same problem came up again. It would be nice if this fix could make it into the next update pack or version of Delphi.

Salut,
Mathias

Dmitry Arefiev wrote:
To fix the issue:
* open Data.DB.pas
* locate there TDataSet.First method
* find there lines:
  if IsUniDirectional then
  begin
    if not BOF then

and replace them with:
  if IsUniDirectional then
  begin
    if not BOF or IsEmpty then

* add path to Data.DB.pas to your IDE Library Path

I will appreciate if you will post here your feedback regarding this patch.

--
With best regards,
Dmitry

Edited by: Mathias Burbach on Dec 13, 2016 3:29 PM
Jo Reiter

Posts: 10
Registered: 10/18/99
Re: FireDAC & nested queries in Delphi 10 Seattle [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 14, 2016 5:44 AM   in response to: Mathias Burbach in response to: Mathias Burbach
Hello Dmitry,

I fear Dmitry Arefiev is no longer in charge for FireDac at Embarcadero.

btw I'm suffering on the same problem...
Mathias Burbach

Posts: 42
Registered: 12/8/99
Re: FireDAC & nested queries in Delphi 10 Seattle [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 14, 2016 1:26 PM   in response to: Jo Reiter in response to: Jo Reiter
Hello Jo,

Thanks for your reply.

Jo Reiter wrote:
I fear Dmitry Arefiev is no longer in charge for FireDac at Embarcadero.
btw I'm suffering on the same problem...
Yes, I had logged this as an issue in Embarcadero's JIRA on the 7th April 2016
(see also here: https://quality.embarcadero.com/browse/RSP-14116)

I have now added a comment that the problem is not fixed in Delphi 10.1 Berlin Update 2 (Version 24.0.25048.9432).

Salut,
Mathias
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC & nested queries in Delphi 10 Seattle [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 21, 2016 1:59 AM   in response to: Mathias Burbach in response to: Mathias Burbach
I have now added a comment that the problem is not fixed in Delphi 10.1 Berlin Update 2 (Version 24.0.25048.9432).

This fix will be included only into DX 10.2. I doubt that it will be included into any of 10.1 updates.

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

Server Response from: ETNAJIVE02