'system.db.execUpdate()' with 'getKey = True' raise an exception

Ignition 8.3 Early Access
, , , ,
1

Hello,

I’m trying to run two named queries inside a form submission script.

The second one need the primary key of the first. So I enabled getKey, but now I get an exception inside the the function system.db.execUpdate().

A bug ?

Thank you :slight_smile:

My script :

def handleSubmission(session, name, data, formContext, sessionContext, retry):
    recipeParameters = {k: v for k, v in data.items() if k != 'categories'}
    recipe_id = system.db.execUpdate(path = 'Recipes/AddRecipe', parameters = recipeParameters, getKey = True) # <------- error here
	
    for category in data['categories']:
	    categoryParameters = {'recipe_id': recipe_id, 'category_id': category}
	    system.db.execUpdate(path = 'Recipes/AddCategoryToRecipe', parameters = categoryParameters)
	
    result = {'success': True}
    result

And the exception raised on the first query

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "<function:handleSubmission>", line 3, in handleSubmission at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execUpdate(AbstractDBUtilities.java:718) at jdk.internal.reflect.GeneratedMethodAccessor137.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.lang.Long cannot be cast to class java.lang.Integer (java.lang.Long and java.lang.Integer are in module java.base of loader 'bootstrap')

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

at org.python.pycode._pyx226.handleSubmission$1(<function:handleSubmission>:14)

at org.python.pycode._pyx226.call_function(<function:handleSubmission>)

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

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

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

at org.python.core.PyFunction.__call__(PyFunction.java:469)

at org.python.core.PyFunction.__call__(PyFunction.java:464)

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

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

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:1167)

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

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:1249)

at com.inductiveautomation.perspective.gateway.script.SessionScriptFunctionHelper.execute(SessionScriptFunctionHelper.java:98)

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

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

at com.inductiveautomation.perspective.gateway.form.FormSubmissionManagerImpl$FormSubmissionTask.run(FormSubmissionManagerImpl.java:218)

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 com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58)

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

Caused by: org.python.core.PyException: java.lang.ClassCastException: java.lang.ClassCastException: class java.lang.Long cannot be cast to class java.lang.Integer (java.lang.Long and java.lang.Integer are in module java.base of loader 'bootstrap')

... 27 common frames omitted

Caused by: java.lang.ClassCastException: class java.lang.Long cannot be cast to class java.lang.Integer (java.lang.Long and java.lang.Integer are in module java.base of loader 'bootstrap')

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execUpdate(AbstractDBUtilities.java:718)

at jdk.internal.reflect.GeneratedMethodAccessor137.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)

... 24 common frames omitted
2

Can you share the queries and the entire script? It's hard to troubleshoot line numbers when you don't have the rest of the script. Also there is a cast Long to Int error there and we would need to see the query setup for params.

3

The query (I’m using PostgreSQL)

INSERT INTO recipes (title, description, servings, prep_time, cook_time)
VALUES
	(:title, :description, :servings, :prep_time, :cook_time)

And this is the entire script. It’s coming from a form from. I just added the ‘def handleSubmission()’ line at the top in my initial post.

4

This looks like it's just a bug in the implementation of system.db.execUpdate. It's assuming the result will be java.lang.Integer and, in this case, it's java.lang.Long.

I made a ticket (IGN-14299).

1 Like
5

Thank you, I found a workaround

With PostgreSQL you can add at the end of the query.

RETURNING id

And then instead of ­­execUpdate(), just use execQuery()

1 Like