Updating data using tMysqlOutput

This Java scenario describes a two-component Job that updates data in a MySQL table according to that in a delimited file.

For more technologies supported by Talend, see Talend components.

• Drop tFileInputDelimited and tMysqlOutput (displayed as tDBOutput_1) from the Palette onto the design workspace.

• Connect the two components together using a Row Main link.

• Double-click tFileInputDelimited to display its Basic settings view and define the component properties.

• From the Property Type list, select Repository if you have already stored the metadata of the delimited file in the Metadata node in the Repository tree view. Otherwise, select Built-In to define manually the metadata of the delimited file.

  For more information about storing metadata, see Talend Studio User Guide.

• Click the [...] button next to the File name/Stream field and browse to the source delimited file that contains the modifications to propagate in the MySQL table.

  In this example, we use the customer_update file that holds four columns: id, CustomerName, CustomerAddress and idState. Some of the data in these four columns is different from that in the MySQL table.

• Define the row and field separators used in the source file in the corresponding fields.

• If needed, set Header, Footer and Limit.

  In this example, Header is set to 1 since the first row holds the names of columns, therefore it should be ignored. Also, the number of processed lines is limited to 2000.

• Click the [...] button next to Edit Schema to open a dialog box where you can describe the data structure of the source delimited file that you want to pass to the component that follows.

• Select the Key check box(es) next to the column name(s) you want to define as key column(s).

• In the design workspace, double-click tDBOutput_1 to open its Basic settings view where you can define its properties.

• Click Sync columns to retrieve the schema of the preceding component. If needed, click the [...] button next to Edit schema to open a dialog box where you can check the retrieved schema.

• From the Property Type list, select Repository if you have already stored the connection metadata in the Metadata node in the Repository tree view. Otherwise, select Built-In to define manually the connection information.

  For more information about storing metadata, see Talend Studio User Guide.

• Fill in the database connection information in the corresponding fields.

• In the Table field, enter the name of the table to update.

• From the Action on table list, select the operation you want to perform, Default in this example since the table already exists.

• From the Action on data list, select the operation you want to perform on the data, Update in this example.

• Save your Job and press F6 to execute it.

Using you DB browser, you can verify if the MySQL table, customers, has been modified according to the delimited file.

In the above example, the database table has always the four columns id, CustomerName, CustomerAddress and idState, but certain fields have been modified according to the data in the delimited file used.