I want the operator to input a job number into a text field. They will then hit a button that runs the named query for inserting the job number into a SQL database. I’m having issues with the operators inserting duplicate job numbers. Is this something I can catch before the query runs or do I need to script this within the named query?
Named Query Script:
INSERT INTO Arms_Internal_Schedule(
JobNum)
VALUES (jobNumInput)
I do something like this:
First, I have a named query:
SELECT COUNT(*) AS IDCount
FROM CalTrax_Assets
WHERE AssetID = :IDPRM
Then, in my button’s script:
(params are defined up here)...
id_check_result = system.db.runNamedQuery("CalTrax_ID_Check", {"IDPRM" : params["IDPRM"]})
id_exists = id_check_result.getValueAt(0, "IDCount") > 0
if not id_exists:
(the rest of the script / run named query)
Edit: After looking at some of the responses below, I wouldn’t handle it this way going forward. I believe it' will still be safe for my application, since I’m not expecting multiple concurrent operators. I will probably change this in a future update though.
I think it’s best to handle this with a stored procedure.
Have your form call the stored procedure to do the insert. Have an if not exists in the stored procedure. This moves the business logic to your database which is a better architecture imo.
Put a unique key constraint on the db table’s column in question.
That depends. Do you want them to not be able to enter the duplicate ID altogether, or enter it with updated information? To update the existing info. you could use REPLACE INTO instead of INSERT INTO.
Otherwise, I would run a SELECT query to check for the existence of the ID before doing the update, and if it exists, display an error message.
Do this.
Trap the exception if it is duplicate with try: ... except Throwable, t:
Yuck. I strongly disagree, for simple duplicate checking and handling. Singe SQL statements can do this.
Definitely not this, as it is racy with multiple operators.
I would do it as soon as the value in the text field changes, so the message can be displayed and the submit button disabled. But you’re right, you still need the unique key constraint as @dkhayes117 says, and proper error handling after the fact in case multiple operators submit the same ID at the same time.
I looked into this deeper because I’ve generally done it with NOT EXISTS inside a sp in TSQL and I’ve never had a problem. I’m always down to learn new things and I know I can lean on your wealth of experience to teach me new things.
I see that there can be race conditions in very large systems with many concurrent clients doing inserts.
NOT EXISTS in TSQL is arguably faster than LEFT JOIN but I haven’t benchmarked it. I haven’t personally had a problem with this but I can see you could have a problem with this in an environment with a large number of concurrent operators pounding inserts. I assume that would be a bigger problem if you have really large tables too.
I also saw that you can have a problem in some of the other DB vendors. I saw people saying MySQL particularly has a problem with this but I don’t have personal experience of this. We typically use MSSQL.
The only real race condition and deadlocking issue I’ve seen with MSSQL is with SGAM contention. You don’t need a lot of concurrent users to encounter that one.
Doesn’t this also allow for a quicker/better feedback to the user than a stored procedure?
If I remember correctly, calling an SP via a named query does not allow for a return value, but using the try…except would allow for custom feedback.
It might vary by provider. You can return values from MSSQL sp’s in a few ways. Output parameters, return codes and you can even return multiple result sets but please don’t ever do that.
I had a colleague for a short time that returned 7 resultsets from a sp once. It was not pretty.
Edit:
You can also use try/catch style logic in TSSQL.
I generally like having the business logic in sp’s because it makes the front end simpler. If your db is a black box the front end is simple and easy to migrate to whatever you move to down the road.
LOL!
I was thinking of the Named Query’s functionality. I remember wanting to return a True/False on a successful or failed INSERT, but the NQ does not allow feedback to the client/user.
When PostgreSQL introduced their ON CONFLICT implementation, it was a big deal because they were the only brand that could do race-free single-statement conditional insert/update. All others, where syntax of some kind existed at all, had unhandled race conditions that could throw errors even when no conflict existed. (T-SQL MERGE was, maybe still is, especially bad.)
Next best solution, across brands, is to unconditionally INSERT and let the DB’s UNIQUE index implementation enforce it for you. If you don’t have a unique constraint, and you do anything with multiple statements (including procedures) to test for existence, you can end up with duplicates. You can get close with strict transaction isolation, but you have to be ready to handle “unexplained” rollbacks.
Don’t try to be smarter than the DB.
MySQL/MariaDB has an on duplicate syntax
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = new_value1,
column2 = new_value2,
...;
Let the database take responsibility for unique IDs instead of making the users responsible for that. Even the best operators will have problems if two are entering a new record at the same time.
This worked like a charm!