Multiple SQL commands for a single insert

Hi jpheh,
Afaik establishing relationship is the best option for you requirement.

Write a Stored Procedure and Use 'Execute Stored Proc' smart service

Hi Jin Pheh as per my understanding, the stored procedure may serve your requirement.

Also when you talk about Hibernate, JPA or any other Framework/API, when more than one entities are in a relationship, multiple SQL queries will be executed into the DB behind the screen to perform some operation over them.

e.g. While performing Insert operation, until we do not save the Parent, we don't know the PK value of the Parent row through which the Child Table is linked to.

Hi,

The best approach is to create nested CDT, In the Mani CDT(Parent) you can add an new column for Child and its provide the datatype CDT (Child CDT).

So whenever you create Insert the data on Parent the Appian automatically map the foreign key with Child table

Thanks

Adding to above point, as per Appian Best practices, we should not prefer Nested CDT approach unless that is much needed/ the Last Choice.

However in your case you can go for Nested CDT as well. But in such case, as per the JPA documentation, multiple SQL queries will be executed on the DB.

Hi Alok,

I go through the Appian documentation,
docs.appian.com/.../CDT_Relationships.html

but i am not find anything which related that the nested CDT is not a best practitce,
I believe the Appian gave such a nice feature, we should used that.
Pelase correct me If I am wrong

Thanks
Vinay

Hi Aloks,

It's not true that multiple SQL statements must be run to insert the child objects. It could very well work like this:
1) Insert Parent object
2) Read Parent Primary Key
3) Insert Child Objects

This is what the documentation I linked in my original post was describing and it's what I'm trying to replicate with Appian's CDT/JPA.

I would like to avoid a stored procedure if possible because this functionality is provided out of box by Hibernate and a stored proc would require constant secondary updating for every change.

I think form best practice perspective what we have seen and also what Appian would recommend is to not to go beyond one level of nesting. if you are nesting multiple level with in a CDT that is not a best practice as those are hard to maintain and also hard to code too,

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