Executing a stored procedure using tMDMSP - 6.3

Talend Components Reference Guide

EnrichVersion
6.3
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

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.

Prerequisites:

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

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

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

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: <result><col0>val1</col1>...<colN>valn</colN></result> (where colN is the nth column in the SELECT clause).

    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.

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

  2. In the Mode area, select Table (print values in cells of a table) for better readability of the result.

  3. Save the Job and press F6 to run it.

    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.