Script to run named query and parse returned row of data from database

Nicole_McCormack · December 28, 2024, 3:27am

Tried to move to a script I could call to get and parse the database row based on other posts in this forum

mystepnumber = system.tag.read("[default]MLWinTags/localVars/currentStepNumber.value")
mydataset = system.db.runNamedQuery('GetNextStep',{mystepnumber})
mypyDataSet = system.dataset.toPyDataSet(mydataset)

No matter what I try I can't pass the parameter mystepnumber to the named query. It is required to run the query though. I've tried every format imaginable to get that parameter pass correct and I just keep getting java casting errors. If I don't pass the parameter, script 'works' (no errors) but dataset is empty.
I want to extract individual columns from mypyDataSet to useable fields
(so an example for that would be awesome too)

>>> 
Java Traceback:
Traceback (most recent call last):
  File "<input>", line 2, in <module>
	at com.inductiveautomation.ignition.common.TypeUtilities.coerce(TypeUtilities.java:1681)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.coerce(PyArgumentMap.java:130)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.interpretPyArgs(PyArgumentMap.java:82)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.interpretPyArgs(PyArgumentMap.java:40)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:106)

	at jdk.internal.reflect.GeneratedMethodAccessor174.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.ClassCastException: java.lang.ClassCastException: Cannot coerce value 'set([[20, Good, Fri Dec 27 21:23:36 EST 2024 (1735352616123)]])' into type: interface java.util.Map


	at org.python.core.Py.JavaError(Py.java:545)

	at org.python.core.Py.JavaError(Py.java:536)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:192)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:553)

	at org.python.core.PyObject.__call__(PyObject.java:477)

	at org.python.core.PyObject.__call__(PyObject.java:481)

	at org.python.pycode._pyx69.f$0(<input>:4)

	at org.python.pycode._pyx69.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1703)

	at org.python.core.Py.exec(Py.java:1747)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:628)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:616)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.lang.ClassCastException: Cannot coerce value 'set([[20, Good, Fri Dec 27 21:23:36 EST 2024 (1735352616123)]])' into type: interface java.util.Map

	at com.inductiveautomation.ignition.common.TypeUtilities.coerce(TypeUtilities.java:1681)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.coerce(PyArgumentMap.java:130)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.interpretPyArgs(PyArgumentMap.java:82)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.interpretPyArgs(PyArgumentMap.java:40)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:106)

	at jdk.internal.reflect.GeneratedMethodAccessor174.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

	... 19 more

Traceback (most recent call last):
  File "<input>", line 2, in <module>
	at com.inductiveautomation.ignition.common.TypeUtilities.coerce(TypeUtilities.java:1681)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.coerce(PyArgumentMap.java:130)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.interpretPyArgs(PyArgumentMap.java:82)

	at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.interpretPyArgs(PyArgumentMap.java:40)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:106)

	at jdk.internal.reflect.GeneratedMethodAccessor174.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.ClassCastException: java.lang.ClassCastException: Cannot coerce value 'set([[20, Good, Fri Dec 27 21:23:36 EST 2024 (1735352616123)]])' into type: interface java.util.Map
>>> Keyboard Interrupt
>>> ```

justinedwards.jle · December 28, 2024, 4:07am

system.tag.readBlocking is the recommended function for 8.1, but in your case, I would use a custom property with direct tag binding to get this value, and trigger the script with the resultant propertyChange event.

This dictionary is missing the parameter name:

{'mystepnumber', mystepnumber}

Edit: Noticed that I had accidentally typed back ticks instead of apostrophes.

Nicole_McCormack · December 28, 2024, 2:17pm

none of those changes work,

Transistor · December 28, 2024, 2:34pm

Both of those changes "work" - but they haven't solved the underlying problem. Please post your revised code and the error message if different.

The error message you gave in post #1 contains,

Cannot coerce value 
    'set([[20, Good, Fri Dec 27 21:23:36 EST 2024 (1735352616123)]])' 
    into type: interface java.util.Map

Notice that your system.tag.read function (which should now have been changed to system.tag.readBlocking) returns a qualified value.

[20, Good, Fri Dec 27 21:23:36 EST 2024 (1735352616123)]
  |     |  |
  |     |  `-- timestamp
  |     `----- quality
  `----------- value

You need to extract the value as shown at the end of the first line below. Try,

mystepnumber = system.tag.readBlocking(["[default]MLWinTags/localVars/currentStepNumber.value"]).value
mydataset = system.db.runNamedQuery('GetNextStep',{"mystepnumber" : mystepnumber})
mypyDataSet = system.dataset.toPyDataSet(mydataset)

Note that system.tag.readBlocking is expecting a list so even when reading only one tag it should be enclosed in [ ].

You can read about QualifiedValue here:

