Truncate data as per field size using Select values

[Cody2590]

I am using a file to fetch data and insert that data into table.
However if the data size is greater than the field size of table its failing. I have used Select values transform and have set the field size as per the DB field size but its still failing.
Can we have a solution for this?

[liosc]

Hi. I think the "Select values" alone is not enough here. You should use a "Strings cut" transform or something similar to actually reduce the length of data. If you are able to develop your own transform, a final solution here could be having a transform who cuts the fields based on the target database columns.

[Cody2590]

Strings cut worked but yes I will need to add it in each and every transform. Any additional settings at DB level or connection level for truncation will help as it will be universal for all.

[dmainou]

Another thing you can do is to configure your database to cut strings that are too long.

For example in mysql you can add this option to the connection string and it will truncate the string rather than rejecting the transaction.

Parameter Value
jdbcCompliantTruncation false

[Cody2590]

I am using oracle driver so which property I can use for truncation?

[dmainou]

Oracle is particularly painful database to work with.

Here is my Gemini search for you.
https://g.co/gemini/share/c4958d4c703f

[Cody2590]

This option is not working. Looking for a more concrete solution.

[ossDataEngineer]

@liosc, @dmainou, @hansva, @bamaer - I noticed one behaviour with "String cuts" transform - it does not take NewLines into consideration when cutting strings to confine to Oracle VARCHAR2(4000) limit. Hop's formula -> LEN([short_string_after_string_cut]) suggests that it is 4000, but when ingesting these strings to Oracle it complains with ORA-12899 "Value to large for column". Data inspection in notepad editors suggest that data after string cut is bigger than 4000.

Can you help on how to perform string cut treating newlines as characters?

[ossDataEngineer]

Goal: Long strings should be trimmed in Apache Hop to fit in Oracle column. The newlines/line-breaks should be preserved and counted towards Oracle column size

[dmainou]

Ok.
Start by creating a unit test that proves the current and desired behaviour (then use this to validate correct working pipelines at every upgrade)

Use the calculator step to:
1 return the length of the field,
2 add a constant with the max number of characters
3 calc the difference
Use the JS or UDJC steps to cut your string to length.
Validate this method takes into consideration carriage returns.

If it doesn't work you are going to have to replace the carriage returns with something else, count that something else repeat the above and then replace the something else with carriage returns

Add a feature request ticket to enhance the strings cut step to enable cutting to length based on a column rather than a hardcoded value. Add a second ticket that enables handling of carriage returns.

[dmainou]

also, another thing you can try is to play with the file input step and try and configure the ingress of data so that it matches the length from the get go.
This may or may not work

[dmainou]

Oh another thought.
There is a non zero chance that carriage returns and Line feeds are being read as 2 characters instead of 1 and impacting some methods of counting.

In that case replacing \n with ~~ would work well with a length calculation. you can then reverse it and voila it works.