tMysqlConnection

tMysqlConnection Properties

Component family	Databases/MySQL
Function	Opens a connection to the database for a current transaction.
Purpose	This component is used to open a connection to the specified database that can then be reused in the subsequent subjob or subjobs.
Basic settings	Property type	Either Built-in or Repository. Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions.
		Built-in: No property data stored centrally.
		Repository: Select the repository file in which the properties are stored. The fields that follow are completed automatically using the data retrieved.
	Host	Database server IP address.
	Port	Listening port number of DB server.
	Database	Name of the database.
	Additional JDBC parameters	Specify additional connection properties for the DB connection you are creating.
	Username and Password	DB user authentication data. To enter the password, click the […] button next to the password field, and then in the pop-up dialog box enter the password between double quotes and click OK to save the settings.
	Use or register a shared DB Connection	Select this check box to share your connection or fetch a connection shared by a parent or child Job. This allows you to share one single DB connection among several DB connection components from different Job levels that can be either parent or child. Warning This option is incompatible with the Use dynamic job and Use an independent process to run subjob options of the tRunJob component. Using a shared connection together with a tRunJob component with either of these two options enabled will cause your Job to fail. Shared DB Connection Name: set or type in the shared connection name.
	Specify a data source alias	Select this check box and specify the alias of a data source created on the Talend Runtime side to use the shared connection pool defined in the data source configuration. This option works only when you deploy and run your Job in Talend Runtime.
Advanced settings	Auto Commit	Select this check box to commit any changes to the database automatically upon the transaction. With this check box selected, you cannot use the corresponding commit component to commit changes to the database; likewise, when using the corresponding commit component, this check box has to be cleared. By default, the auto commit function is disabled and changes must be committed explicitly using the corresponding commit component. Note that the auto commit function commits each SQL statement as a single transaction immediately after the statement is executed while the commit component does not commit only until all of the statements are executed. For this reason, if you need more room to manage your transactions in a Job, it is recommended to use the commit component.
	tStatCatcher Statistics	Select this check box to gather the job processing metadata at a Job level as well as at each component level.
Usage	This component is more commonly used with other tMysql* components, especially with the tMysqlCommit and tMysqlRollback components.
Log4j	The activity of this component can be logged using the log4j feature. For more information on this feature, see Talend Studio User Guide. 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	n/a

Scenario 1: Inserting data in mother/daughter tables

The following Job is dedicated to advanced database users, who want to carry out
multiple table insertions using a parent table id to feed a child table.

As a prerequisite to this Job, follow the steps described below to create the relevant
tables using an engine such as innodb:

In a command line editor, connect to your Mysql server.
Once connected to the relevant database, type in the following command to
create the parent table:

create table f1090_mum(id int not null auto_increment, name varchar(10), primary key(id)) engine=innodb;

1

create table f1090_mum(id int not null auto_increment, name varchar(10), primary key(id)) engine=innodb;
Then create the second table:

create table f1090_baby (id_baby int not null, years int) engine=innodb;

1

create table f1090_baby (id_baby int not null, years int) engine=innodb;

Back in Talend Studio, the Job requires seven components including
tMysqlConnection and tMysqlCommit.

Linking the components

Drag and drop the following components from the Palette: a tFileList, a
tFileInputDelimited, a tMap, a tMysqlConnection, a tMysqlCommit and two tMysqlOutput.
Connect tMysqlConnection to tFileList using an OnComponentOk link.
Connect tFileList to tMysqlCommit using an OnComponentOk link.
Connect the tFileList component to the
input file component using an Iterate link
as the name of the file to be processed will be dynamically filled in from
the tFileList directory using a global
variable.
Connect the tFileInputDelimited component
to the tMap and dispatch the flow between
the two output Mysql DB components. Use a Row link for each for these connections representing the
main data flow.

Configuring the components

Set the tFileList component properties,
such as the directory name where files will be fetched from.
In the tMysqlConnection Component view,
set the connection details manually or fetch them from the
Repository if you centrally stored them as a Metadata DB connection
entry. For more information about Metadata, see Talend Studio User Guide.
On the tFileInputDelimited component’s
Basic settings panel, press Ctrl+Space bar to access the variable list. Set
the File Name field to the global variable:
tFileList_1.CURRENT_FILEPATH
Set the rest of the fields as usual, defining the row and field
separators according to your file structure.
Then set the schema manually through the Edit
schema feature or select the schema from the Repository. In
Java version, make sure the data type is correctly set, in accordance with
the nature of the data processed.
In the tMap Output area, add two output
tables, one called mum for the parent table, the second called baby, for the
child table.

Drag the Name column from the Input area, and drop it to the mum table.

Drag the Years column from the Input area and drop it to the baby table.
Make sure the mum table is on the top of the baby table as the order is
determining for the flow sequence hence the DB insert to perform
correctly.

Connect the output row link to distribute correctly the flow to the
relevant DB output component.
In each of the tMysqlOutput components’
Basic settings panel, select the
Use an existing connection check box to
retrieve the tMysqlConnection details.
Set the Table name making sure it
corresponds to the correct table, in this example either
f1090_mum or
f1090_baby.

There is no action on the table as they are already created.

Select Insert as Action on data for both output components.

Click on Sync columns to retrieve the schema set in the tMap.
In the Additional columns area of the DB
output component corresponding to the child table
(f1090_baby), set the id_baby column so that it
reuses the id from the parent table.
In the SQL expression field type in:
"(Select Last_Insert_id())"

The position is Before and the Reference column is years.

In the Advanced settings panel, clear
the Extend insert check box.

Executing the Job

Press Ctrl + S to save your Job.
Press F6 to execute it.

