Scrpit to DB update / insert

Ignition
1

this script works but looking for any recommendations to improve it.
anyway to do this in one DB call instead of 2?

this is with MSSQL DB.

#check if row exists
		query = "SELECT Count(ArrayID) FROM TGMS_LookupBuilding WHERE ArrayID=?"
		args = [ArrayID]
		InitialDB = system.db.runPrepQuery(query, args, DB)
		#set values for update
		args = [currentValue.value, ArrayID]
		# if row doesn't exist add new or Update
		if InitialDB[0][0] == 0:
			query ="INSERT INTO TGMS_LookupBuilding (Name, ArrayID) Values (?,?)"
		else:
			query = "UPDATE TGMS_LookupBuilding set Name = ? WHERE ArrayID = ?"
		system.db.runPrepUpdate(query, args, DB)

Note on solution --- be careful where you use this sort of solution --- shouldn't be used if you could have mutiple calls to the same WHERE clause at the same time.

2

PostgreSQL implements the SQL standard ON CONFLICT clause. Highly recommended. MariaDB has UPSERT, and MS SQL has MERGE, but both of those have nasty race conditions and/or caveats.

Not sure about Oracle, but I would suspect they've implemented ON CONFLICT.

3

I thought you knew some command shorthand I didn't know, but it doesn't appear that way. :confused: As far as I know, the MariaDB/MySQL command is INSERT ON DUPLICATE KEY UPDATE which is a lot more long-winded than UPSERT, but does the same thing.

And I also highly recommend using an upsert/on conflict/on duplicate command, whatever it happens to be in your SQL server.

(EDIT: And, yes, the MariaDB ON DULICATE KEY has some weird caveats. The only one that has bit me bad enough to keep me nervous is if you are working on a table with more than one key column and your INSERT hits matching keys on two different rows...)

4

Hmm. You can tell how often I use MySQL/MariaDB.

I went digging around for the history of this in PostgreSQL, and found this discussion:

https://wiki.postgresql.org/wiki/UPSERT

I recall reading this back in 2016-ish when the "on conflict" feature was deployed.

5

Did you try writing a SQL query that has an if statement in it? We using this style in our DB (MSSQL) to avoid duplicate entries while updating existing entries.

IF EXISTS(SELECT ArrayID FROM TGMS_LookupBuilding WHERE ArrayID=?)
BEGIN
	UPDATE TGMS_LookupBuilding SET Name=? WHEREArrayID=?
END
ELSE
BEGIN
	INSERT INTO TGMS_LookupBuilding(Name, ArrayID) VALUES(?, ?)
END
6

That's not a query. That's a SQL script, which is not a standard feature of JDBC. Only a few JDBC drivers will run scripts.

That script doesn't eliminate the chance of a collision on import, as it isn't doing any locking.

That Wiki link I posted contains cross-brand analyses of the concurrency problems and how they are solved (in PostgreSQL, not so much in the others.).

1 Like
7

Hmm, guess I'm not completely sure on where something is a SQL script vs a query. I've only ever worked with MSSQL with low volume of data coming through, which probably doesn't help. That's probably why everything I've done so far has 'Just Worked'™

8

Any BEGIN or DECLARE or anything that would use more than one GO in SSMS is a script. @variable assignments are parts of scripts. Any statement that nests another statement where the other statement is allowed to be BEGIN is also a SQL script element.

MS SQL Server has so many solutions that require scripts that it needs SQL script support to not look utterly lame in the JDBC world. IMNSHO.

I don't put SQL Server in the same category[1] as Windows itself, mainly because it can be (and should be) installed on Linux, but also because its high availability clustering seems to work very well (as seen with a few of my clients).

  1. I place MS Windows in the "Engineering Malpractice" category when used anywhere in production networks. ↩︎

1 Like
9

I suspect it would come into play if the query was executing from potentially multiple sources. So that if 2 sources ran at the same time with an ID field value of 3 either both could be inserted ending up with 2 rows with ID of 3 and probably some other issues.

For my use case that isn't an issue since there is only one thing that can run the script so there isn't any way to have overlapping requests.

10

Well, if you can run INSERT and UPDATE separately, and you have a unique key (or primary key) present to prevent duplicates, just run the INSERT first, in a try: block. If it fails, run the update instead. Check the return value from either to make sure you got one row affected, or queue up a retry.

If you are mostly inserting, optimistically running it will be a win.

11

mostly updating. Just want to cover the case of insert if needed.

this is just for some reference tables that are in the DB based on tag values from PLC that get updated via excel sheet script that is updated by another DB.

Yes, sounds like convoluted process, but what is needed for audit trail and change management.

This is on a tag change script so when the tag is updated it updates the table in the DB, but if the PLC array is expanded and tags added I want it to automatically add the new rows to the table.