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

**URL:** <https://forum.inductiveautomation.com/t/using-a-tag-change-gateway-script-to-search-sql-and-trigger-a-boolean/114448>\
**Category:** Ignition\
**Tags:** scripting\
**Created:** [March 11, 2026, 5:25pm UTC](https://forum.inductiveautomation.com/t/using-a-tag-change-gateway-script-to-search-sql-and-trigger-a-boolean/114448 "2026-03-11T17:25:38Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![orioleswatcher](https://avatars.discourse-cdn.com/v4/letter/o/a4c791/32.png) [@orioleswatcher](https://forum.inductiveautomation.com/u/orioleswatcher)\
**Post date:** [March 11, 2026, 5:25pm UTC](https://forum.inductiveautomation.com/t/using-a-tag-change-gateway-script-to-search-sql-and-trigger-a-boolean/114448/1 "2026-03-11T17:25:38Z")

</div>

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:

```python
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.)

```python
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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [March 11, 2026, 5:34pm UTC](https://forum.inductiveautomation.com/t/using-a-tag-change-gateway-script-to-search-sql-and-trigger-a-boolean/114448/2 "2026-03-11T17:34:37Z")

</div>

> [@orioleswatcher](#):
>
> ```python
> from datetime import datetime, timedelta
> 
> ```

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

```python
# 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

```python
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

```python
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`).

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 11, 2026, 7:03pm UTC](https://forum.inductiveautomation.com/t/using-a-tag-change-gateway-script-to-search-sql-and-trigger-a-boolean/114448/3 "2026-03-11T19:03:51Z")

</div>

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):

```python

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.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 11, 2026, 7:12pm UTC](https://forum.inductiveautomation.com/t/using-a-tag-change-gateway-script-to-search-sql-and-trigger-a-boolean/114448/4 "2026-03-11T19:12:06Z")

</div>

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.

> [@orioleswatcher](#):
>
> ` system.util.logError("Database Query Failed", e)`

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.

---

<div class="post-metadata">

**Author:** ![orioleswatcher](https://avatars.discourse-cdn.com/v4/letter/o/a4c791/32.png) [@orioleswatcher](https://forum.inductiveautomation.com/u/orioleswatcher)\
**Post date:** [March 11, 2026, 7:54pm UTC](https://forum.inductiveautomation.com/t/using-a-tag-change-gateway-script-to-search-sql-and-trigger-a-boolean/114448/5 "2026-03-11T19:54:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![orioleswatcher](https://avatars.discourse-cdn.com/v4/letter/o/a4c791/32.png) [@orioleswatcher](https://forum.inductiveautomation.com/u/orioleswatcher)\
**Post date:** [March 11, 2026, 7:55pm UTC](https://forum.inductiveautomation.com/t/using-a-tag-change-gateway-script-to-search-sql-and-trigger-a-boolean/114448/6 "2026-03-11T19:55:53Z")

</div>

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.