system.db.runScalarPrepQuery No longer works in perspective

Ignition Early Access
1

system.db.runScalarPrepQuery no longer works in perspective. I am able to run it from the script console.
I’ve tried running it in a try/except block but did not catch any errors (which actually seems like a bug on its own).

Not sure when it stopped working but I’m using last nights build now.

2

I don’t have any issues with system.db.runScalarPrepQuery (in the script console or a perspective session).
I’m also using today’s build.
Can you paste your code?

3

class_pk = 4
query = “SELECT [RequiresReporting] FROM [dbo].[bDefectClass] WHERE [bDefectClassPK] = ?”
is_reportable = system.db.runScalarPrepQuery(query, [class_pk], ‘VIDES’)

4

I just tried on todays build and had no issue as well.

5

How are you supplying the other properties in the query? ("RequiresReporting", "dbo", "bDefectClass", "bDefectClassPK")

6

The square brackets there are just identifying db objects/tables. The ‘?’ character should be the location of all parameters should it not?

7

Oh, okay, you're just masking the names - got it.
Yes, the "?" will be populated with the supplied argument.
When I intentionally caused my query to fail, I received an error in the Gateway logs:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 3, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:315) 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.runScalarPrepQuery(SELECT quantity FROM beverages WHER name = ?, QADB, [Coca-Cola], )

Note the missing "E" in "WHERE".

When I replace the "E", the function performs as expected. I even tried with a number value just in case, by swapping "quantity" and "name" and supplying the value of a NumericEntryField. I then even tried a query which returned more than one value by replacing the "=" with a ">" and supplying a value of 0, so it seems to be working for arguments which are strings OR numbers, and it seems to be working for queries which return one or more results. I then tried a query which returned no results, and my bound Label took on a text value of "null", so it seems to wrk with queries which return no results as well.

Ca you tell me anything more about the query? What happens if you put logging just before and just after the execution of the query?

8

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 62, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:315) at jdk.internal.reflect.GeneratedMethodAccessor123.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.runScalarPrepQuery(SELECT [RequiresReporting] FROM [dbo].[bDefectClass] WHERE [bDefectClassPK] = ?, VIDES, [4], )

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

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:315)

at jdk.internal.reflect.GeneratedMethodAccessor123.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:188)

at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.call(ScriptManager.java:472)

at org.python.core.PyObject.call(PyObject.java:515)

at org.python.core.PyObject.call(PyObject.java:519)

at org.python.pycode._pyx1605.runAction$1(:104)

at org.python.pycode._pyx1605.call_function()

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

at org.python.core.PyBaseCode.call(PyBaseCode.java:308)

at org.python.core.PyFunction.function___call__(PyFunction.java:471)

at org.python.core.PyFunction.call(PyFunction.java:466)

at org.python.core.PyFunction.call(PyFunction.java:456)

at org.python.core.PyFunction.call(PyFunction.java:451)

at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:702)

at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:820)

at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:82)

at com.inductiveautomation.perspective.gateway.action.ScriptAction.runAction(ScriptAction.java:65)

at com.inductiveautomation.perspective.gateway.action.ActionDecorator.runAction(ActionDecorator.java:18)

at com.inductiveautomation.perspective.gateway.action.SecuredAction.runAction(SecuredAction.java:51)

at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.lambda$call$0(ActionCollection.java:257)

at com.inductiveautomation.perspective.gateway.api.LoggingContext.mdc(LoggingContext.java:54)

at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:246)

at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:215)

at com.inductiveautomation.perspective.gateway.threading.BlockingTaskQueue$TaskWrapper.run(BlockingTaskQueue.java:154)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.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: org.python.core.PyException: Traceback (most recent call last): File “”, line 62, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:315) at jdk.internal.reflect.GeneratedMethodAccessor123.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.runScalarPrepQuery(SELECT [RequiresReporting] FROM [dbo].[bDefectClass] WHERE [bDefectClassPK] = ?, VIDES, [4], )

… 34 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runScalarPrepQuery(SELECT [RequiresReporting] FROM [dbo].[bDefectClass] WHERE [bDefectClassPK] = ?, VIDES, [4], )

… 33 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)

at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:946)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:958)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:910)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:919)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:519)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:519)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:1055)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepQuery(SRConnectionWrapper.java:164)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepQuery(GatewayDBUtilities.java:148)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:308)

9

I was able to get it partly working in perspective. The parameter I was passing in, class_pk, was actually getting its value from “self.props.value”, which returned 4. I confirmed that that was the value and I can also see it in the logs here:

Caused by: java.lang.Exception: Error executing system.db.runScalarPrepQuery(SELECT [RequiresReporting] FROM [dbo].[bDefectClass] WHERE [bDefectClassPK] = ?, VIDES, [4], )

When I hardcode the value of class_pk = 4, it does work but I’m not sure why

10

Try updating your MSSQL JDBC driver. The perspective property node is returning a numeric(ish) type that the JDBC driver you’re using doesn’t know how to interpret.

11

Thanks @PGriffith and @cmallonee I moved the query to a named query until I can update the driver

12

If you log “type(self.props.value)”, I’m guessing you won’t get an “int”. The issue might be that self.props.value isn’t a “number”, and you’re trying to pass it into a place that expects a number. Wrapping it with int(self.props.value) might take care of it for you. (Also, throw a try: except: around that so you can catch an exception in case self.props.value every isn’t actually an int.)

Also, if you do that, post back here. I’d be curious to see what type(self.props.value) returns. Depending on the type, it might be possible that our devs could do some magic on our side to try to infer a native data type and put it into a format that a JDBC driver will happily take before we pass the value to the JDBC driver’s prep query functions.

1 Like
13

@Kevin.McClusky type returned class org.python.core.PyLong and casting it to an int did work.

14

Hmm, unfortunate. I think we'll need to fix this.

@cmallonee can you replicate this?

15

@nburt Is there any chance you could send me the View which contains this failing query either in this thread or via Direct Message? We’re not able to replicate the behavior with the information we have available so far, so we’d like to take a deeper look at the use case.

16

@cmallonee Sure thing. Sent you a message

17

The stacktrace you posted earlier was reproduced but only by downgrading the MSSQL driver to a version that is no longer supported. Can you try the solution @PGriffith suggested and upgrading your driver to a more recent version and seeing if that fixes your issue?

18

What version(s) of the jdbc are supported? And should I not have gotten a supported version included in the install of ignition?

19

There are some support matrixes here: https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-2017

We should be shipping a newer JDBC driver with fresh installations but upgrades retain their existing JDBC drivers.

1 Like
20

I didn’t seem to get a newer JDBC driver after the fresh install.

  • I ran the uninstall.exe file
  • Ran the installer from the most recent build
  • Started Ignition
  • Restored my gateway backup

I hit the same issue as before so I looked for the JDBC driver in \Inductive Automation\Ignition\user-lib\jdbc. I see I have sqljdbc4.jar in that folder, which I assume should be at least sqljdbc42.jar if not a newer version?