We are troubleshooting an issue impacting community login, and it may be intermittently unavailable. Sorry for any inconvenience.
I'm trying to execute a batch of SQL insert queries with multiple data values. I've stored the values (to be inserted) in an Infotable, iterating over the infotable and building the INSERT query statements and then making a call to "RunDatabaseCommand" service of a DB thing.
Ideally, with different set of values the said service inserts the values into the DB.
When one of the multiple values (at the time of iteration) has already exist, the service throws an error (DB error i.e., Primary key violation) - this is expected. However, the service execution stops at the existing entry and doesn't reach the remaining values.
For example - the Infotable values (v1, v2, v3, v4 etc., ) needs to be inserted. These values are iterated, over each iteration the values are being inserted into the DB. Suppose "v2" values already exist in the DB and v3 and v4 values doesn't exist in the DB. At the time iteration execution with v1 value gets inserted. Execution with v2 values throws an error, then the rest of the values i.e., v3 and v4 never gets executed / inserted into the DB.
Here, when the execution reaches v2 value, it should throw an error / warn message, and then the execution should continue with v3 and v4 value insertions. How can I achieve this behaviour??
I've tried putting a TRY / CATCH block around the code which makes a call to RunDatabaseCommand service. But the execution is getting stopped post reaching the existing entry.
Solved! Go to Solution.
Using Try / Catch Block I was able to handle the error situation, by using the Update DB query
Can you post your code? It's unclear if you post one large statement or multiple inserts.
If you have one large statement, it's only one DB transaction and TWX can't interfere with that. You would have to run multiple individual inserts. Those you should be able to try/catch.
Using Try / Catch Block I was able to handle the error situation, by using the Update DB query
As you already have the data as an infotable, why don't you remove the duplicates before trying to insert to the DB ? That would also prevent unnecessary DB requests.
As @Rocko said, it would be good to see your code. I doubt a try/catch would not catch this if you have individual INSERTs.
Is this postgres? If so, you can use the ON CONFLICT DO NOTHING statement outlined here:
https://www.postgresql.org/docs/current/sql-insert.html
Specifically something like this:
-- This statement could infer a partial unique index on "did"
-- with a predicate of "WHERE is_active", but it could also
-- just use a regular unique constraint on "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
ON CONFLICT (did) WHERE is_active DO NOTHING;
- Nick