Creating a stored procedure - Cloud - 8.0

Talend Studio User Guide

Version
Cloud
8.0
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Cloud
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Design and Development
Last publication date
2024-02-29
Available in...

Data Fabric

MDM Platform

Before you begin

  • Make sure the MDM server is up and running.

  • You have already connected to the MDM server from Talend Studio.

  • You have the appropriate user authorization to create a stored procedure.

About this task

To create a stored procedure, do the following:

Procedure

  1. In the MDM Repository tree view, right-click Stored Procedure and select New from the contextual menu.
    The New Stored Procedure dialog box is displayed.
  2. Enter a name for the new stored procedure and then click OK.
    The newly created stored procedure is opened in the workspace.
    Stored Procedure "PriceGreaterThan10 0.1" opened in the workspace.
  3. In the Description field, enter a description for the new stored procedure.
  4. In the Procedure area, enter the syntax to address the data records contained in a specific entity.
    Warning: The stored procedure must have a syntax that can be natively understood by the underlying storage of the data stored in the MDM Hub. When an RDBMS is used to store MDM data, the query must use SQL and each result of the stored procedure has the following syntax: <result><col0>val1</col1>...<colN>valn</colN></result> (where colN is the nth column in the SELECT clause).
  5. Select the Refresh the cache after execution check box if you want to refresh the cache after the execution of the stored procedure.
    This is necessary as a stored procedure may perform an insert/update/replace action that is executed directly at the database level. Because of the cache, the change may not be reflected.
  6. Press Ctrl + S to save the procedure.
  7. Test the stored procedure if needed. To do that, in the Execute Procedure section of the stored procedure editor, select the data container holding the data records on which you want to execute the query, and then click Execute Procedure to run the procedure.
    In this example, it returns the Price and Description for records with a Price greater than 10.
    Warning: 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.