July 30, 2023

tMDMSP – Docs for ESB 7.x


Offers a convenient way to centralize multiple or complex queries in an MDM Hub
and calls the stored procedure easily.

tMDMSP Standard properties

These properties are used to configure tMDMSP running in the Standard Job framework.

The Standard
tMDMSP component belongs to the Talend MDM family.

The component in this framework is available in all Talend


Schema and
Edit Schema

In SP principle, the schema is an input

A schema is a row description, it defines
the number of fields to be processed and passed on
to the next component.

The schema of this component is

Use an existing
Select this check box if you
want to use a configured tMDMConnection component.


By default, Server
is selected. Although it is recommended to migrate existing Jobs for
this new version, the Server 5.6 option is available
to ease the process of the migration of your Jobs so as to keep them working without
modification with a 6.0 server. To do so, an option on the server must be enabled to
accept and translate requests from such Jobs.


Type in the URL of the MDM server.

Username and

Type in the user authentication data for the
MDM server.

To enter the password, click the […] button next to the
password field, and then in the pop-up dialog box enter the password between double quotes
and click OK to save the settings.

Data Container

Type in the name of the data container that
stores the procedure you want to call.


Select Master or Staging to specify the database on
which the action should be performed.

Procedure Name

Type in the exact name of the Stored

Parameters (in

Click the Plus button and select the various
Input Columns
that will be required by the procedures.


The SP schema can hold more columns than
there are parameters used in the procedure.



Select this check box to gather the
processing metadata at the Job level as well as at
each component level.



ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
to access the variable list and choose the variable to use from it.

For further information about variables, see
Talend Studio

User Guide.


Usage rule

This component is used as intermediary component. It can be
used as start component but only no input parameters are thus needed for the
procedure to be called. An output link is required.

You can increase the timeout values for a Job using this component
to help process a large number of data records. For more information, see advanced
execution settings for JVM parameters in the article Timeout values for a Job using
MDM components
on Talend Help Center (https://help.talend.com).


Outgoing links (from this component to

Row: Main

Trigger: Run if;
On Component Ok; On Component Error, On Subjob Ok,
On Subjob Error.

Incoming links (from one component to this

Row: Main,

Trigger: Run if,
On Component Ok, On Component Error, On Subjob Ok,
On Subjob Error

For further information regarding
connections, see
Talend Studio User Guide

Executing a stored procedure using tMDMSP

This scenario applies only to Talend MDM Platform and Talend Data Fabric.

In this scenario, the Job first generates parameters and sends them to tMDMSP, which executes a predefined stored procedure,
and then extracts data from the returned execution result and presents the extracted
data in the console.


  • Make sure the MDM server is up and running.

  • You have imported the MDM demo project and loaded the sample data into the data container
    Product by running the Job MDM_LoadAll.

  • You have been assigned a role with the appropriate user authorization and access rights to
    the data model Product.

  • You have created a store procedure called ProductSelection and
    deployed this stored procedure to the MDM server.

In this example, the stored procedure ProductSelection is designed to query two fields
Name and Price of
Product data records within a price range:


For more information on working with stored procedures, see

Talend Studio
User Guide

Creating a Job to execute the stored procedure

  1. Drag and drop the following components onto the design workspace:
    tFixedFlowInput, tMDMSP, tExtractXMLField, and tLogRow.
  2. Link the components using the Row >

Configuring tFixedFlowInput to generate a price range

  1. Double-click tFixedFlowInput to open
    its Basic settings view.


  2. Click the […] button next to
    Edit schema to open the
    schema editor.
  3. Click the [+] button to add two
    parameters min and max
    that are used to define the price range.


  4. Click OK to close the schema
  5. In the Mode area, keep the default
    option Use Single Table, and enter
    10 between quotes for the
    min parameter and
    15 between quotes for the
    max parameter.

Configuring tMDMSP to execute the stored procedure

  1. Double-click tMDMSP to open its
    Basic settings view.


  2. Enter the user name and password for accessing the MDM server.
  3. In the Data Container field, enter
    Product between quotes.
  4. In the Procedure Name field, enter
    ProductSelection between quotes.
  5. In the Parameters table, click the
    [+] button to add two rows,
    and select min and max

Configuring tExtractXMLField to extract data from the returned execution
result of the stored procedure

  1. Double-click tExtractXMLField to open
    its Basic settings view.


  2. Click the […] button next to
    Edit schema to open the
    schema editor.
  3. Add two columns to define the structure of the output data:
    name and price.


  4. Click OK to close the schema editor,
    and then click Yes in the Propagate dialog box.
  5. In the Loop XPath query field, enter
    result between quotes on which the loop
    is based.

    Each result of the stored procedure has the following syntax:
    (where colN is the nth column in the
    SELECT clause).

    If a null value exists in a data record to be returned by the
    execution result of the stored procedure, some
    inconsistencies will occur between the column number and the
    value for this column in the returned data record.

  6. In XPath query column of the
    Mapping table, enter the
    exact node name on which the loop is applied:
    col0 and col1,
    respectively, between quotes.

Configuring the data display mode and executing the Job

  1. Double-click the tLogRow component to
    display its Basic settings
  2. In the Mode area, select Table (print values in cells of a
    for better readability of the


  3. Save the Job and press F6 to run

    According to the stored procedure and the price range, the
    Product data records within the price
    range of 10 to 15 are displayed with the values of the
    Name and Price
    fields as expected.

Document get from Talend https://help.talend.com
Thank you for watching.
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x