When you need to automate some Mapping expressions and operations which are commonly used, you can use the User Defined Functions feature.
It offers the possibility to define through a dedicated Metadata a list of functions, with the possibility to make them dynamic on technologies implementation.
This article gives an overview on how to start working with User Defined Functions in Stambia.
Prerequisites:
- Stambia DI Designer S19.0.0 or higher
- User Defined Functions are supported only in Mappings
Metadata definition
The first step is to create a User Defined Functions Library Metadata, which will contain your future functions and implementations.
You'll then be able to define your functions accordingly to your requirements and start using it in Mappings.
Each User Defined Functions Library will be associated to a defined prefix that will next be used to call the functions.
Metadata creation
Create first the Metadata, click next, choose a name and click on finish.
The Helper Wizard will open and let you define the prefix to use for this function library.
It also offers the possibility to pre-define functions that the wizard should create automatically; this part is optional, and we'll come back on it later in the article.
For the moment, specify the prefix that will be used to call the functions, and click on finish.
The prefix defines the keyword that will be used to access the functions of the User Defined Functions Library.
Each Metadata will have its unique prefix and it offers the possibility to separate and structure your functions, if required.
In this article we decided to use the 'demo' prefix, so our functions will be next called like this:
demo::myfunction(myparam1,myparam2)
The 'user' prefix is reserved and cannot be used as prefix for User Defined Functions.
Function definition
Creation of the function
To add a function to the library, simply right click on the root node, and select New > Function
Specify then the function's name and optionally a description:
Definition of input parameters
Our function being created, we can now add its input parameters.
Right click on the function and select New > Parameter
As our function will have two input parameters, we did it two times in this example:
Definition of Implementations
We now have our function and its parameters, so we can start making implementations.
An implementation will contain the code expression that the function should use for the specified technologies.
Right click on the function and select New > Implementation
Here is the overview of our implementation:
About Product(s)
In Product(s) box, you'll specify technologies the implementation will be used for.
This means that when using the function in a Mapping, the implementation that will be used will be automatically calculated based on involved technologies.
For instance, if we are using the function on a Hypersonic SQL (HSQL) table in a Mapping, implementation for Hypersonic SQL (HSQL) will be used.
You can click on the button next to the box to add new products to the implementation.
In this example, our implementation will be used on Hypersonic SQL (HSQL) and Oracle.
You can create as many implementations as required to handle the specificities of each technology.
This offers the possibility to have one function that will work on all implemented technologies seamlessly.
In this other example, we created a dedicated implementation for Microsoft SQL Server that will, for instance, use a different expression than the one defined for Hypersonic SQL and Oracle.
When using the function on Microsoft SQL Server in a Mapping, it will therefore use automatically its dedicated implementation.
Note that a Product (E.g. Hypersonic SQL) must be used only in one implementation of a function, you cannot have multiple implementations for the same Product on the same function.
About Expression
Expression box is the place where the function's code will be written.
You can use in all the functions that are supported by the products you specified, xpath expressions, and the input parameters.
The syntax to use the input parameters is the following:
$<parameterName>
For instance, to use 'param01' of previous example:
$param01
And final expression, using 'concat' function available in Hypersonic SQL and Oracle:
Using other User Defined Functions in implementation
Expression of a function can contain calls of other User Defined Functions.
Syntax to be used is the same as when using a User Defined Function in a Mapping: <prefix>::<function>
When your are using another function of the same library, there is nothing particular to do, this will work seamlessly.
But note that if you want to use in implementation a User Defined Function of another library (of another Metadata), you have to add this library as required library on root node:
It can then be used in expression:
In this example, the expression is using a function "anotherfunction" which is defined in another User Defined Functions library called "other".
Prerequisites:
- Stambia DI Designer S19.0.18 or higher
Using the Wizard (alternative)
A wizard is available to simplify the two first steps of the function's definition: creation of the function and definition of the parameters.
These two steps can be done automatically by the wizard that allows to define a list of functions signatures to create.
To access the Wizard, right click on the root node of the User Defined Functions Metadata, and click on Actions > Launch Library Wizard
You'll be back on the popup window that also appears at Metadata creation.
- Specify the function to create, with its parameters
- Then click on the '+' button to add it to the defined functions
- Repeat point 1 and 2 for each function you want to create
- All the defined functions will be created when clicking on Finish
You can use this to create a bunch of functions and focus then on their implementations.
Miscellaneous - Having multiple Metadata with same prefix in workspace
As previously indicated, the prefix defined in a User Defined Functions Metadata must be unique in the Workspace, as Stambia will use it to identify the functions.
You can have multiple User Defined Functions Metadata without any problem, by simply specifying a different prefix in each of them.
When multiple Metadata are using the same prefix, the Designer will only use one of them, and disable the others.
A disabled User Defined Functions Metadata looks like this:
You have several possibilities when this happens.
The first one is to open the Metadata and change its prefix (and also optionally its name)
The Metadata will then be enabled automatically at save and you will be able to use its functions.
The other solution is to not touch the Metadata, and simply switch between the enabled / disabled Metadata as you would usually do for [DUPPLICATE] resources for instance.
For this right click on the [DISABLED] Metadata, and use one of the following actions:
Enable Resource will enable the selected Metadata and disable the previously enabled one.
Reveal Enabled Resource will focus on the currently enabled Metadata in the Project Explorer.
User Defined Functions in a Mapping
Our functions being defined and ready, we can now start using them!
The user defined functions can be used in Mapping Expressions as you would usually do for the standard SQL functions for instance.
With the exception that it must be prefixed by the prefix you specified in the Metadata.
The syntax is the following:
<prefix>::<function>
Example:
demo::function01('myfriststring','mysecondstring')
Using the User Defined Functions List Window
The little library button in the Expression Editor opens a popup showing the list of available user defined functions, grouped by library.
A double click on a function will add it in the Expression Editor:
In this popup, the functions on the left are regrouped under library nodes, which corresponds to the 'Name' specified on the root node of the User Defined Functions Metadata.
Used Defined Functions are supported only in Mappings
Using completion
In the Expression Editor, the completion can be used to list the available functions while typing.
For this press the following keys to active the completion: CTRL + space
Used Defined Functions are supported only in Mappings