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
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.
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.
@crotmn , have you verified below link?
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)
Talend named a Leader.
Kickstart your first data integration and ETL projects.
This video will show you how to add context parameters to a job in Talend Cloud
This video will show you how to run a job in Studio and then publish that job to Talend Cloud
This video will help someone new to using Talend Studio get started by connecting to Talend Cloud and fetching the Studio License