I have two queries that I am running on a couple different tables and I would like to convert them to function since i have to use them multple times. I will admit that I am a novice at this so I am sure my code is rough.
My first funtion I would like to be able to hand it the FROM and WHERE staments and it return NoEntry
#Check to see if 1 has been used
query = "SELECT * FROM %s WHERE CoatNumber=1 AND recipes_id= %s" % (table, recipes_id)
NoEntry = system.db.runScalarQuery(query,'Foxall')
Here I would like to do similar and have it return Gap.
#Get the next available number for the Recipe Number
query = "SELECT t1.CoatNumber + 1 FROM %s t1 WHERE NOT EXISTS (SELECT * FROM %s t2 WHERE t2.CoatNumber = t1.CoatNumber + 1 AND recipes_id=%s) order by CoatNumber LIMIT 1" %(table,table,recipes_id)
Gap = system.db.runScalarQuery(query,'Foxall')
After the funtion return a value or not I compare them to find if my next entry needs to either insert a 1 or the first availible in a gap.
If you think that is a better way to do this, please let me know, as I came up with this solution from googling. My ultimate goal is to search for the lowest unused number in the CoatNumber field and use that one next when inserting the date.
Try to use prepared statements (like system.db.runScalarPrepQuery) to pass on variables, that way, they get escaped, and you’re less likely to run into bugs and security issues. Some day, someone will insert some data with a single quote ', and this will break a lot of queries.
Try to code your table directly into your query (unless it’s really too much trouble, but then you have to question the DB design), as column and table names can’t be escaped safely by the db functions.
Don’t do a SELECT * for a scalar query. That query will only return one value. Just select a non-null column, and check if the result is None or not
It’s generally bad practice when you first query a value, and later use it in an update query (unless you use transactions rigorously). Whenever you encounter multithreading (be it inside Ignition, or even a completely unrelated process accessing the DB), the state of the DB might not be what you expect, and you need to handle all cases of updating the same row, disappeared rows, …
So, try to put it in a single query:
system.db.runPrepUpdate("""
INSERT INTO TableName
(CoatNumber, recipes_id)
SELECT
COALESCE(MAX(CoatNumber), 0) + 1,
?
FROM TableName WHERE recipes_id = ?""", [recipes_id, recipes_id], "dbName")
The Max(CoatNumber) will get the next coatnumber, COALESCE will make sure, if the MAX doesn’t exist, it will return 0 instead of NULL.
It may not be entirely what you need, but it should get you on the way I guess.