• State Suggested Answer
  • +1 person also asked this people also asked this
  • Replies 18 replies
  • Answers 3 answers
  • Subscribers 12 subscribers
  • Views 26606 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

  • 0
    Certified Associate Developer
     in reply to sureshg57
    Hi Sureshg57,

    I am not trying to map or nest multiple levels within a CDT - I'm trying to get a single parent-child relationship to save to the database within a single transaction.
  • 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.
  • 0
    Certified Associate Developer
     in reply to sureshg57
    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).
  • 0 in reply to Jin Pheh
    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
  • 0
    Certified Associate Developer
     in reply to sureshg57
    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.
  • 0
    i would suggest to use a database trigger to perform your job.
    an AFTER INSERT trigger will do it.

    And also the load on Appian will be reduced and the process will be performance oriented.

    Regards
    Alok Patro
  • 0 in reply to sureshg57
    Hi 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?
  • 0
    Depending on the implementation there are two ways to do this:

    1. In your XSD CD definition, you can use the @joincolumn annotation for the child CDT. When you write the data, you have to write the child CDT first before you write the parent.

    2. Second option is just create an ID in the parent CDT, and manually assign the value to it after the child CDT was written.

© 2024 Appian. All rights reserved.