Duplicate entry checking with Azure database

Ignition
1

I have a short script which saves a product name to a Azure SQL database. My question is how to test for and disallow duplicate names. I know I can put a constraint on the database which will throw an exception if a duplicate is entered. Also, I know I can use a “try … except” block to catch the exception. What I don’t know is how to identify that particular exception so it can be handled. Any ideas would be most appreciated! We are using Ignition Ver. 7.7.2, and here the script I’m using, which works fine except for the need to check for dups.

[code]varIng = event.source.parent.getComponent(‘Ingredient’).text
varIngID = event.source.parent.parent.SEL_ING

if (varIng == “”):
system.gui.messageBox(“Ingredient name cannot be blank”)

else:
if varIngID > 0:
#edit Ingredient
varIngID = system.db.runPrepUpdate(“UPDATE Ingredients SET INGREDIENT = ?
WHERE Ingrd_id = ?”, [varIng, varIngID])

	else:
			# Add Ingredient
			varIngID = system.db.runPrepUpdate("INSERT INTO Ingredients (INGREDIENT) VALUES (?)",
			[varIng], getKey=1)

system.db.refresh(event.source.parent.parent.getComponent(‘Ing_Tbl’),‘data’)
[/code]

2

You could use a stored procedure to handle it all for you and then take appropriate action based on the return value.

3

You can use

SELECT COUNT(*) 
FROM Ingredients
WHERE Ingrd_id = x

and use your result to decide whether to INSERT or UPDATE.

Alternatively, search for UPSERT - bunch of articles out there explaining how you can do it in one transaction.

4

Thank you both for your reply’s! And yes, I’m fairly new to SQL (but learning quickly).

SeanT - Either your code example or an UPSERT looks like what I’m looking for. My question now: would it be best to do this as a stored procedure as JGJohnson suggests, or it is possible to put it into a script behind a button? Adding it behind a button would be my preference, but I don’t see a good way to do that.

Again, thanks for all the assistance!

5

Azure is a cloud service right? Knowing that, and knowing that upsert is not too complicated, I’d probably lean toward keeping the query in Ignition.