CODEWITHSUNDEEP
mysqltriggerstalend
Arhyaa
Arhyaa

Reputation: 369

Talend - Check columns lenght before inserting into mysql database

I'm trying to use Talend Open Studio for Data Integration for a BI school project. I have a txt file with some data like this :

NoClient;Name;Region;State;City;Postcode
24;Edna Thomas;West;California;Laguna Niguel;92677

I used jobs to transform data and insert it into tables. It works nicely.

Now, I'd like to handle SQL errors. For example, if the column length specified in the DB is 10 and if the job tries to insert a 11 length data in that column, I will get an SQL error.

How can I achieve it ? Is there a specific feature in tMysqlOutput or should I simply use triggers like this to check data before inserting :

CREATE TRIGGER my_trigger
BEFORE INSERT 
ON my_table
  FOR EACH ROW BEGIN

    IF (SELECT LENGTH(NEW.Noclient)>255) THEN
    // instructions
END IF

Hope it's clear enough ! Thanks in advance, sorry for bad english :-)

Upvotes: 1

Views: 367

Answers (2)

Balazs Gunics
Balazs Gunics

Reputation: 2077

There's a component for this: tSchemaComplianceCheck A nice example can be found at: http://dwetl.com/2015/03/30/data-validation-using-tschemacompliancecheck/

Upvotes: 1

Théo Capdet
Théo Capdet

Reputation: 1062

Maybe you can do a check in each variable in a tmap.

Like for example :

StringHandling.LEN(var) <= 10 ? var : StringHandling.LEFT(var,10)

And you adapt in function of the size of your field.

Upvotes: 1

Related Questions