Using a tag change gateway script to search SQL and trigger a Boolean

Ignition
1

I am trying to use a gateway script triggered by the rising edge of a Boolean tag change to query a SQL database for a lot number. If a matching lot number is found within the past two years, I need to set another Boolean tag to “True”.
Below is my script:

from datetime import datetime, timedelta
from system.db import runQuery
if not initialChange and not currentValue.value == 0 and previousValue.value == 0:

# Get the value of the Scanned_Lot_Number tag
	scannedLot = system.tag.readBlocking(["[default]PhibroTest/Scanned_Lot_Number"])[0].value

# Calculate the date 2 years ago
	twoYearsAgo = datetime.now() - timedelta(days=730)

# Define the SQL Query (use a function to avoid variable issue)
	def get_sql():
	    return "SELECT COUNT(*) FROM TestDB.dbo.TransactionReportHeader WHERE Lot_Number = ? AND t_stamp >= ?"

	sql = get_sql()

# Execute the Query
	try:
	    result = runQuery("TestDataBase", sql, [scannedLot, twoYearsAgo])
	    count = result[0][0]
	    isValid = count > 0
	except Exception as e:
	    system.util.logError("Database Query Failed", e)
	    isValid = False

# Write the result to the Boolean Tag
	system.tag.writeBlocking(["[default]PhibroTest/SCADA_Lot_Found"], [isValid])

Admittedly, SQL is not in my wheelhouse, so I am a little lost here. In the Gateway Scripts Logger, the script lists "Error" under status, gives the following error message, and then changes to "Success" but does not actually write "True" to the Boolean.
(This is abridged, but should be all the relevant information.)

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 19, in File "", line 19, in at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:359) at jdk.internal.reflect.GeneratedMethodAccessor117.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(TestDataBase, Transaction datasource unknown, [u'0987654111', datetime.datetime(2024, 3, 11, 13, 13, 4, 64000)])

...

Caused by: org.python.core.PyException: java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(TestDataBase, Transaction datasource unknown, [u'0987654111', datetime.datetime(2024, 3, 11, 13, 13, 4, 64000)])

Caused by: java.lang.Exception: Error executing system.db.runQuery(TestDataBase, Transaction datasource unknown, [u'0987654111', datetime.datetime(2024, 3, 11, 13, 13, 4, 64000)])

Any help or guidance at all would be a huge help. Thanks in advance.

2

Ignition expects java date objects, so use system.date.now() and system.date.addDays(-730) instead. Also check your db and make sure your tstamp column really is a datetime object and that someone didn’t make it a varchar

Not sure why you’d have to do this

# Define the SQL Query (use a function to avoid variable issue)
	def get_sql():
	    return "SELECT COUNT(*) FROM TestDB.dbo.TransactionReportHeader WHERE Lot_Number = ? AND t_stamp >= ?"

	sql = get_sql()

Instead of just

query = "SELECT COUNT(*) FROM TestDB.dbo.TransactionReportHeader WHERE Lot_Number = ? AND t_stamp >= ?"

If this query is used elsewhere as well its worth centralizing via a named query or in a function that runs a prep query.

The real issue is from your error though - it doesn’t know what database to run this on because you are in a gateway scope

Caused by: java.lang.Exception: Error executing system.db.runQuery(TestDataBase, Transaction datasource unknown, [u'0987654111', datetime.datetime(2024, 3, 11, 13, 13, 4, 64000)])

Datasource unknown. You have to tell it what db to run against - this is another arugment in the system.db.runPrepQuery function.

You’re also using system.db.runQuery but using ?. Use system.db.runPrepQuery if you are using SQL that has question mark parameters ( and you SHOULD use system.runPrep* functions instead of the raw system.db.runQuery/runUpdate).

1 Like
3

Since you are looking for a single value you can use system.db.runScalarPrepQuery()

Assuming "TestDataBase" is your database connection (and showing and example of system.date.* functions):


if not initialChange and currentValue.value:

	# Get the value of the Scanned_Lot_Number tag
	scannedLot = system.tag.readBlocking(["[default]PhibroTest/Scanned_Lot_Number"])[0].value

	# Calculate the date 2 years ago
	twoYearsAgo = system.date.addYears(system.date.now(), -2)

	# Execute the Query
	sql = "SELECT COUNT(*) FROM TestDB.dbo.TransactionReportHeader WHERE Lot_Number = ? AND t_stamp >= ?"
	count = system.db.runScalarPrepQuery(sql, [scannedLot, twoYearsAgo], "TestDataBase")
	isValid = bool(count)

	# Write the result to the Boolean Tag
	system.tag.writeBlocking(["[default]PhibroTest/SCADA_Lot_Found"], [isValid])

I'll also recommend to keep things simple. Make it work, then make it pretty.

2 Likes
4

In addition to the other excellent advice in this thread, I'll add:
Trust your LLM of choice a lot less than you currently are.

There's no such function. Complete hallucination. That probably also explains a lot of other 'weirdness' already noted.

LLMs give you the illusion of velocity in a domain like this. At some point, the technical debt bill will come due.

3 Likes
5

I tried the changes you suggested and ultimately got the script to stop faulting, but I could not get it to reliably write to the SCADA_Lot_Found tag.

6

Thank you so much for this! This is exactly what I needed. I just added an extra line to return the Boolean to False when the script is finished.