tMysqlLastInsertId - 6.1

Talend Components Reference Guide

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

tMysqlLastInsertId properties

Component family

Databases

 

Function

tMysqlLastInsertId fetches the last inserted ID from a selected MySQL Connection.

Purpose

tMysqlLastInsertId obtains the primary key value of the record that was last inserted in a Mysql table by a user.

Basic settings

Schema and Edit Schema

A schema is a row description. It defines the number of fields (columns) to be processed and passed on to the next component. The schema is either Built-In or stored remotely in the Repository.

Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions.

 

 

Built-In: You create and store the schema locally for this component only. Related topic: see Talend Studio User Guide.

 

 

Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs. Related topic: see Talend Studio User Guide.

  

Click Edit schema to make changes to the schema. If the current schema is of the Repository type, three options are available:

  • View schema: choose this option to view the schema only.

  • Change to built-in property: choose this option to change the schema to Built-in for local changes.

  • Update repository connection: choose this option to change the schema stored in the repository and decide whether to propagate the changes to all the Jobs upon completion. If you just want to propagate the changes to the current Job, you can select No upon completion and choose this schema metadata again in the [Repository Content] window.

 

Component list

Select the relevant tMysqlConnection component in the list if more than one connection is planned for the current job.

Advanced settings

tStatCatcher Statistics

Select this check box to collect log data at the component level.

Dynamic settings

Click the [+] button to add a row in the table and fill the Code field with a context variable to choose your database connection dynamically from multiple connections planned in your Job. This feature is useful when you need to access database tables having the same data structure but in different databases, especially when you are working in an environment where you cannot change your Job settings, for example, when your Job has to be deployed and executed independent of Talend Studio.

Once a dynamic parameter is defined, the Component List box in the Basic settings view becomes unusable.

For examples on using dynamic parameters, see Scenario 3: Reading data from MySQL databases through context-based dynamic connections and Scenario: Reading data from different MySQL databases using dynamically loaded connection parameters. For more information on Dynamic settings and context variables, see Talend Studio User Guide.

Global Variables 

NB_LINE: the number of rows processed. This is an After variable and it returns an integer.

ERROR_MESSAGE: the error message generated by the component when an error occurs. This is an After variable and it returns a string. This variable functions only if the Die on error check box is cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl + Space to access the variable list and choose the variable to use from it.

For further information about variables, see Talend Studio User Guide.

Usage

This component is to be used as an intermediary component.

Warning

If you use this component with tMySqlOutput, verify that the Extend Insert check box in the Advanced Settings tab is not selected. Extend Insert allows you to make a batch insertion, however, if the check box is selected, only the ID of the last line in the last batch will be returned.

Log4j

If you are using a subscription-based version of the Studio, 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: Get the ID for the last inserted record

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.

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

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

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

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.

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

  • In the design workspace, select tMySqlLastInsertId.

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

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

Note

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.

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