OL Learn

Unable to use automation.parameters into a custom query

Hi,

Have systematically JavaNullException when I try to use parameter into a custom SQL query.
The parameter is correctly defined into the datamapper in the right pane.
and the SQL look like :
= exec dbo.GetBP ‘{automation.parameters.numCommande}’

If I use constant value in place of parameter all work fine.

Thanks for your help.

HH

numCommande is defined as parameter.

SQL Query is : = exec dbo.GetBP ‘{automation.parameters.numCommande}’

Thanks.

ERROR [05 juil. 2021 14:17:03,227][main] com.objectiflune.datamining.scripting.utils.DynamicTextParser.parseDynamicXpath(DynamicTextParser.java:-1) com.objectiflune.datamining.scripting.scopes.DataminingEnvironment$UndefinedVariableException: The property numCommande is not defined in this scope (DME000021)
javax.script.ScriptException: com.objectiflune.datamining.scripting.scopes.DataminingEnvironment$UndefinedVariableException: The property numCommande is not defined in this scope (DME000021)
at com.objectiflune.datamining.scripting.utils.ContextBasedScriptExecutor.runJs(ContextBasedScriptExecutor.java)
at com.objectiflune.datamining.scripting.utils.f.parse(f.java)
at com.objectiflune.datamining.scripting.utils.DynamicTextParser.parseDynamicXpath(DynamicTextParser.java)
at com.objectiflune.datamining.scripting.utils.DynamicTextParser.parseDynamicXpath(DynamicTextParser.java)
at com.objectiflune.datamining.ui.model.DataMiningModel.parseDynamicText(DataMiningModel.java)
at com.objectiflune.datamining.ui.perspective.inputdata.SQLQueryDialog.a(SQLQueryDialog.java)
at com.objectiflune.datamining.ui.perspective.inputdata.SQLQueryDialog.access$0(SQLQueryDialog.java)
at com.objectiflune.datamining.ui.perspective.inputdata.g.run(g.java)
at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:72)
at com.objectiflune.datamining.ui.perspective.inputdata.f.call(f.java)
at com.objectiflune.ui.framework.support.OLCallbacks.call(OLCallbacks.java)
at com.objectiflune.ui.framework.support.OLSelectionCallbacks.widgetSelected(OLSelectionCallbacks.java)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:252)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4105)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1037)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3922)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3524)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:823)
at org.eclipse.jface.window.Window.open(Window.java:799)
at com.objectiflune.datamining.ui.perspective.inputdata.c.call(c.java)
at com.objectiflune.ui.framework.support.OLCallbacks.call(OLCallbacks.java)
at com.objectiflune.ui.framework.support.OLSelectionCallbacks.widgetSelected(OLSelectionCallbacks.java)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:252)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4105)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1037)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3922)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3524)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$5.run(PartRenderingEngine.java:1160)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1049)
at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:155)
at org.eclipse.ui.internal.Workbench.lambda$3(Workbench.java:658)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:557)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:154)
at com.objectiflune.application.Application.start(Application.java)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:203)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:137)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:107)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:401)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:255)
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.eclipse.equinox.launcher.Main.invokeFramework(Main.java:657)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:594)
at org.eclipse.equinox.launcher.Main.run(Main.java:1447)
at org.eclipse.equinox.launcher.Main.main(Main.java:1420)
Caused by: com.objectiflune.datamining.scripting.scopes.DataminingEnvironment$UndefinedVariableException: The property numCommande is not defined in this scope (DME000021)
at com.objectiflune.datamining.scripting.ScriptableAutomation$ScriptableAutomationProperty.get(ScriptableAutomation.java)
at org.mozilla.javascript.ScriptableObject.getProperty(ScriptableObject.java:2320)
at org.mozilla.javascript.ScriptRuntime.getObjectPropNoWarn(ScriptRuntime.java:1607)
at org.mozilla.javascript.gen.c_24._c_script_0(:0)
at org.mozilla.javascript.gen.c_24.call()
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:412)
at com.objectiflune.scripting.engine.OLContextFactory.doTopCall(OLContextFactory.java)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3545)
at org.mozilla.javascript.gen.c_24.call()
at org.mozilla.javascript.gen.c_24.exec()
at com.objectiflune.scripting.engine.Script.exec(Script.java)
at com.objectiflune.datamining.scripting.b.run(b.java)
at org.mozilla.javascript.Context.call(Context.java:559)
at org.mozilla.javascript.ContextFactory.call(ContextFactory.java:522)
at com.objectiflune.scripting.engine.OLContextFactory.call(OLContextFactory.java)
at com.objectiflune.datamining.scripting.DataminingScriptEngine.run(DataminingScriptEngine.java)
… 51 more

