Multiple SQL commands for a single insert

HI Jpheh,

Are you establishing the relationship with in the CDT, if this is the case when you use write to data store entity to insert the parent CDT(which wll have the child cdt as well) this will successfully insert data to the database in the parent table as well as child table. The foreign key in the child table should also be populated.

But the problem is when you save the data back to the parent CDT to acquire the generated ID (updated cdt which is returned by the writetodatastore smart service) Parent CDT will return with generated ID as well as child CDT s with generated ID, but in the child CDT the foreign key value not be returned. (If you go to DB and check now you will be able to see the foreign key is actually populated already).

Now if you do another update to DB with out mapping the child CDT's foreign key, in DB child will lose the value in foreign key field.

To answer your question, there is no need to 2 DB statement to insert data but it will be done by one call to writetodatastore, but when you try the second time make sure you update the CDT with correct foreign key in the child or fetch the data to main CDT from DB and do all manipulation of data and update DB.

Regards
Suresh

HI Jpheh,

Are you establishing the relationship with in the CDT, if this is the case when you use write to data store entity to insert the parent CDT(which wll have the child cdt as well) this will successfully insert data to the database in the parent table as well as child table. The foreign key in the child table should also be populated.

But the problem is when you save the data back to the parent CDT to acquire the generated ID (updated cdt which is returned by the writetodatastore smart service) Parent CDT will return with generated ID as well as child CDT s with generated ID, but in the child CDT the foreign key value not be returned. (If you go to DB and check now you will be able to see the foreign key is actually populated already).

Now if you do another update to DB with out mapping the child CDT's foreign key, in DB child will lose the value in foreign key field.

To answer your question, there is no need to 2 DB statement to insert data but it will be done by one call to writetodatastore, but when you try the second time make sure you update the CDT with correct foreign key in the child or fetch the data to main CDT from DB and do all manipulation of data and update DB.

Regards
Suresh

Yes, you are right Suresh. I also faced same issue as part of my project. I have followed the same approach i.e. before second update, I used to make sure to copy parent P.Key to child F.Key field. This is one time activity and from third update onward's it works as expected.

Regards,
Janardhna Ketha

Hey Janardhna,

I've used this approach successfully in the past as well however in this particular case I need to save both the parent and child within a single transaction for data integrity reasons. As such saving the parent in a separate call then manually updating the FKs on the child before saving them won't work - Appian treats them as different transactions.

Hey Suresh,

Thanks for your reply but that's not the behavior I'm seeing. If I save a parent CDT that has a nested child CDT Appian will actually make 2 database calls within the span of a single transaction:

1) Insert the Parent and Child objects
2) Update the Child objects with the Parent FKs

While you are correct that from a process model point of view this is only a smart service node, under the covers Hibernate is still executing 2 INSERT statements and an UPDATE statement (as seen in the SQL debug logs and database triggers I have mapped to the tables).

ok, now I think I understand what you are after, yes since Appian use hibernate under the hood it will create two sql statements, but from Appian perspective this orchestrate as one transaction. Come to think of it i do not think we can achieve what you are after with using only one sql statement since the parent row has to be created and the ID needs to be generated first so that we can populate and insert the child row to the table

Hi Sureshg57,

Thanks for your reply. The link I posted in my original post was to the hibernate documentation where this exact problem is resolved by mapping the parent to child AND the child to parent.

Note that the solution proposed in the post will still write the Parent first - the change is that it won't write the child and then follow it up with an update command - it will insert the parent first, update the child object in memory with the FK and then insert the child into the database.

Hi sureshg57 I am trying to accomplish what you state in your first paragraph but I am failing to do so. I have a parent and child relationship (one to many). On my child CDT, do I need to have the foreign key (which comes from the parent)? I am asking because when I attempt to write to the data into the database with the datastore, it gives me the error: org.hibernate.PropertyValueException: not-null property references a null or transient value. Any thoughts on what my issue might be?