July 30, 2023

tMysqlConnection – Docs for ESB 7.x

tMysqlConnection

Opens a connection to the specified MySQL database for reuse in the subsequent
subJob or subJobs.

tMysqlConnection Standard properties

These properties are used to configure tMysqlConnection running in the Standard Job framework.

The Standard
tMysqlConnection component belongs to the Databases and the ELT families.

The component in this framework is available in all Talend
products
.

Note: This component is a specific version of a dynamic database
connector. The properties related to database settings vary depending on your database
type selection. For more information about dynamic database connectors, see Dynamic database components.

Basic settings

Database

Select a type of database from the list and click
Apply.

Property type

Either Built-in or
Repository.

 

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
database connection or fetch a database connection shared by a parent or child Job,
and in the Shared DB Connection Name field displayed, enter the
name for the shared database connection. This allows you to share one single database
connection (except the database schema setting) among several database connection
components from different Job levels that can be either parent or child.

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.

This check box is not available when the Specify a data source
alias
check box is selected.

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

Usage rule

This component is more commonly used with other tMysql*
components, especially with the tMysqlCommit and tMysqlRollback components.

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:

  1. In a command line editor, connect to your Mysql server.

  2. Once connected to the relevant database, type in the following command to
    create the parent table:

  3. Then create the second table:

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

Linking the components

  1. Drag and drop the following components from the Palette: a tFileList, a
    tFileInputDelimited, a tMap, a tMysqlConnection, a tMysqlCommit and two tMysqlOutput.
  2. Connect tMysqlConnection to tFileList using an OnComponentOk link.
  3. Connect tFileList to tMysqlCommit using an OnComponentOk link.
  4. 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.
  5. 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.

    tMysqlConnection_1.png

Configuring the components

  1. Set the tFileList component properties,
    such as the directory name where files will be fetched from.
  2. 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

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

    tMysqlConnection_2.png

  4. Set the rest of the fields as usual, defining the row and field
    separators according to your file structure.
  5. 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.
  6. 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.
    tMysqlConnection_3.png

  7. 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.
  8. In the Basic settings
    tab of the two tMysqlOutput components,
    select the Use an existing connection
    check box to retrieve the tMysqlConnection details.

    tMysqlConnection_4.png

  9. 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.
  10. Switch to the Advanced
    settings
    tab of the DB output component corresponding to the
    child table (that is, f1090_baby).

    • In the id-baby row of the Additional
      columns
      table, fill the SQL
      expression
      field with "(Select
      Last_Insert_id())"
      .
    • Set the Position field to
      Before.
    • Set the Reference column field to
      years.

    These settings allow the f1090_baby table to use the ID key of the
    f1090_mum as its key.

  11. Clear the Extend insert
    check box of both database output components.

Executing the Job

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

    tMysqlConnection_5.png

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

Rollback from inserting data in
mother/daughter tables

Based on Inserting data in mother/daughter tables, insert a rollback function in order to prevent
unwanted commit.

tMysqlConnection_6.png

  1. Drag and drop tMysqlRollback to the design
    workspace. tMysqlRollback ensures that the transaction will not
    be partly committed.
  2. Connect tFileList to tMysqlRollback with an OnSubjobError trigger.
  3. Double-click tMysqlRollback to enter its
    Basic settings view.
  4. From the Component List, select the
    connection component.

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

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

  2. Connect the tRowGenerator component to
    the tMysqlOutput component using a
    Row > Main connection.
  3. Connect the tMysqlConnection component to
    the tRowGenerator using a Trigger > OnSubjobOk connection.

    tMysqlConnection_7.png

Configuring the database connection

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

    tMysqlConnection_8.png

  2. 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
    .
  3. 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

  1. Double-click the tRowGenerator component
    to open the Row Generator editor.

    tMysqlConnection_9.png

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

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

  4. In the Number of Rows for RowGenerator
    field, specify the number of data rows you want to generate, 10 in this example.
  5. Click the Preview button on the Preview tab to validate the generator
    settings.
  6. 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

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

    tMysqlConnection_10.png

  2. 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.
  3. In the Table field, enter the name of the
    database table you are going to write data to, customers in this example.
  4. From the Action on table list, select the
    Drop table if exists and create option
    to ensure a clean table is created.
  5. From the Action on data list, select
    Insert.
  6. 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.
  7. Press Ctrl+S to save the Job.

Setting up the parent Job

Arranging data flow for the parent Job

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

  2. Connect the tRunJob component to the
    tMysqlConnection compnent using a
    Trigger > OnSubjobOk connection.
  3. Connect the tMysqlConnection component to
    the tMysqlInput component using a Trigger > OnSubjobOk connection.
  4. Connect the tMysqlInput component to the
    tLogRow component using a Row > Main
    connection.
  5. Connect the tMysqlInput component to the
    tMysqlCommit component using a
    Trigger > OnSubjobOk connection.

    tMysqlConnection_11.png

Configuring the components

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

    If a child Job 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.
    tMysqlConnection_12.png

  2. 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.
  3. 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.
    tMysqlConnection_13.png

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

    tMysqlConnection_14.png

  5. 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.
  6. Click the […] button next to Edit schema to open the Schema editor, and define the same data structure as in
    the child Job.

    tMysqlConnection_15.png

    When done, click OK to close the dialog
    and click Yes when prompted to propagate
    the schema to the next component.
  7. Specify the table name in the Table Name
    list, and click Guess Query to get the
    query statement automatially filled in the Query field.
  8. 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

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

    tMysqlConnection_16.png

    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.
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x