Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: Possible to use TFDStoredProc in cached mode for detail table in M/D



Permlink Replies: 2 - Last Post: Jul 29, 2017 12:31 AM Last Post By: Dmitry Arefiev
Keith Marbach

Posts: 69
Registered: 8/21/01
Possible to use TFDStoredProc in cached mode for detail table in M/D
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 13, 2017 7:21 PM
I have been unable to find any help or prior thread on whether or not it is possible to use TFDStoredProc as the detail dataset in a Master/Detail relationship. I have experimented quite a bit, and it works almost 100%, but at the last step for me (which is to set FetchOptions.DetailCascade to True for the detail table (to copy the value of new identity column from master to the detail rows when I applyUpdates), when I try to run with DetailCascade set to true, I get an error when I try to open the detail table:


Project1

[FireDAC][DatS]-33. Invalid foreign key [ForeignKeyConstraint].

OK

Below is some of my code and the set up of the two datasets.

object FDStoredProc1: TFDStoredProc // master table
CachedUpdates = True
Connection = FDConnection1
SchemaAdapter = FDSchemaAdapter1
ResourceOptions.AssignedValues = [rvParamCreate]
UpdateOptions.UpdateTableName = 'Lead'
UpdateOptions.KeyFields = 'ID'
StoredProcName = 'JCSPGetLeads'
...
object FDStoredProc1ID: TFDAutoIncField
FieldName = 'ID'
Origin = 'ID'
ProviderFlags = [pfInWhere, pfInKey]
ReadOnly = True
end
object FDStoredProc1Name: TStringField
FieldName = 'Name'
Origin = 'Name'
Size = 50
end
end

object FDStoredProc2: TFDStoredProc // detail table
CachedUpdates = True
IndexFieldNames = 'LeadID'
MasterSource = DataSource1
MasterFields = 'ID'
Connection = FDConnection1
SchemaAdapter = FDSchemaAdapter1
FetchOptions.AssignedValues = [evDetailCascade, evDetailServerCascade]
FetchOptions.DetailCascade = True
FetchOptions.DetailServerCascade = True // the server deletes rows if the master is deleted
ResourceOptions.AssignedValues = [rvUnifyParams]
ResourceOptions.UnifyParams = True
ParamData = <
item
Position = 1
Name = '@RETURN_VALUE'
DataType = ftInteger
ParamType = ptResult
end
item
Position = 2
Name = '@ID'
DataType = ftInteger
ParamType = ptInput
end>
object FDStoredProc2LeadID: TIntegerField
FieldName = 'LeadID'
Origin = 'LeadID'
ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
Required = True
end
object FDStoredProc2ContactID: TIntegerField
FieldName = 'ContactID'
Origin = 'ContactID'
Required = True
end
end

procedure TForm1.btnOpen(Sender: TObject);
begin
FDConnection1.Connected := true;
FDStoredProc1.open;
FDStoredProc2.open; // on this line is where the error is thrown. If I set DetailCascade to false the error goes away, but detail column does not update with master identity value when I apply updates
end;

procedure TForm1.btnSave(Sender: TObject);
begin
with FDStoredProc2 do if state in [dsEdit, dsInsert] then post;
with FDStoredProc1 do if state in [dsEdit, dsInsert] then post;

FDStoredProc1.ApplyUpdates(0);
FDStoredProc2.ApplyUpdates(0);
FDStoredProc1.CommitUpdates;
FDStoredProc2.CommitUpdates;
end;
Keith Marbach

Posts: 69
Registered: 8/21/01
Re: Possible to use TFDStoredProc in cached mode for detail table in M/D
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 27, 2017 11:44 AM   in response to: Keith Marbach in response to: Keith Marbach
Sorry if I was unclear. The question is, can a TFDStoredProc be used as a detail dataset in a master/detail relationship, and if so, are there examples on how to make it work? My goal is to have a data entry screen with multiple master/detail relationships and to cache all changes until user hits a submit button. Thanks for your feedback.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Possible to use TFDStoredProc in cached mode for detail table in M/D
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 29, 2017 12:31 AM   in response to: Keith Marbach in response to: Keith Marbach
TFDStoredProc will work as a detail dataset, but DetailCascade=True will not work.

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

Server Response from: ETNAJIVE02