Nicole_McCormack · December 28, 2024, 3:36pm

Transistor:

mystepnumber = system.tag.readBlocking(["[default]MLWinTags/localVars/currentStepNumber.value"]).value
mydataset = system.db.runNamedQuery('GetNextStep',{"mystepnumber" : mystepnumber})
mypyDataSet = system.dataset.toPyDataSet(mydataset)

mystepnumber = system.tag.readBlocking(["[default]MLWinTags/localVars/currentStepNumber.value"])
print mystepnumber
mydataset = system.db.runNamedQuery('GetNextStep',{"mystepnumber" : mystepnumber})
print mydataset
mypyDataSet = system.dataset.toPyDataSet(mydataset)
print mypyDataSet

output

>>> 
[[20, Good, Sat Dec 28 09:33:59 EST 2024 (1735396439869)]]
<PyDataset rows:0 cols:12>
<PyDataset rows:0 cols:12>
>>>

So by doesn't work, there seems to be no data received rows0
The GetNextStep named query works as I can see the data in a table on the screen but it is laggy triggering the way I am.

SELECT * 
FROM tasks
WHERE stepnumber = :myCurrStepNum

My purpose that I just can't find a way to work, looking at transaction groups, scripts, etc.
Trigger database read based on completing a set of tasks (if it was super fast, ideally when I hit next/confirm on one screen, go grab the next row of the database, parse the row to display on screen instructions, capture data input by operator, and write that to data table in database.

Since this is not superfast, update the nextStep on entry to screen, trigger a read to grab the next row and parse it, on hitting the next button move all the variables from next step to current step, and then go grab the next step again
also on next step write out current step captures

read step 20
next button
write out step 19 capture data if any
move step 20 to current tasks, go get step 21
execute step 20 tasks
next button
write out step 20 capture data
move step 21 to current tasks, got get step 22

need to understand how to do this in scripts (because I don't seem to be getting any rows with the code you've helped with) and what to use to trigger that script, the next button is an easy place HOWEVER, I have a script that runs to initialize values back to zero and on startup it works fine but if I jump back and forth between screens it doesn't reinitialize even though it prints to console that the script ran again. So maybe again I am moving too fast?

Transistor · December 28, 2024, 3:49pm

You didn't copy my code correctly. It's missing .value at the end of the line. If you fix that then print mystepnumber should return 20 and not the full QualifiedValue.

If you can get that fixed I'll look at the rest of your post.

justinedwards.jle · December 28, 2024, 6:13pm

I know we're throwing a lot of information at you here, so I'll go into detail and walk you through my recommended approach:
Step 1. Right click on the text field, and select "Customisers-->Custom Properties."

Step 2. Press the plus button and add a custom property called myStepNumber that is an integer datatype.

Step 3. Find the custom property for the text field at the bottom of the Vision Property Editor and click the binding icon:

Step 4. Select Tag at the top of the binding type selector on the left for a direct tag binding, and navigate to the tag in the folder tree on the right. Once the tag is selected, press the OK button to complete the binding.

Step 5. Right click on the text field, and select "Scripting."

Step 6. Select the propertyChange event handler, and write a simple script to update the text field anytime the step number changes:

# At run time, when the value of hte myStepNumber custom property changes,
# ...update the text field
if event.propertyName == 'myStepNumber' and event.newValue is not None:
	defaultValue = 'Unknown Step'
	
	# Create a Python dictionary of parameters to pass using the new value.
	# Note that everything is CASE SENSITIVE, so capitalization matters. 
	# Make sure the capitalization is correct for this use case
	parameters  = {"mystepnumber" : event.newValue}
	  
	# Run the Named Query.
	dataset = system.db.runNamedQuery("GetNextStep", parameters)
	
	# Get the value for the current step number from the dataset,
	# ...or use the default value if nothing is returned
	if dataset.rowCount:
		event.source.text = dataset.getValueAt(0, 'safety')
	else:
		event.source.text = defaultValue

This will work at all times in a running client session, but do not expect this to run outside of preview mode in the designer. In preview mode in the designer, this will only update if the tag changes value.

Nicole_McCormack · December 28, 2024, 6:31pm

I did copy it correctly but assumed it was a typo since it fails.

running in the script console to test.

mystepnumber = system.tag.readBlocking(["[default]MLWinTags/localVars/currentStepNumber.value"]).value
print mystepnumber
mydataset = system.db.runNamedQuery('GetNextStep',{"mystepnumber" : mystepnumber})
print 'here is my dataset from query'
print mydataset
mypyDataSet = system.dataset.toPyDataSet(mydataset)
print mypyDataSet

results in

>>> 
Traceback (most recent call last):
  File "<input>", line 3, in <module>
AttributeError: 'java.util.ArrayList' object has no attribute 'value'
>>> 
Traceback (most recent call last):
  File "<input>", line 1, in <module>
AttributeError: 'java.util.ArrayList' object has no attribute 'value'
>>> type or paste code here

pturmel · December 28, 2024, 6:44pm

Doesn't go with

Parameters in named queries are case-sensitive.

After following Justin's suggestion to make a custom property bound to your step number tag, I don't see any reason you can't use a Named Query Binding to run the query instead of a script. (You should not need any script to connect the step's data to your display.) Within the NQ binding, point your parameter at the custom property.

