Problem with system.db.RunQuery in a Tag Event Script

Ignition
1

Hello,

I'm trying to extract a UUID from my database into tag when a value change on a tag. I tried running it in a tag event Script but also in a Gateway tag change and I was not able to make it work
Here is the Script:

selector_1 = system.tag.readBlocking(["[LaserWelding]parametres/source_1_selector"])[0].value
source_1_partid = system.tag.readBlocking(["[LaserWelding]parametres/source_1_partid"])[0].value
source_1_matid = system.tag.readBlocking(["[LaserWelding]parametres/source_1_matid"])[0].value
projet = 'A1-022961-01'

if selector_1 == 0:
	system.tag.writeBlocking(["[LaserWelding]parametres/source_1_uuid"],'')
if selector_1 == 1:
	cast = system.db.runQuery("Select part_uuid from casting.experiment where project_id = '%s' and experiment_id = %s" %(projet,source_1_partid))
	system.tag.writeBlocking(["[LaserWelding]parametres/source_1_uuid"],cast)

Here is the Error in the Log:

([LaserWelding]parametres/source_1_partid, valueChanged) Error executing tag event script: Traceback (most recent call last): File "tagevent:valueChanged", line 12, in valueChanged at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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(Select part_uuid from casting.experiment where project_id = 'A1-022961-01' and experiment_id = 400, , )

Thanks for your feedback!

2

Which is line 12?

3

There's more details in the logs somewhere that would help show the actual problem. Copy every piece of information you can, from the gateway logging interface or the wrapper.log file directly.

4

One thing to check: When run in the gateway you need to tell it which database connection to use as it is not aware of the project's default.

system.db.runQuery(query, database, tx)

See the first syntax variant in system.db.runQuery - Ignition User Manual 8.0 - Ignition Documentation.

5

Thanks for all the reply. I went but with a query tag with an event driven instead of a fixed rate. Our goal is to hunt all the query against the DB and try to limit them to only have query when needed.

1 Like
6

Is projet a typo? Perhaps it is meant to be project?

7

It's french. There's other clues in the OP.

8

Yeah sorry we are a french main language organization.

1 Like
9

Fairly certain that @Transistor is on the correct track here, since you're calling this in a Gateway Scope you need to provide a database connection name. I don't trust the default to always work anyway.

Also, don't use multiple calls to read blocking, combine them into one.

I would strongly recommend using a Prep Query, because you could also be having issues with data type conversions.

Try this:

selector_1, source_1_partid, source_1_matid  = [qv.value for qv in system.tag.readBlocking(["[LaserWelding]parametres/source_1_selector","[LaserWelding]parametres/source_1_partid","[LaserWelding]parametres/source_1_matid"])]
projet = 'A1-022961-01'
databaseName = 'your Database Connection Name'
cast = ''
if selector_1:
	cast = system.db.runPrepQuery("Select part_uuid from casting.experiment where project_id = ? and experiment_id = ?",[projet,source_1_partid],databaseName)

system.tag.writeBlocking(["[LaserWelding]parametres/source_1_uuid"],cast)
2 Likes