topic Re: Field length with dynamic schema columns in Talend Studio

Field length with dynamic schema columns

Anonymous — Wed, 06 Nov 2013 09:44:37 GMT

I'm currently attempting to use the Dynamic schema settings for columns (in a tFileInputDelimited) to read in a dynamically structured file and then add it into a staging table in an MS SQL Server DB before further processing.
This seems to work fine as it is but I'm losing rows through truncation whenever a field is over 100 characters. Is there a setting somewhere that I can override this 100 character limit?

Re: Field length with dynamic schema columns

Anonymous — Wed, 06 Nov 2013 10:39:04 GMT

I hadn't realised you could get the rejects from a DB output component so that might come in handy in the future. I assume that it kills performance if you disable batch mode though?
The reject is giving me the error that I expected, data truncation on these lines (they have a 103 character field).

Re: Field length with dynamic schema columns

Anonymous — Wed, 06 Nov 2013 10:44:03 GMT

Hi,

I assume that it kills performance if you disable batch mode though?

It may reduce job performance. But sometimes we need the rejected row error info from DB.
For exception of Data too long for column.
Please take a look at KB article TalendHelpCenterata too long for column. Let me know if it is working for you.
Best regards
Sabrina

Re: Field length with dynamic schema columns

Anonymous — Wed, 06 Nov 2013 12:23:26 GMT

I'm currently reading the data in and pushing it to the DB to stage it so I have dynamic columns on both the tFileInputDelimited and the tMSSqlOutput so I can't set the field length in my table (putting a length in a dynamic column doesn't seem to do anything).
Is there a way I can override this default 100 length somewhere or do I need to turn my staging database into a key value pair database?

Re: Field length with dynamic schema columns

Anonymous — Thu, 07 Nov 2013 08:43:23 GMT

Hi,

I'm currently reading the data in and pushing it to the DB to stage it so I have dynamic columns on both the tFileInputDelimited and the tMSSqlOutput so I can't set the field length in my table (putting a length in a dynamic column doesn't seem to do anything).
Is there a way I can override this default 100 length somewhere or do I need to turn my staging database into a key value pair database?

The max length of character is 255. You can have a try to turn your staging database into a key value pair database to see if it is working for your case. Please let me know your result.
Best regards
Sabrina

Re: Field length with dynamic schema columns

Anonymous — Thu, 28 Apr 2016 20:30:15 GMT

Is there a setting to apply to dynamic schema and change default size from 100 to 255.
the table i created using dynamic schema has all datatypes as varchar which is ok, but size is just 100.
Isn't it supposed to be 255

Re: Field length with dynamic schema columns

Anonymous — Thu, 08 Sep 2016 18:01:09 GMT

Is there a setting to apply to dynamic schema and change default size from 100 to 255.
the table i created using dynamic schema has all datatypes as varchar which is ok, but size is just 100.
Isn't it supposed to be 255

Bump

Re: Field length with dynamic schema columns

Anonymous — Tue, 13 Sep 2016 08:02:37 GMT

Hi,

the table i created using dynamic schema has all datatypes as varchar which is ok, but size is just 100.
Isn't it supposed to be 255

Could you please post your schema setting screenshot into forum?
Best regards
Sabrina

Re: Field length with dynamic schema columns

TomA — Wed, 26 Oct 2016 11:37:38 GMT

Hi, I had the exact problem. I tried to use a tJavaFlex to set the column length, but could not find a method to do so.
In the end, I had to just let the table be created with the default length of 100 for each column, but limit the tFileInputDelimited to a single row.
Then used a tMSSQLColumnList to issue an alter table alter column statement for each column, to set the length to 4000. Then load all data from the source file, with truncate table action on the output table.
But I'd like to know if there is a more elegant way...

Re: Field length with dynamic schema columns

Morpheus — Wed, 26 Jun 2019 17:44:42 GMT

@xdshi , is there any elegant solution for this apart from adding an additional alter statement into the flow as suggested by @TomA ?

Re: Field length with dynamic schema columns

Anonymous — Thu, 27 Jun 2019 03:24:57 GMT

Hello @chanaka

Could you please let us know if this related topic helps?

https://community.talend.com/t5/Design-and-Development/resolved-Generic-Talend-Job/m-p/92354

Best regards

Sabrina

Re: Field length with dynamic schema columns

Anonymous — Wed, 18 Sep 2019 12:17:27 GMT

Sorry to bump this, but does anyone have a simple solution to this? I've read the various links and posts and nothing is helping me. I am a SQL developer, not a Java developer and I am trying to work with a CSV that has over 500 columns I have received from a client. I need to build the schema dynamically from the column headers, and then dump the data into a SQL table, so that the rest of my job can then select only the required columns to do the rest of the work I need.

It seems ridiculous that you cannot overwrite the column length for dynamic schema settings. VARCHAR in SQL can handle 8000 characters. Limiting this to 100 makes it almost useless.

Re: Field length with dynamic schema columns

Anonymous — Wed, 18 Sep 2019 20:18:22 GMT

You can manually set the column length of dynamic columns.

Consider the following code snippet

Dynamic dyn = row1.dynamic_column;

for(int i = 0; i < dyn.getColumnCount(); i++){
	DynamicMetadata meta = dyn.getColumnMetadata(i);
	meta.setLength(5000);
}
row2.dynamic_column = dyn;

This will set them all to 5000

Re: Field length with dynamic schema columns

Anonymous — Mon, 23 Sep 2019 15:09:31 GMT

@evansdar

FOrgive my ignorance, but where would I use that code? As I said in my post above, I'm not a java developer, I'm a SQL developer. My talend job only has 4 components

DBConnection >>> tFileInputDelimited >>> tMap >>> tDBOutput

tFileInputDelimited schema has one "Dynamic" type column.

Re: Field length with dynamic schema columns

Anonymous — Mon, 23 Sep 2019 21:09:16 GMT

You'll want to put it in a tJavaRow after whichever component the defined Dynamic Schema is in (looks liketFileInputDelimited in your job)

The name of the row connection is how the data will be passed into and out of the component.

Re: Field length with dynamic schema columns

Anonymous — Tue, 24 Sep 2019 10:45:04 GMT

@evansdar

Brilliant. Thank you so much. That's all sorted now.