Hi @Herve_Heritier,

Are you running the shared SQL query by a Action Step? If that’s true, can you please share the used JavaScript code? Otherwise, can you please share a screenshot of the area/field of your Data Mapping Configuration in which you’re using the SQL query?

Parameter definition in the right pane :

parameters

Custom SQL Query :

sql%20query

Steps :

Thank you for sharing the screenshots.

I can see that you’re using the Parameter “numcommande” with a lower case “C”, can you let me know please if the issue does still occur when you use the SQL Qeury as described in your forum post:

=exec dbo.GetBP '{automation.parameters.numCommande}'

P.S. Based on the shared error message “The property numCommande is not defined in this scope” I suppose that the answer will be yes but I would like to double check this.

This is an error from myself : I use numCommande as you can see in a previous post :

SQL Query is : = exec dbo.GetBP ‘{automation.parameters.numCommande}’

Can you please check what will happen when you use a Job Info variable, as in the following SQL Qeury for example, instead:

=exec dbo.GetBP '{automation.jobInfo.JobInfo9}'

I try it and I’m obtain the same error.

Can you run one last test please?

Can you share the Info, Warn or Error message which will be shown in the Message panel of the Connect Designer application when you run the (1) custom query:

= exec dbo.GetBP '452674'

And when you run (2) the custom query:

= exec dbo.GetBP '{automation.parameters.numCommande}'

I’m asking you this because I’m able to run the following custom query for example in which I also make use of a Runtime Parameter:

= SELECT * FROM users WHERE USERNAME='{automation.parameters.username}'

Where in my case the following Info message will be shown in the Messages panel:

= SELECT * FROM users WHERE USERNAME=’{automation.parameters.username}’ parsed into SELECT * FROM users WHERE USERNAME=‘John Doe’ (Dm_1742)

Which version of OL Connect you use ?

For mysel is 2020.2.1

I ran the test in version 2021.1.1 of PlanetPress Connect.

Perhaps the problem is solved with this version.

It’s difficult to make an upgrade actually, because I’m in huge production of documents.

I’m afraid to wait this winter to do it…

I can also run the same custom query without any problem in version 2020.2.1 of PlanetPress Connect. I don’t get any error when I run the following custom query, which I also ran in version 2021.1.1 of PlanetPress Connect:

= SELECT * FROM users WHERE USERNAME='{automation.parameters.user}'

@Marten

Support found the problem. I put a property inside the preprocessor. The datamapper fail when he create this property then he don’t create the automation object correctly.

I delete the preprocessor property and then all work fine.

Support Incident : #189903

Thank’s again for your help.

Hello together,

I have a similar problem with {automation.JobInfo.JobInfo9}.
This variable is set in Workflow: %9 %y%m%d.

In the (database)-datamapper this variable should be used to select data from the actual date.
The SQL looks like this:

=SELECT * FROM [AS400].[***].[***].[LETTERS]
WHERE DR1_DATUM_N08 = {automation.jobInfo.JobInfo9}
ORDER BY AT2E_KDNR, AT2E_LFDNR7

Some times this works in the datamapper, and otherwise I got an error when I execute this query.
When I write the date directly in the sql-statement it will always work, but I do not want to change the date every day by hand.

I have no properties set inside the preprocessor.
What could be the problem?

Many thanks.

Hello,

Coud you try the same thing with a datamapper parameter ?

Hi @NBecker,

Can you please share the error you get when you’re facing this issue?

Does the JobInfo9 automation property have a Default Value when you open the Data Mapping Configuration file in the Connect Designer?

That is the mesaage I get, when executing the sql statement.
The message occurs when JobInfo9 has a value, or JobInfo9 is empty.

Can you also check what has been logged in the Messanges panel of the Connect Designer at the moment the issue does occur?

Does the issue also occur when you put quotes around the JobInfo automation property, like:

= SELECT * FROM [XYZ].[V_PRINT_LETTERS_KATANF01]
WHERE DR1_DATUM_N08='{automation.jobInfo.JobInfo9}'

After putting into: ‘{automation.jobInfo.JobInfo9}’ the same error occurs.