topic Insert/update CSV files to database (MySQL char limit/ last table text garbled) in Talend Studio

Insert/update CSV files to database (MySQL char limit/ last table text garbled)

Anonymous — Sat, 16 Nov 2024 02:58:22 GMT

Hi there,

I wanted to use CSV to insert/update DB record (using dynamic schema) with the following job design.

(Insert and Update Record will shared the same 1st job design)

**the 1st job has a problem, so I will only explain this one.

**If there's a way to directly update a record (with dynamic schema), please let me know.

FYI, the input source will be from CSV and there's also original source in MSSQL while the final output should be in MySQL DB.

Insert/Create New Record from CSV

CSV Input

However, there are two major problems here (insert case).

1) When I set the tDBOutput as MySQL, the row limit error occurred.

Exception in component tDBOutput_1 (Data_Update1)
java.sql.SQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, 
not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some 
columns to TEXT or BLOBs

Is there a way to make it work in MySQL?

Some columns has 1000 characters while Talends only allows 100-255 characters per column.

Is there a way to dynamically set the column character limit? or make it all 1000 characters/column?

2) When I set tDBOutput to MSSQL, there's no problem with character limit.

However the last table's text data is not encoding properly, resulting in garbled text. Encoding is already set to UTF-8.

encoding setting

When trying the update, primary key(in my case, a compose pk and pk fields) is needed, however, I do not know how I can set PK dynamically, like how dynamic column is.
Does anyone know how to do so?

Right now, I'm working on a lengthy way to update record as the following:

1st Job: create and store record in temp DB

2nd Job: inject constraint into temp DB's tables

3rd job: migrate to real DB

If there's better way than this, please let me know.

And if there's anything unclear please let me know.

Thank you

Re: Insert/update CSV files to database (MySQL char limit/ last table text garbled)

manodwhb — Tue, 17 Mar 2020 02:56:15 GMT

@crotmn , have you verified below link?

https://community.talend.com/t5/Design-and-Development/MySQLSyntaxErrorException-Row-size-too-large/td-p/63404

Re: Insert/update CSV files to database (MySQL char limit/ last table text garbled)

Anonymous — Tue, 17 Mar 2020 03:17:58 GMT

@manodwhb

Yes, I did.
It works fine when migrating database from MSSQL to MySQL, however it doesn't work when importing from CSV...
Is there anyway to make this work? I think I heard about setting single row, but to be honest I'm not sure how that works.

For you reference, this is an error when I tried to import a single CSV to database.

Exception in component tDBOutput_1 (testconnection)
java.sql.BatchUpdateException: Data truncation: Data too long for column 'comment' at row 14
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)