Welcome Guest! Log in


 

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.

metadatacreation

 

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.

 

metadataPrefixDefinition

 

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:

metadataFunctionNameAndDescription

 

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:

metadataParameter

 

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:

metadataImplementation

 

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.

metadataImplementationProduct

 

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.

 

metadataImplementationMssql

 

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:

 

metadataExpression

 

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.

implementation using another udf of same library

 

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:

 

implementation using another udf of another library configuration

It can then be used in expression:

 

implementation using another udf of another library 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.

  1. Specify the function to create, with its parameters
  2. Then click on the '+' button to add it to the defined functions
  3. Repeat point 1 and 2 for each function you want to create
  4. All the defined functions will be created when clicking on Finish

 

metadataWizard

 

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:

disable overview

 

You have several possibilities when this happens.

The first one is to open the Metadata and change its prefix (and also optionally its name)

 

disable changePrefix

 

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:

 

disable contextMenu

 

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:

mappingPopupUDF

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

mappingExpressionEditorCompletion

Used Defined Functions are supported only in Mappings

 

 

You have no rights to post comments

Articles

Suggest a new Article!