Scenario: Using tMysqlSP to find a State Label using a stored
The following job aims at finding the State labels matching the odd State IDs in a
Mysql two-column table. A stored procedure is used to carry out this operation.
Drag and drop the following components used in this example: tRowGenerator, tMysqlSP, tLogRow.
Connect the components using the Row Main
The tRowGenerator is used to generate the
odd id number. Double-click on the component to launch the editor.
Click on the Plus button to add a column to
the schema to generate.
Select the Key check box and define the
Type to Int.
The Length equals to 2 digits max.
Use the preset function called sequence but
customize the Parameters in the lower part of the window.
Change the Value of step from 1 to 2 for
this example, still starting from 1.
Set the Number of generated rows to 25 in
order for all the odd State id (of 50 states) to be generated.
Click OK to validate the
Then select the tMysqlSP component and
define its properties.
Set the Property type field to Repository and select the relevant entry on the list.
The connection details get filled in automatically.
Else, set manually the connection information.
Click Sync Column to retrieve the generated
schema from the preceding component.
Then click Edit Schema and add an extra
column to hold the State Label to be output, in addition to the ID.
Type in the name of the procedure in the SP
Name field as it is called in the Database. In this example,
getstate. The procedure to be executed states as
follows:1234567DROP PROCEDUREIF EXISTS `talend`.`getstate` $$CREATE DEFINER=`root`@`localhost` PROCEDURE `getstate`(IN pid INT, OUTpstate VARCHAR(50))BEGINSELECT LabelState INTO pstate FROM us_states WHERE idState = pid;END $$
In the Parameters area, click the plus
button to add a line to the table.
Set the Column field to
ID, and the Type field
to IN as it will be given as input parameter to the
Add a second line and set the Column field
to State and the Type to
Out as this is the output parameter to be
Eventually, set the tLogRow component
Synchronize the schema with the preceding component.
And select the Print values in cells of a
table check box for reading convenience.
Then save your Job and execute it.
The output shows the state labels corresponding to the odd state ids as defined in the
Check Scenario: Inserting data in mother/daughter tables if you want to analyze a set of records from a
database table or DB query and return single records.