Question about FireDac / DataSnap / FireBird and Identity Fields.
Question about FireDac / DataSnap / FireBird and Identity Fields.
I've been trying out a lot of things by now and I just cannot seem to get this working the way it should. I have a DataSnap server where I have a TFDQuery which I use to fetch / edit / add a single record in the CUSTOMERS table. The ID field in that TFDQuery is an Identity field in the underlying FireBird database and it gets recognised by delphi when I add a persistent field (it adds a TFDAutoIncField).
I did add TFDUpdateSQL component to the mix in which I've set up the INSERT statement to be RERTURNING the ID Field.
Client side I have TFDMemTables whith CachedUpdates, and I even set the AutoIncFields and KeyFields to 'ID'.
Now ... when I edit / delete / add records through that dataset, they seem to be getting into the database correctly. As long as the record isn't posted, the ID field has a negative value. But once posted I find my record in my database with the correct ID field value.
What I would like to have next is the new value of that ID field in the client, but there seems to be no way for me to get that working the way I should. Already played with some properties, tried to .RefreshRecord on my dataset, but nothing else works.
Does anyone here have an idea on how I can achieve this? I know the server can get the new value. since I tried it with a call to GetLastAutoGenValue on the connection and that seems to show the correct value for the new record. Of course I'm not 100% sure that's safe in a multi user environment. So my question is... whats the best approach to get the newly generated identity value for my new record in my client? Any ideas?
I've been trying out a lot of things by now and I just cannot seem to get this working the way it should. I have a DataSnap server where I have a TFDQuery which I use to fetch / edit / add a single record in the CUSTOMERS table. The ID field in that TFDQuery is an Identity field in the underlying FireBird database and it gets recognised by delphi when I add a persistent field (it adds a TFDAutoIncField).
I did add TFDUpdateSQL component to the mix in which I've set up the INSERT statement to be RERTURNING the ID Field.
Client side I have TFDMemTables whith CachedUpdates, and I even set the AutoIncFields and KeyFields to 'ID'.
Now ... when I edit / delete / add records through that dataset, they seem to be getting into the database correctly. As long as the record isn't posted, the ID field has a negative value. But once posted I find my record in my database with the correct ID field value.
What I would like to have next is the new value of that ID field in the client, but there seems to be no way for me to get that working the way I should. Already played with some properties, tried to .RefreshRecord on my dataset, but nothing else works.
Does anyone here have an idea on how I can achieve this? I know the server can get the new value. since I tried it with a call to GetLastAutoGenValue on the connection and that seems to show the correct value for the new record. Of course I'm not 100% sure that's safe in a multi user environment. So my question is... whats the best approach to get the newly generated identity value for my new record in my client? Any ideas?
Workaround: Use a Guid as identifier field. On insert create a Guid at client program and set the identifier field before post. So everybody (client, middleware, database) is happy. You can go add some detail records using this Guid as master identifier.
ReplyDeleteJean-Marc Kiener Wish I had something to say about the DB Structure. Sadly ... I don't and I'll have to live with the Identity fields.
ReplyDeleteIIRC, You need to handle the TDatasetProvider.AfterUpdateRecord event. Get the ID and send it back as part of the DeltaDS. You also have to set TDatasetProvider.Options.poPropogateChanges = True.
ReplyDeleteRandy Tapson That's indeed how we would do it if we were using the TClientDataSet / DataSet Provider approach. But we're using the new DataSnap technology in FireDAC now. I guess I'll have a look to see if there is anything similar in there.
ReplyDelete