Creating a stored procedure - 6.2

Talend Data Fabric Studio User Guide

EnrichVersion
6.2
EnrichProdName
Talend Data Fabric
task
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

Prerequisite(s): 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 displays.

  2. Enter a name for the new stored procedure and then click OK to close the dialog box.

  3. In the MDM Repository tree view, expand the Stored Procedure node and click the new stored procedure you created to open it in the workspace.

  4. In the Description field, enter a description for the stored procedure you want to create.

  5. 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 XML database is used, a XQuery query must be provided. 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).

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

  7. Click the save icon on the toolbar or press Ctrl + S on your keyboard to save the procedure.

  8. 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 you created.

    In this example, it returns the Price and Description for records with a Price of greater than 10.