Setting the length of a database table column using schema - Cloud - 7.3

Talend Studio User Guide

Version
Cloud
7.3
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Cloud
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Design and Development

About this task

The Job in this topic demonstrates the way to use the length setting of a schema column to set the length of a MySQL database table column when creating the database table. You need to have a valid MySQL user account with necessary permissions to run the Job.

Take the following steps to set up and run the Job.

Procedure

  1. Launch Talend Studio and create an empty standard Job.
  2. Drag and drop tFixedFlowInput and tMysqlOutput to the work space from Palette.
  3. Connect the two components using a Row > Main connection.
  4. Double-click the tFixedFlowInput component to open its Basic settings view and do the following.
    1. Open the schema editor by clicking the [...] button next to Edit schema and add a column (testColumn in this example), setting Type to String and Length to 3.
    2. Select Use Single Table and enter a string ("abcdefg" in this example) in the Values table
    3. Leave the other options as they are.

    This component sets the string (that is, abcdefg) to write to the database table to be created. The length of the string is 7 and the length of the column in the component schema is 3.

  5. Double-click the tDBOutput component to open its Basic settings view and do the following.
    1. Provide your user account credentials in the corresponding fields.
    2. Click Sync columns to synchronize the schema with that of the tFixedFlowInput_1 component.
    3. Select Drop table if exists and create from the Action on table drop-down list.
      If you are sure the table does not exist, you can also select the Create table if does not exist option.
    4. Select Insert from the Action on data drop-down list.
    5. Leave the other options as they are.

    This component tries to create that table and insert the incoming data to the table.

  6. Press F6 to run the Job.

    The following figure shows the execution result, which prompts a data too long error. This is because the length of the string to write (that is, 7) is larger than the column length (that is, 3).

  7. Change the schema column length to 7 for the two components and run the Job again.

    The Job runs successfully, indicating that the string is written to the table properly.

Results

The length setting in a schema works when you create a database table. It determines the length of the corresponding column in the database table.