
Component family |
Databases/Mysql |
|
Function |
tMysqlSP calls the database |
|
Purpose |
tMysqlSP offers a convenient way |
|
Basic settings |
Property type |
Either Built-in or Since version 5.6, both the Built-In mode and the Repository mode are |
|
|
Built-in: No property data stored |
|
|
Repository: Select the repository |
|
Host |
Database server IP address |
|
Port |
Listening port number of DB server. |
|
Database |
Name of the database |
|
Username and |
DB user authentication data. To enter the password, click the […] button next to the |
|
Schema and Edit |
A schema is a row description. It defines the number of fields to be processed and passed on Since version 5.6, both the Built-In mode and the Repository mode are |
|
|
Built-In: You create and store the schema locally for this |
|
|
Repository: You have already created the schema and |
Click Edit schema to make changes to the schema. If the
|
||
|
SP Name |
Type in the exact name of the Stored Procedure |
|
Is Function / Return result |
Select this check box, if a value only is to be returned. Select on the list the schema column, the value to be returned is |
|
Parameters |
Click the Plus button and select the various Schema Columns that will be required by the Select the Type of IN: Input parameter.
OUT: Output parameter/return
IN OUT: Input parameters is to be
RECORDSET: Input parameters is to NoteCheck Scenario 1: Inserting data in mother/daughter tables if you want to analyze a set of records from a |
Dynamic settings |
Click the [+] button to add a row in the table and fill The Dynamic settings table is available only when the For more information on Dynamic settings and context |
|
Usage |
This component is used as intermediary component. It can be used |
|
Log4j |
The activity of this component can be logged using the log4j feature. For more information on this feature, see Talend Studio User For more information on the log4j logging levels, see the Apache documentation at http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/Level.html. |
|
Limitation |
The Stored Procedures syntax should match the Database syntax. |
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
link. -
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
configuration. -
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` $$<span></span>CREATE DEFINER=`root`@`localhost` PROCEDURE `getstate`(IN pid INT, OUTpstate VARCHAR(50))<span></span>BEGIN<span></span>SELECT LabelState INTO pstate FROM us_states WHERE idState = pid;<span></span>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
procedure. -
Add a second line and set the Column field
to State and the Type to
Out as this is the output parameter to be
returned. -
Eventually, set the tLogRow component
properties.

-
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
procedure.
Note
Check Scenario 1: 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.
For related scenarios, see: