Skip to main content Skip to complementary content

Setting length for a column in the schema

About this task

You can use the length setting of a schema for the following two purposes.
  • Setting the length of a table column when creating a database table.
  • Validating incoming data through tSchemaComplianceCheck.

This section gives an example of using the length setting in a schema to set the length of a column of a database table. For more information about using tSchemaComplianceCheck to validate data, see Validating data.

Setting the length of a database table column using schema

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.

Job in the design workspace.

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.
      Schema editor.
    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.

    Basic settings view of the component.
  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.

    Basic settings view of the component.
  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).

    Execution result.
  7. Change the schema column length to 7 for the two components and run the Job again.
    Schema editor.

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

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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here