SQL type conversion error

Ignition
, ,
1

Good Morning.

I am trying to populate a page with SQL and I have run into some type conversion issues. I have broken it down to just trying to get a button press to populate one value into a numeric text field. I have verified the column I am working with is an INT in sql. Below is the code that I am using, as well as the error I get. When I run the first line of code in the SQL query browser, it returns the expected value. Can anyone provide some insight as to what I might be missing here?

target1Value = "SELECT setpoint1 FROM recipes WHERE id = 1"

event.source.parent.getComponent('Group 15').getComponent('Group').getComponent('Numeric Text Field').intValue = target1Value

Traceback (most recent call last):
File "event:actionPerformed", line 14, in
TypeError: can't convert 'SELECT setpoint1 FROM recipes WHERE id = 1' to int

at org.python.core.Py.TypeError(Py.java:234)
at org.python.core.Py.tojava(Py.java:566)
at org.python.core.PyBeanProperty._doset(PyBeanProperty.java:64)
at org.python.core.PyObject.__set__(PyObject.java:3738)
at org.python.core.PyObject.object___setattr__(PyObject.java:3802)
at org.python.core.PyObject.object___setattr__(PyObject.java:3792)
at org.python.core.PyObject$object___setattr___exposer.__call__(Unknown Source)
at org.python.core.PyObjectDerived.__setattr__(PyObjectDerived.java:1043)
at com.inductiveautomation.factorypmi.application.script.PyComponentWrapper.__setattr__(PyComponentWrapper.java:162)
at org.python.pycode._pyx28.f$0(<event:actionPerformed>:14)
at org.python.pycode._pyx28.call_function(<event:actionPerformed>)
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 com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:804)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:207)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:300)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
at jdk.proxy2/jdk.proxy2.$Proxy62.actionPerformed(Unknown Source)
at java.desktop/javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at java.desktop/javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.desktop/java.awt.Component.processEvent(Unknown Source)
at java.desktop/java.awt.Container.processEvent(Unknown Source)
at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
at java.base/java.security.AccessController.doPrivileged(Unknown Source)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
at java.base/java.security.AccessController.doPrivileged(Unknown Source)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.run(Unknown Source)

Ignition v8.1.44 (b2024102210)
Java: Azul Systems, Inc. 17.0.12

2

This doesn't run a query, it just assigns that query string to a string variable.

You need to actually run the query: system.db.runQuery | Ignition User Manual

4

Your variable targetValue is a string, not the result of an SQL execution.
To get data from a database via SQL try:

  1. Best Practice: create a NamedQuery and set the Query Type to Scaler Query then use system.db.runNamedQuery to put the 1st row/column into a variable
  2. to get a single value without a named query use system.db.runScalerPrepQuery
  3. system.db.runScalerQuery

Other commands to keep in mind when working with database data:

  1. system.db.runPrepQuery
  2. system.db.runQuery
2 Likes