Watch, Follow, &
Connect with Us

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

Welcome, Guest
Guest Settings

Thread: Cached Updates, Schema Adapter, Stored Procedures in MS SQL Server ????????

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

Permlink Replies: 0
Keith Marbach

Posts: 69
Registered: 8/21/01
Cached Updates, Schema Adapter, Stored Procedures in MS SQL Server ????????  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 2, 2017 8:03 AM
I had more words for the Subject line of this post (Identity Columns, DetailCascade, some curse words) but maybe it's just as well I ran out of room. I've been trying for weeks to get FireDac to work for me, but it has been very frustrating, and when I search on THE WHOLE INTERNET (Google), I don't find any help with the problems I'm having. So, once again, I post to this forum, hoping one of the experts can tell me what I'm doing wrong, or tell me that FireDac won't do what I'm expecting it to do. Any help is appreciated.

I'm using Delphi XE6, MS SQL Server 2012, cached updates on two datasets (TFDStoredProc) set up in master-detail relationship. I've gotten very close to having things working but then I run into a problem, near the end of testing, and cannot figure it out. Two errors that occur are:

[FireDAC][DatS]-33. Invalid foreign key [ForeignKeyConstraint]'.
'[FireDAC][DatS]-38. Cannot change table [] structure, when table has rows'.

With my current test project I'm getting the -38 error. I get this error when I try to open the detail dataset, with the SchemaAdapter property set to the one FDSchemaAdapter in the project. If I clear the detail's SchemaAdapter, then the table opens fine and all works like I want, except that the detail records don't save properly (foreign key column retains negative values, they don't update to correct identity column values from the master when changes are commited).

If you've gotten this far, what would help me the most is an example with these elements:

Firedac with master/detail datasets using TFDStoredProcs
Cached Updates
Schema Adapter
SQL Server database
Identity column (autoinc) for Master ID field (key column)

I don't want to maintain the SQL code in Delphi, I want to use stored procedures. So the TFDQuery example I found is helpful, but not conclusive as it does not seem to translate well to TFDStoredProc.
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02