Groovy Function
This function is used to execute a script in the Groovy programming language. Groovy is a scripting language that is closely based on Java syntax, but also allows a simplified syntax. It is characterized by its dynamic typing, high execution speed and good integration into the Java Virtual Machine. The Groovy function can be used to program very individual transformations implemented at runtime. It is also suitable for mathematical calculations.
More information about the Groovy programming language is available at http://groovy-lang.org.
Some SMTP-related Groovy functions are obsolete and will raise a deprecation warning, starting with the 2024 Fall release (24.2). See Deprecations and Breaking Changes for details.
Function Editor
Access the Function Editor through FieldTransform.
Input fields
The input fields can be used in the script as Groovy variables under the following conditions:
- If an alias is defined for the input field, it defines the Groovy variable name. Valid characters for an alias are uppercase and lowercase letters, numbers and the symbols "_" and "$". The alias must not start with a number and must not be a Groovy keyword (e.g. "my_InputField1$" is a valid alias). The alias is a valid Groovy identifier name.
- If no alias is defined, then the name of the input field is the Groovy variable name. In that case, it must be a valid Groovy identifier name.
- Additionally, the variable name in the form "_input<i>" can also be used where <i> is the position of the input field. For example, "_input1" can be used for the first input field and "_input2" for the second.
Note: we recommend you use the alias name. As best practice the variable name should be in lower camel case e.g. "myvariable1". If the name in the input is identical to a class in a standard Java library e.g. "Currency" an alias name is obligatory (otherwise the Groovy script would assign the value to the Java class).
A type can be assigned to each input field that defines the type of the Groovy variable in the script. If necessary, a type conversion of the input data is done before the script is executed (with the exception of Object type, which does no conversion and keeps the input type). This is especially helpful for numerical inputs and calculations.
Parameters
Script | Groovy script |
Type | The data type of return value. The default data type is string. The assignment of a type is optional. |
Buffered | If set, the function results are cached and the script is not evaluated for identical input values (default). If not set, the script is always evaluated for all rows. This is necessary if the result depends on the row number or a random number generator. |
Null default | Defines a fallback value to be used when the incoming value is NULL. If checked, then a specific fallback for the data type used in the function will be used, e.g. empty string for string data type, 0 for integer data type, etc. |
The result of the function is determined in the script with a return statement.
A script that is intended to be interruptible by Integrator Server must use Java's sleep method, e.g. Thread.sleep(15000). For more information, see https://docs.oracle.com/javase/7/docs/api/java/lang/Thread.html#sleep(long).
Documentation on Groovy syntax and a list of all keywords can be found here.
Besides the functionality offered by Groovy (and Java), specific Jedox Integrator functionality can be used via the Jedox Integrator Scripting API.
Note that Groovy does not support any UI interactions, such as pop-ups. At the end of this article you find a list of the allowed classes and packages for Groovy sandbox.
Notes
- For performance reasons, the Groovy function internally buffers the results, so that a script is executed only once for the same set of input values. This may lead to wrong values if the script doesn't depend only on the input values, e.g. random value functions, use of setProperty()/getProperty(). In this case, a RowNumber function should be added to the input list of the script function so that it is always unique.
- The scripting functions (undo, redo, copy, paste, and search) are only limited to the current function.
Example
Input | Type | Alias |
OrderQty | double | |
Unit Price | double | price |
Possible valid scripts | ||
return _input1*_input2; | ||
return OrderQty*price; | ||
double OrderValue= OrderQty*_input2; return OrderValue; |
Groovy Sandbox mode
Groovy Sandbox mode is enabled by default in all Jedox environments. There are two security layers: one that is enforced before a script is executed (static checks), and a second at runtime while executing the script.
When getting a scriptHandler and then running execute on it in the same line, Groovy is not able to tell that this is a method we want to execute. But when the script is split into two lines, Groovy is able to understand the type and the script is executed successfully.
For example, the following line will result in an error:
conn.getScriptHandler().execute(dimScript,"Demo",variables);
By splitting the script into two lines, whereby the type is first specified, the script will work:
com.jedox.palojlib.main.ScriptHandler scriptHandler = conn.getScriptHandler();
scriptHandler.execute(dimScript,"Demo",variables);
Allowed classes for Groovy Sandbox
In Jedox, Groovy runs in sandbox mode to ensure high security for your environment. Groovy jobs / functions are analyzed at runtime, using only certain classes that are considered safe.
Only the following classes and packages are allowed:
- com.azure.storage.blob.specialized.BlobInputStream
- com.jedox.etl.components.scriptapi.MailAPI
- com.jedox.palojlib.interfaces.*
- com.jedox.palojlib.main.*
- groovy.json.*
- groovy.sql.*
- groovy.time.*
- groovy.xml : MarkupBuilder, MarkupBuilderHelper, XmlSlurper only
- groovy.xml.slurpersupport.*
- java.io : BufferedInputStream, BufferedOutputStream, ByteArrayInputStream, BytArrayOutputStream, StringWriter only
- java.lang : Boolean, Byte, Character, Double, Long, Float, Integer, Long, Math, Object[ ], Short, String, StringBuffer, StringBuilder only
- java.math.*
- java.net.URLEncoder
- java.nio.charset.*
- java.sql.Date
- java.text.*
- java.time.*
- java.util.*
- org.apache.commons.lang3.*
- org.apache.groovy.json.internal.*
- org.apache.http.conn.EofSensorInputStream
- org.codehaus.groovy.runtime: EncodingGroovyMethods, GStringImpl only
- org.apache.poi.ss.usermodel.DateUtil
- sun.nio.cs.*
- sun.util.calendar.*
- All Java primitive types, their corresponding object and array classes
If any other class is used (e.g. java.lang.System or java.lang.ProcessBuilder), you will get an error message.
See also Groovy Job.
Updated December 9, 2024