• State Suggested Answer
  • +1 person also asked this people also asked this
  • Replies 18 replies
  • Answers 3 answers
  • Subscribers 12 subscribers
  • Views 26605 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Data and Records

Multiple SQL commands for a single insert

Jin Pheh
Certified Associate Developer

Hello there,

I have a parent child relationship mapped within Appian.  When I call writeToDataStore on the parent object, Appian successfully saves both the parent and child objects however it requires 2 SQL commands to save the children.  Specifically what it does is:

  1. INSERT the Parent and Child objects 
  2. UPDATE the child objects with the foreign key of the parent

 

Is there any way to force Appian to insert the Parent and children without requiring the second update command?  IE: Insert the parent, set the FK value on the child, insert the child objects?

Some Hibernate documentation I've read suggests setting up a "ManyToOne" relationship on the child towards the parent however this causes several unusual datastore validation errors (data store does not have a definition for parent or child)

docs.jboss.org/.../example-parentchild.html

 

  Discussion posts and replies are publicly visible

Parents
  • 0
    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
  • 0 in reply to sureshg57
    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
Reply Children
  • 0
    Certified Associate Developer
     in reply to 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.

© 2024 Appian. All rights reserved.