topic Re: Output form various components ignores field length in schema in Talend Studio

Output form various components ignores field length in schema

Anonymous — Sat, 16 Nov 2024 12:18:35 GMT

I have several components that pass data through - tLogrow, tFileOutputDelimited, tFilterColumns, etc. I have the schema set such that the fields have a specific length. One of the fields has a length of 120 at the beginning of the data flow, and in the schema I set the length to 40. However, the data passing through the component ignores this data length.
Example, I have a tSalesForceInput that grabs data from salesforce.com. One of the fields has a length of about 120. I run it through a tLogRow to log to console, through a tFilterColumns to filter out some of the columns, through another tLogRow, and finally into a tMSSQLOutput to load the data into a MSSQL table. In every one of these components, I specify a length of 40 in the schema.
When it gets to the tMSSQLOutput, I get:
Exception in component tMSSqlOutput_1
java.sql.BatchUpdateException: Data truncation
I look at the data being logged to console, and I find that the data is ignoring the length specified in the schema, but is going through with the original length that it has when retrieved from the data source.
Is it supposed to do this? Why can we specify data lengths in the schema, if not to enforce the length of the field?
I know how to trim the data to prevent this, but I'm not understanding the purpose of being able to specify a field length in the schema.

Re: Output form various components ignores field length in schema

Anonymous — Thu, 19 Apr 2012 04:48:50 GMT

Hi
The usage of column length is to trim the excess content by tSchemaComplianceCheck or to create a new DB table when using tXXXDBOutput components.
Regards,
Pedro

Re: Output form various components ignores field length in schema

Anonymous — Thu, 19 Apr 2012 05:58:52 GMT

When it gets to the tMSSQLOutput, I get:
Exception in component tMSSqlOutput_1
java.sql.BatchUpdateException: Data truncation

Hi
Is the table created on tMysqlOutput component or it is created out of Talend studio. To fix the problem,
I. Trim the incoming data in job if you want to keep the table structure.
II. Alter the table to increase the length of column.
Best regards
Shong

Re: Output form various components ignores field length in schema

Anonymous — Thu, 19 Apr 2012 17:24:33 GMT

Hi
The usage of column length is to trim the excess content by tSchemaComplianceCheck or to create a new DB table when using tXXXDBOutput components.
Regards,
Pedro

So would it to be correct to say that many components simply ignore this?

Re: Output form various components ignores field length in schema

Anonymous — Fri, 20 Apr 2012 02:39:43 GMT

Hi
Yes. Usually column length in schema won't truncate data automatically.
Regards,
Pedro

Re: Output form various components ignores field length in schema

janhess — Fri, 20 Apr 2012 09:21:05 GMT

I raised this issue as an enhancement request but never heard anything from it.
I think the data length should be automaticvcally checked against the schema field length and truncated if too large. A warning could be issued.