We are using Delphi front end and Superr HTTP, with C# back end with MS SQL2008 R2.
We went through the example of Master Detail, the Orders example. Everything worked fin with the sql in the schema and relationships setup for creation order. We then moved the sql to stored procs and then discovered the ID of the master does not get populated correctly to the child detail record.
We tried a couple of different ways, Firstly specifying the ID as an Out parameter, then not specify the new ID as an Out parameter.
How do we use stored procs in-place of schema sql to use master detail correctly?
Unfortunately I wasn’t able to reproduce the issue you reported. Master/detail data was inserted fine for both SQL and StoredProcedure-based tables. Could you provide us a simple testcase so we’ll be able to reproduce and fix this issue?
So you just setup your relationships and rules in schema, and for sql procedure you do / or do not have to output new ID? - does the relationship take care of this?
Yes. You don’t need to output new ID.
For updating master/detail tables it’s enough to have relationship and standart delta-commands for each table in the schema.
However there is one caveat you should be aware of:
Data Abstract uses @@IDENTITY to retrieve last auto inc value from MS SQL Server. So if you perform more than 1 insert in the stored procedure you have to insert data in the master table AFTER any other inserts that affetct tables with auto inc fields
Thanks for the quick response…OK great, I didn’t know that it works that way, but it makes sense, and it also makes sense as to why sometimes things get screwed up.
What if I need to call 3 other scripts after a master insert that setup other tables automatically and I need the master id, once they finish I can’t set the @@IDENTITY again.
I’ll have to put anything that relies on this ID into a another script and call as command after the applyupdates I guess - is this the correct approach for that situation?
Hi, I set up a detailed client/server with DB scripts, But I replicated a issue of not setting ID based on relationship in schema using just schema sql - not stored procs. attached is the sample - on creating an account the detail record as contact which has 2 addresses does not get the ID assigned, it gets -1.
Hi,
As I see from your testcase, AccountContact relation is between Account and ContactJob tables, but on the client you operate with Contact (tbl_Contact) table. After changing Detail table from ContactJob to Contact in the AccountContact relation MasterDataId is inserted fine.