August 15, 2023

Scenario: Get the ID for the last inserted record with tMysqlLastInsertId – Docs for ESB 6.x

Scenario: Get the ID for the last inserted record with
tMysqlLastInsertId

The following Java scenario creates a job that opens a connection to Mysql database,
writes the defined data into the database, and finally fetches the last inserted ID on
the existing connection.

  • Drop the following components from the Palette onto the design workspace: tMySqlConnection, tMySqlCommit,
    tFileInputDelimited, tMySqlOutput, tMysqlLastInsertId, and tLogRow.

  • Connect tMySqlConnection to tFileInputDelimited using an OnSubjobOk link.

  • Connect tFileInputDelimited to tMySqlCommit using an OnSubjobOk link.

  • Connect tFileInputdelimited to the three
    other components using Row Main links.

Use_Case_tMysqlLastInsertId.png
  • In the design workspace, select tMysqlConnection.

  • Click the Component tab to define the basic
    settings for tMysqlConnection.

  • In the Basic settings view, set the
    connection details manually or select them from
    the context variable list, through a Ctrl+Space click in the corresponding field if you stored
    them locally as Metadata DB connection entries. For more information about
    Metadata, see
    Talend Studio

    User Guide
    .

Use_Case_tMysqlLastInsertId1.png
  • In the design workspace, select tMysqlCommit
    and click the Component tab to define its basic
    settings.

  • On the Component List, select the relevant
    tMysqlConnection if more than one
    connection is used.

  • In the design workspace, select tFileInputDelimited.

  • Click the Component tab to define the basic
    settings of tFileInputDelimited.

Use_Case_tMysqlLastInsertId2.png
  • Fill in a path to the processed file in the File
    Name
    field. The file used in this example is
    Customers.

  • Define the Row separator that allow to
    identify the end of a row. Then define the Field
    separator
    used to delimit fields in a row.

  • Set the header, the footer and the number of processed rows as necessary. In
    this scenario, we have one header.

  • Click
    the three-dot button next to Edit Schema to
    define the data to pass on to the next component.

Related topics:
Talend Studio

User Guide.

Use_Case_tMysqlLastInsertId3.png

In this scenario, the schema consists of two columns, name and
age. The first holds three employees’ names and the second
holds the corresponding age for each.

  • In the design workspace, select tMySqlOutput.

  • Click the Component tab to define the basic
    settings of tMySqlOuptput.

Use_Case_tMysqlLastInsertId4.png
  • Select the Use an existing connection check
    box.

  • In the Table field, enter the name of the
    table where to write the employees’ list, in this example:
    employee.

  • Select relevant actions on the Action on table
    and Action on data lists. In this
    example, no action is carried out on table, and the action carried out on data
    is Insert.

  • Click
    Sync columns to synchronize columns with
    the previous component. In this example, the schema to be inserted into the
    MySql database table consists of the two columns name
    and age.

Use_Case_tMysqlLastInsertId5.png
  • In the design workspace, select tMySqlLastInsertId.

  • Click the Component tab to define the basic
    settings of tMySqlLastInserId.

Use_Case_tMysqlLastInsertId6.png
  • On the Component List, select the relevant
    tMysqlConnection, if more than one
    connection is used.

  • Click
    Sync columns to synchronize columns with
    the previous component. In the output schema of tMySqlLastInsertId, you can see the read-only column
    last_insert_id
    that will fetch the last inserted ID on the
    existing connection.

Use_Case_tMysqlLastInsertId7.png

You can select the data type Long from the
Type drop-down list in case of a huge number of
entries.

  • In the design workspace, select tLogRow and
    click the Component tab to define its basic
    settings. For more information, see tLogRow.

  • Save your job and press F6 to execute
    it.

Use_Case_tMysqlLastInsertId8.png

tMysqlLastInsertId fetched the last inserted ID for
each line on the existing connection.


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