beginTransaction in Project Script without legacy access

Ignition
1

I'm a little confused about some aspects of how Legacy Database Access works with Vision clients, particularly when they are using Project Library scripts instead of bindings or component scripts. It seems sort of related to this earlier post, Client Permissions, but not quite the same thing.

We have a Vision project where we use Named Queries for all of the component bindings. For updates to the database, we are using stored procedures. We have found that Named Queries were not working correctly when calling our stored procedures, so we are calling those from Project Library scripts, using the standard system.db scripting functions. So far, so good.

The problem arose when I tried to wrap multiple stored procedures calls within a transaction. While we have had no problems creating the calls, setting parameters, and executing them without Legacy Database Access enabled, I could not get past system.db.beginTransaction without raising an exception. Then I enabled Legacy Database Access, and I was able to create the transaction, use it in the stored procedure calls, commit it, and close it.

It appears that even though we can use stored procedures in a Project Library script, we cannot begin a database transaction. Is this by design? If not, is the problem that we can't begin the transaction, or that we can run the stored procedures?

Or maybe this is a red herring, and I'm missing the actual cause of the problem. Here's the full exception that I'm getting, which again only happens when using a database transaction with Legacy Database Access disabled.

Traceback (most recent call last):
File "event:actionPerformed", line 14, in
File "module:dataModels.wms.receipt", line 286, in updateHeaderAndLinesFromDatasets
File "module:shared.exception", line 64, in logAndRaise
File "module:shared.exception", line 64, in logAndRaise
File "module:dataModels.wms.receipt", line 284, in updateHeaderAndLinesFromDatasets
File "module:repositories.wms.receipt", line 566, in updateReceipt
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.beginTransaction(AbstractDBUtilities.java:429)
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.beginTransaction(PhoenixVision_PhoenixWMS, 2, 30000)

at org.python.core.PyException.doRaise(PyException.java:205)
at org.python.core.Py.makeException(Py.java:1565)
at org.python.core.Py.makeException(Py.java:1569)
at org.python.core.Py.makeException(Py.java:1573)
at org.python.core.Py.makeException(Py.java:1577)
at org.python.pycode._pyx148.f$0(<event:actionPerformed>:22)
at org.python.pycode._pyx148.call_function(<event:actionPerformed>)
at org.python.core.PyTableCode.call(PyTableCode.java:171)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1614)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:782)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:206)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:297)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
at com.sun.proxy.$Proxy49.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(Native Method)
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(Native Method)
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)

Caused by: org.python.core.PyException: Traceback (most recent call last):
File "event:actionPerformed", line 14, in
File "module:dataModels.wms.receipt", line 286, in updateHeaderAndLinesFromDatasets
File "module:shared.exception", line 64, in logAndRaise
File "module:shared.exception", line 64, in logAndRaise
File "module:dataModels.wms.receipt", line 284, in updateHeaderAndLinesFromDatasets
File "module:repositories.wms.receipt", line 566, in updateReceipt
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.beginTransaction(AbstractDBUtilities.java:429)
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.beginTransaction(PhoenixVision_PhoenixWMS, 2, 30000)

... 50 common frames omitted
2

I’ve only looked at this briefly, but it seems to me that the bug is that you can run prepared statements from a client without the legacy DB client permission. It seems correct and intentional that you can’t begin a transaction without it.

1 Like
3

Which “standard system.db scripting functions” are you using?

runPrepQuery and runPrepUpdate are protected by the legacy client access, but execSProcCall is not.

4

I am specifically referring to those functions used when calling stored procedures, so yes execSProcCall. If those are allowed, I don’t see why beginTransaction is not. This leaves no way to run multiple procedures in a single transaction which can be rolled back if any of them fail.

5

I’ll raise the issue for review. I’m not sure why the transaction-related system.db functions are protected.

1 Like
6

Thanks! And I guess this answers a question I had, which is do these project scripts run on the client or on the gateway. I had assumed that component scripts ran on the client, but project scripts used in the client were actually running on the gateway. But it sounds like they are actually being executed on the client, correct?

If there is something we really need to run on the gateway, would we have to invoke it via system.util.sendMessage?

7

Project scripts can be called from both ‘gateway’ and ‘client’ scopes - the call source matters, not where the code is written. sendMessage/sendRequest are the best option to get something to run on the gateway.

1 Like
8

All script functions run in the environment they are called from, except for Perspective, where "called from" is really a special scope in the gateway. Project vs. shared vs. inherited is not relevant.

1 Like