Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
+1
person also asked this
people also asked this
Replies
13 replies
Subscribers
8 subscribers
Views
3664 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
General
#GeneralInterest -: Hi all , I have one scenario, Let say i have one table
jasmins
over 7 years ago
#GeneralInterest -: Hi all ,
I have one scenario, Let say i have one table Employee in this table, i have 3 columns RowId (Primary Key ),EmpId(int) and EmpName(text),EmpId and EmpName should be unique and from UI if two users are trying to insert same value at same time, then these entry should go as overwrite instead of creating two separate entries in database, I have tried using Unique Key constraint but if we are trying to insert same record which is exist in database that time write to database node is failing and i am getting “Unique key constraint violation error”, for work around i am checking record is exist in database or not, if yes then updating existing record on Write to Database node and also using unique key constraint ,but any other way to do this because two users are running flow simultaneously with same value so whose entry will go first that we are not sure?
Kindly suggest how to handle?
OriginalPostID-217893
OriginalPostID-217893
Discussion posts and replies are publicly visible
0
Karthik
over 7 years ago
Before writting into the DB Tables, query the DB table by empName and empId and if there is an entry already then update the same result set object and do an update, otherwise insert.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Sidhant Behura
over 7 years ago
@jasmins - I think creating an unique key constraint is absolutely fine. When you get an “Unique key constraint violation error” and the database node fails, you should configure an exception flow where you should get the already existing record and update it.
Please refer below link for more information.
https://forum.appian.com/suite/help/16.2/Exception_Flows.html
Or else as suggested by karthikeyans75 you can first check whether the record already exists or not and then you can insert or update accordingly.
Hope the information was helpful.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
gaurava322
over 7 years ago
I think the way you are handling is totally fine.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
VV
Certified Associate Developer
over 7 years ago
Hi @sidhantb - Does the exception flow get triggered when a node fails with an error? I don't believe it does that yet although it will be nice to have. As far as I know, the process is paused by exception.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Karthik
over 7 years ago
Even I'm don't think exception flow would be triggered in case of node exceptions.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 7 years ago
To the best of my knowledge, what @sidhantb has specified ("and the database node fails, you should configure an exception flow where you should get the already existing record and update it.") above isn't possible but I guess he has said so thinking on the lines of including the database operation in a sub-process node and then including the exception on the node(which times out after certain time period) and I guess this is pretty much possible. I believe he has put forward his opinion in a slightly different manner.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
ankushj
over 7 years ago
Is your RowId (PK) is auto generated?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Stefan Helzle
A Score Level 3
over 7 years ago
You can use the "Query Database" smart service to write to DB. It lets you define whether it stops on errors or not. I use this to implement robust locking for records. See
forum.appian.com/.../e-215481
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Sidhant Behura
over 7 years ago
Apologies if the comment was misleading or not detailed. It is always good to have DB Interactions in a sub-process and configuring a exception timer on the subprocess in case the insertion or updation fails.
Thanks @sikhivahan for explaining :-)
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
jasmins
over 7 years ago
@sikhivahans and sidhanthb - I have added timer in sub process which will time out after 1 minute once DB call fails and added exception on sub process node, this is working fine, but to update existing record i need to add update logic on write DB node.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
>