Given an Oracle or MS SQL server, what is the canonical way in Jython scripting to do an “upsert” to a table. Where an “upsert” is a a combination of “update” and “insert”. IE if the row doesn’t in a table it is inserted, but if it does exist then it is simply updated.
I see that an “upsert” can be done via a transaction group, but I don’t want to be manually defining new transaction groups whenever I have new data (from different sources) that needs to be inserted/update to a particular table.
My use case is that I have a table in which each row represents the status of a single machine. Right now I have one machine that will place data in that table, but in the future other machines will be slowly added. I want a robust solution that will allow me to define a new machine and have the whole SQL side of things be automatically generated rather than having to manually define a new transaction group each time a new machine comes online.
Upsert is very DB specific, and not all have true atomic implementations. The SQL standard calls for the use of an ON CONFLICT clause. I know that PostgreSQL has implemented it (back in 2015) per the standard. Oracle and Microsoft may have, or have a proprietary alternative.
I should have added that I know there is no direct “upsert” SQL statement for Oracle or MS. What I am looking for is what is the canonical method to construct upsert functionality via Jython scripting. I’m sure I could cobble something together that works, but I am not likely to be following best practices.
I found this interesting blog about Upsert patterns and anti-patterns, but that is focussed on stored procedures. That is not a path I want to go down (for DB admin issues at site, not because I can’t write SPs)
Well, jython in Ignition uses JDBC, so what you might find for JDBC would be appropriate inside of an Ignition DB scripting call. I would use system.db.runPrepUpdate or a named query. In many cases, JDBC will not run a DB script language, just plain statements. If you need to run multiple statements with transaction support, you will need to invoke Ignition’s scripted transaction begin/commit with separate JDBC statements between.