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
Used 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 functions 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

 

About the prefix

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 the 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 the Product(s)

In the Product(s) box, you'll specify the 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 the involved technologies.

For instance, if we are using the function on an Hypersonic SQL (HSQL) table in a Mapping, the implementation for Hypersonic SQL (HSQL) will be used.

You can click on the little 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 the 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.

Warning: 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 the Expression

The 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 the 'param01' of our example:

$param01

 

And our final expression, that uses the 'concat' function available in Hypersonic SQL and Oracle:

 

metadataExpression

 

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

Metadata with the same prefix

As previously said, 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!