The parent table id has been reused to feed the
id_baby column.

Scenario 2: Sharing a database connection between a parent Job and child
Job

This scenario shows how a database connection is shared between a parent Job and a
child Job. The parent Job first calls the child Job to write some randomly generated
data to a MySQL database, and then reads the data from the MySQL database and displays
it on the console. A connection to the MySQL database is set up only once and used in
both Jobs.

Setting up the child Job

Adding and linking components

Add the following components by typing the component names on the design
workspace or dropping them from the Palette:
- a tMysqlConnection component, to
  open a connection to the MySQL database,
- a tRowGenerator, to generate
  random input data,
- a tMysqlOutput component, to
  write data to the MySQL database.
Connect the tRowGenerator component to
the tMysqlOutput component using a
Row > Main connection.
Connect the tMysqlConnection component to
the tRowGenerator using a Trigger > OnSubjobOk connection.

Configuring the database connection

Double-click the tMysqlConnection
component to open its Basic settings
view.
With the Property Type set to Built-In, specify the connection details in the
relevant fields, including:
- the host name or IP address of your database server
- the listening port number
- the database name
- the user name and password for your database
  authentication.
If you have stored your connection details under the Metadata node in the Repository tree view, you can simply drop your centralized
metadata item onto the tMysqlConnection
component. For information on how to centralize a database connection, see
the chapter on managing metadata of the Talend Studio
User Guide.
Select the Use or register a shared DB
Connection check box so that the database connection open by
this component can be shared across different Jobs. Then, enter a name for
the shared connection between double quotes in the Shared DB Connection Name field, shared_mysql_connection in this example.

Configuring the input data

Double-click the tRowGenerator component
to open the Row Generator editor.
Click the [+] button to add four columns
and set their properties:
- id, type Integer, 2 characters
  long.
- firstName, type String, 15
  characters long
- lastName, type String, 15
  characters long
- city, type String, 15
  characters long
Define the function for each column:
- For the id column, select
  Numeric.sequence from the
  Function list to generate
  sequence numbers.
- For the firstName column,
  select TalendDataGenerator.getFirstName from the Function list to generate random first
  names.
- For the lastName column, select
  TalendDataGenerator.getLastName
  from the Function list to generate
  random family names.
- For the city column, select
  TalendDataGenerator.getUsCity
  from the Function list to generate
  random city names.
In the Number of Rows for RowGenerator
field, specify the number of data rows you want to generate, 10 in this example.
Click the Preview button on the Preview tab to validate the generator
settings.
When done, click OK to close the editor
and click Yes when prompted to propagate
the schema to the next component.

Configuring the database output

Double-click the tMysqlOutput component
to open its Basic settings view.
Select the Use an existing connection
check box and, in case you have more than one connection component in the
Job, select the connection component to be used from the Component List drop-down list.
In the Table field, enter the name of the
database table you are going to write data to, customers in this example.
From the Action on table list, select the
Drop table if exists and create option
to ensure a clean table is created.
From the Action on data list, select
Insert.
Click the […] button next to Edit schema to check the output schema. If
needed, click Sync columns to retrieve the
schema from the preceding component.
Press Ctrl+S to save the Job.

Setting up the parent Job

Adding and linking components

Add the following components by typing the component names on the design
workspace or dropping them from the Palette:
- a tRunJob component, to call the
  child Job,
- a tMysqlConnection component, to
  open the MySQL database connection,
- a tMysqlInput component, to read
  the data written to the MySQL database by the child Job.
- a tLogRow component to display
  the data on the console,
- a tMysqlCommit component to
  commit data upon transation and close the database
  connection.
Connect the tRunJob component to the
tMysqlConnection compnent using a
Trigger > OnSubjobOk connection.
Connect the tMysqlConnection component to
the tMysqlInput component using a Trigger > OnSubjobOk connection.
Connect the tMysqlInput component to the
tLogRow component using a Row > Main
connection.
Connect the tMysqlInput component to the
tMysqlCommit component using a
Trigger > OnSubjobOk connection.

Configuring the components

Double-click the tRunJob component to
open its Basic settings view.

If a child Jod has been already specified in the component, you can open
its Basic settings view by right-clicking
it and selecting Settings from the
contextual menu, or by clicking the component and then selecting the
Component tab.
Click the […] button next to the
Job field and select the child Job from
the [Repository Content] dialog box. Leave
all the other parameters as default.
In the Basic settings view of the
tMysqlConnection component, select the
Use or register a shared DB Connection
check box. Then, in the Shared DB Connection
Name field, enter the name of the shared connection defined
in the child Job, shared_mysql_connection
in this example.

Leave all the other parameters blank or as default.
Double-click the tMysqlInput component to
open its Basic settings view.
Select the Use an existing connection
check box and, in case you have more than one connection component in the
Job, select the connection component to be used from the Component List drop-down list.
Click the […] button next to Edit schema to open the [Schema] editor, and define the same data structure as in
the child Job.

When done, click OK to close the dialog
and click Yes when prompted to propagate
the schema to the next component.
Specify the table name in the Table Name
list, and click Guess Query to get the
query statement automatially filled in the Query field.
In the Basic settings view of the
tLogRow component, select the Table mode to display the execution result in
table cells.

Leave the settings of the tMysqlCommit
component unchanged.

Execute the Job

Press Ctrl+S to save the Job.
Press F6 or click the Run button on the Run console to execute the Job.

The Job first calls the child Job to write data to the database and then
reads the data from the database and displays it on the console.

Document get from Talend https://help.talend.com

Thank you for watching.

Docs 5.x

0 Comments

Inline Feedbacks

View all comments

tMysqlConnection – Docs for ESB 5.x