I’m running a couple gateway scripts and I look for a tag change in the plc, then I insert some data into a sql database. That part works really good, the part that I’m having trouble with is that I need to run a query before the insertion to check and make sure the serial number is not already in there because the serial number is the primary key in the sql database so it doesn’t like you trying to write back over it again. I need an example of a query that assigns its results to a variable, so I can compare if the two are the same in an IF statement, then insert data into the database if the serial number is not there.
https://docs.inductiveautomation.com/display/DOC79/system.db.runScalarQuery
There are examples at the bottom
You can do the multiple query check, but some advanced SQL statements could help you achieve what I think you want to do without worrying about it (unless you have a reason to). The idea here being to handle the duplicate primary key error you will get.
If you just simply do not ever want to write a new record, then using an INSERT IGNORE may be a way to gracefully handle it on the server side and it keeps you from sending multiple queries to the server.
INSERT IGNORE INTO table (id, name, age) VALUES (1, "A", 19);
If you want to be able to insert new records, but update values as well, then the INSERT…ON DUPLICATE KEY UPDATE may be another valid option.
INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19)
ON DUPLICATE KEY UPDATE NAME = "A", AGE = 19;
Finally, you can also use the REPLACE INTO statement which is essentially a DELETE and INSERT.
REPLACE INTO table (id, name, age) VALUES(1, "A", 19);
Consider just catching the exception if you just want to not be bothered if the data is already there.
Good point!