[IGN-13835] Scalar named query with system.db.execQuery fails

Ignition 8.3 Early Access
1

Hi,
The new call for executing scalar named queries fails when executing.
Example of simple named query

image
image529×300 14 KB

A call with system.db.runNamedQuery works
A call with system.db.execQuery fails with the following error :

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 1, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execQuery(AbstractDBUtilities.java:688)

	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.ClassCastException: java.lang.ClassCastException: class java.sql.Timestamp cannot be cast to class com.inductiveautomation.ignition.common.Dataset (java.sql.Timestamp is in module java.sql of loader 'platform'; com.inductiveautomation.ignition.common.Dataset is in unnamed module of loader java.net.URLClassLoader @44ea2863)


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

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

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

	at org.python.pycode._pyx314.f$0(<input>:1)

	at org.python.pycode._pyx314.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: class java.sql.Timestamp cannot be cast to class com.inductiveautomation.ignition.common.Dataset (java.sql.Timestamp is in module java.sql of loader 'platform'; com.inductiveautomation.ignition.common.Dataset is in unnamed module of loader java.net.URLClassLoader @44ea2863)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execQuery(AbstractDBUtilities.java:688)

	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)

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

	... 19 more

Traceback (most recent call last):
  File "<input>", line 1, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execQuery(AbstractDBUtilities.java:688)

	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.ClassCastException: java.lang.ClassCastException: class java.sql.Timestamp cannot be cast to class com.inductiveautomation.ignition.common.Dataset (java.sql.Timestamp is in module java.sql of loader 'platform'; com.inductiveautomation.ignition.common.Dataset is in unnamed module of loader java.net.URLClassLoader @44ea2863)
2

What is this? This isn't a documented function. system.db | Ignition User Manual

Whats wrong with using system.db.runNamedQuery? You're defining a named query and that's what I would expect to call it.

Always helps if you can share your script too.

Did not see version. My fault.

Not for nothing either but if you just want the current time system.date.now() is available to you unless you specifically want the databases time.

3

This concerns Ignition 8.3, where system.db.runNamedQuery is deprecated

image
image823×268 20.1 KB

I tagged the topic with the 8.3 category.

The query returning the current datetime was just an example. No use in giving my specific scalar query as no one would be able to reproduce it without our database structure.

2 Likes
4

Considering that execQuery and execUpdate are distinct, there's probably going to be an execScalar or similar. It just isn't documented yet.

5

Maybe.
That would change from 8.1 where "normal" and scalar named queries were handled by the same function.
And documentation would need to be changed system.db.execQuery | Ignition User Manual

6

It would be in line with the old system.db.runQuery vs runUpdateQuery vs runScalarQuery (and their prep equivalents) though I get that runNamedQuery was a catch all for all 3. I guess its a return to the old form.

7

As currently implemented, execQuery is supposed to be used for scalar results and that it doesn't is a bug -- thanks @automatisation for the report.

But I think I've convinced myself that I actually do want to add a distinct execScalar function, to give you a sort of "type safety" where you have to assert that you know what kind of query you're about to execute even before you execute it. You're basically never going to do the 'right' thing with a result if you were expecting a scalar/dataset/result count but you got something else, so it seems fine to me outside of maybe a contrived use case where you're trying to make a decorator around 'any type of named query'...but again, what would you have been doing with the results from that before?

3 Likes
8

Since we cannot do

Dataset ds = system.db.execQuery("SomeQ")
int i = system.db.execQuery("ScalarQuery")

I personally am in favor of the more descriptive naming so if I see execScalar in code I know I would expect on value there to be a single thing. Otherwise I am having to go look at the SQL.

1 Like
9

Distinct functions or additional parameter, as long as it works, I'm happy.
I'm using the deprecated function call at the moment and it works, so all good.
I'll check again if a new 8.3 version ships in the future.
Thanks

1 Like
10

Yeah, in case this wasn't clear, while we reserve the right to deprecate scripting functions (and there is a larger than usual set of deprecated functions in 8.3) we essentially never remove any functionality - so the old function calls will absolutely continue to work indefinitely. There's no urgent need to migrate them just because you're on 8.3.

2 Likes
11

"Essentially never" means we are finally removing the alert scripting functions that were already deprecated when I joined IA in 2013.

4 Likes