Once you have that NQ binding in place, my original advice to use try() will apply.

justinedwards.jle · December 28, 2024, 6:59pm

I've run into situations where I get internmittent overlays with SQL bindings, which is what I imagined was happening in the previous topic concerning this same issue. When that happens, I get rid of the bindings and handle the data with scripting. Although, we're only getting one row here, so I can't imagine any reason why there would be any latency issues.

This is happening because readblocking returns a list. This needs to be written like this:

system.tag.readBlocking(["[default]MLWinTags/localVars/currentStepNumber"])[0].value

Note the addition of the [0] index value

pturmel · December 28, 2024, 7:02pm

Sounds like something to fix without using scripting where you shouldn't. There is no good reason to use a script for this query, and at least two reasons to not use a scripted query:

NQ bindings run asynchronously--they won't block your UI, and
Bindings run in both design mode and preview mode.

(This is Vision--avoiding time-consuming tasks in event scripts is critical to proper operation. Unlike Perspective.)

Transistor · December 28, 2024, 8:20pm

Thanks, Justin. I missed the index value.

Nicole_McCormack · December 29, 2024, 12:45am

I appreciate all the feedback but I have a couple problems to solve which will be repeated on the many screens or redrawn on one screen over and over.
Problem 1: getting proper data to display to screen from database read in timely manner
Problem 2: getting the data to be useable

Problem 1 has several solutions and seems to be working however I wanted a script to get the data to make the data useable.

If I put the data in a custom property of say root container
I can't get the string entered by user (by hand or by scanner) to match what is in root container.partnum
I can trim the data input by user but it still doesn't = the data in root container

This seems so easy if I could get scripts to work or expressions to work with the the data
see the expression below - it fails to match

((trim({[default]MLWinTags/localVars/input_string.value}))={Root Container.partnum})

So the whole scripting rabbit hole was to solve both problems.
Retrieve row from database
Separate all columns from that row into individual tags
be able to manipulate those tags to perform datavalidation
The task says use part number 123A4B
The scan of part number says 123A4B
Perfect! - go ahead and assemble or Fail! get the right part for this step

pturmel · December 29, 2024, 1:19am

That often means the column type in the database isn't what you think, perhaps adding padding. You'll have to share more information. I recommend you open the one-row result dataset in the dataset property editor, and copy to clipboard. Paste that here, and format it as if it were code. That will expose the precise data types that are coming through JDBC.

Also, you probably do not want to involve tags in this kind of data entry form. Just use the window/component properties to hold the intermediate value. Write results to tags and/or database when the user "saves" or advances to "next".

Nicole_McCormack · December 29, 2024, 1:39am

I can't get a row of the database this way. I can get it by binding a custom property to a named query separate from the example above, but there seemed to be a lag.

When I added the new text field and followed all the instructions, I got the default value Unknown Step only. It never changes from that in the runtime.

6	20	Inspect bottom tray and scan tray data if it passes inspection.	Safety Glasses, Gloves, and Steel Toe Shoes required	both	Bottom Tray	                  2035035302-A-001	21	21	none

image is my postgressql database column definition
task_description is Inspect ... passes inspection.
safety is Safety glasses ... required
pack_type is both

pturmel · December 29, 2024, 1:54am

Maybe that's your fundamental problem. Does your database have an index on the column(s) used in the WHERE clause? (Doing the query in a script won't "magically" make your database faster.)

But really, get the one row in the dataset custom property, and copy/paste here (not an image).

Consider running your query in the designer's database browser interface to see if there's a lag there.

Based on your schema image, I'd expect your NQ to need comparisons for both taskid and stepnumber in your WHERE clause.

That stepnumber is numeric is probably the problem. That won't come through JDBC cleanly.

Nicole_McCormack · December 29, 2024, 2:42am

the get named query works with all and sundry when executed from a custom property dataset. That is how I got the table to show you.

I was able to with bits and pieces gleaned from each person move the ball and it is working!!!
So thank you.
My solution is probably brute force and not very pretty but working is what matters since I was 'on vacation' yesterday at 5p, about 12 hours of work ago.
I probably have 9 more tomorrow to 'finish'
But I appreciate all of you who answered on your weekends.