Trouble with .getSelectedValues() from a List component

JoshM · March 2, 2018, 8:12pm

I am new to Ignition so please forgive my hopefully basic questions but I have been hunting for an answer for a couple hours now and seem to be no closer to finding a solution.

The documentation for a List component says that .getSelectedValues() returns an Object[], but what is this object?It does not seem to behave like standard DataSet or a pyDataSet.

When I try to convert it to a pyDataSet (as suggested here) I am getting a '1st arg can’t be coerced" error.

When I try to use it as standard DataSet (to list in a table) I am getting a “can’t convert to com.inductiveautomation.ignition.coomon.Dataset” error.

I can reference one data point at a time explicitly calling out the array location but I am not sure how that is helpful without being able to use the other functions such as rowCount to manually correct the output. The list is comprised of a simple sql query of string data points.

Here is my test script that I think should work(this is set to happen on property change of the list)
dataset = event.source.getSelectedValues()
event.source.parent.getComponent(‘Table5’).data = dataset

Any guidance would we wonderful.

MMaynard · March 2, 2018, 8:30pm

It is returning an array.
So you can loop it and convert it if you want it in a table,
Off the top of my head here so bear with me

hdr = ['Values']
ds =
for r in event.source.getSelectedValues():
row = [r]
ds.append(row)
event.source.parent.getComponent('Table5').data = system.dataset.toDataSet(hdr,ds)

JoshM · March 2, 2018, 8:39pm

Yep, knew it would be an easy solution. Thank you for such a perfect and prompt response!

JoshM · March 2, 2018, 10:35pm

So maybe you can help with one more thing.

Now that it is a Dataset it is almost behaving the way I would expect, but not exactly. Instead of listing those returned values on a table, I want to use that data set in a WHERE IN SQL query. As I have it when I have only 1 thing select in the list it runs as it queries correctly and returns the data to the table. However as soon as I select a second item it fails to build the query correctly with the following error.

My Code:

hdr = ['BatchID']
ds=

for r in event.source.getSelectedValues():
row =[r]
ds.append(row)

/# Intermediate Troubleshooting Step
dataset = system.dataset.toDataSet(hdr,ds)
event.source.parent.getComponent('Table5').data = dataset

/# Desired Bulk Query
databaseconnection ="DBManual"

selectquery = "SELECT SampleID from batchsamples where BatchID IN (?)"
selectargs = dataset
sampleselectquery = system.db.runPrepQuery(selectquery, selectargs, databaseconnection)

event.source.parent.getComponent('TableExp').data = sampleselectquery

Error when selecting two items.

Traceback (most recent call last):
File "event:mouseClicked", line 20, in
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:287)

at sun.reflect.GeneratedMethodAccessor421.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

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

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(SELECT SampleID from batchsamples where BatchID IN (?), DBManual, [TT368, TT369], )

at org.python.core.Py.JavaError(Py.java:495)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:287)
at sun.reflect.GeneratedMethodAccessor421.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.call(ScriptManager.java:429)
at org.python.core.PyObject.call(PyObject.java:422)
at org.python.core.PyObject.call(PyObject.java:426)
at org.python.pycode._pyx635.f$0(event:mouseClicked:22)
at org.python.pycode._pyx635.call_function(event:mouseClicked)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:634)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:180)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:271)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
at com.sun.proxy.$Proxy31.mouseClicked(Unknown Source)
at com.inductiveautomation.factorypmi.application.components.util.EventDelegateDispatcher$MouseEventDispatcher.mouseClicked(EventDelegateDispatcher.java:97)
at java.awt.AWTEventMulticaster.mouseClicked(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.Exception: Error executing system.db.runPrepQuery(SELECT SampleID from batchsamples where BatchID IN (?), DBManual, [TT368, TT369], )
... 52 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Parameter index out of range (2 > number of parameters, which is 1).
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:819)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:789)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepQuery(ClientDBUtilities.java:204)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:284)
... 50 more
Caused by: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)
at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4616)
at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:4046)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:1055)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:118)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:77)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:405)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:518)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
at java.lang.Thread.run(null)

Ignition v7.9.5 (b2017111615)
Java: Oracle Corporation 1.8.0_161

pturmel · March 2, 2018, 11:41pm

A question mark substitution can only provide a single value to the SQL query. You’ll have to use the length of the list to produce the correct number of question marks.

MMaynard · March 3, 2018, 12:11am

WhereClause=''
for r in event.source..getSelectedValues():
	WhereClause = WhereClause + "'" + str(r) + "',"
finalWhere = '('+WhereClause.rstrip(',')+')'
selectstring = "SELECT FROM MyTable WHERE MyField in " + finalWhere
print selectstring

You can build up your where clause into a comma separated list and then use it as a WHERE IN