Creating a stored procedure - 6.3

Talend MDM Platform Studio User Guide

EnrichVersion
6.3
EnrichProdName
Talend MDM Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

Prerequisite(s):

  • 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.

To create a stored procedure, do the following:

  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.

  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.