Execute 'LOAD DATA LOCAL INFILE' command doesn't work in tMysqlRow

One Star

Execute 'LOAD DATA LOCAL INFILE' command doesn't work in tMysqlRow

Hi,
I need to load data from a file into a mysql db table. I am using tMysqlRow and execute the 'LOAD DATA LOCAL INFILE...' command directly. However, none of the rows was inserted (no errors reported). However if I copy the exact same SQL and execute it directly, the rows are affected. Note that I cannot use mysqlbulkexec component to do this because I need to identify the columns (schema) dynamically during run time.
I tried other simple statements like 'insert into..' and they are fine. Just this particular one is not working.
Please help. I must be missing something very obvious. If there is a bug, is there another way to do this?
Here is the sample sql:
LOAD DATA LOCAL INFILE '/temp/data.csv' INTO TABLE tmp_table FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (source_id, @c_language,@c_sla) SET c_language = (CASE WHEN @c_language = '' THEN NULL ELSE @c_language END),c_sla = (CASE WHEN @c_sla = '' THEN NULL ELSE @c_sla END);
Thanks
Sarah
One Star

Re: Execute 'LOAD DATA LOCAL INFILE' command doesn't work in tMysqlRow

Hi again,
I found out the reason why it is not working. For some reasons, when I specify the @ variables, it didn't work. Seems like I can't set the values using @ variables. However, as mentioned the sql itself works fine when executed directly in mysql. Any idea?
I need to use @ var to set empty string in file to null. The file itself was exported by tpivotToColumnsDelimited and there is no option to set null values to \N as required by mysql INFILE command..
Thank
Sarah
One Star

Re: Execute 'LOAD DATA LOCAL INFILE' command doesn't work in tMysqlRow

Hi Talend, any followup? thanks!
One Star Lie
One Star

Re: Execute 'LOAD DATA LOCAL INFILE' command doesn't work in tMysqlRow

Hi
I don't know if it'll help, but... you could create a sql procedure then call it from your tMysqlRow ?

Regards,
Lie
One Star

Re: Execute 'LOAD DATA LOCAL INFILE' command doesn't work in tMysqlRow

Thanks Lie. You can definitely do it with store procedure. Although I would like to keep ETL logic inside Talend.
As a workaround, I am doing something similar to achieve the same result - by flattening the records using sql - dynamically generated (but not in a store procedure), and use tMysqlRow to process whatever I need. In fact it seems to work better since no longer do I depend on an external file.
However, this post was more about whether there is a bug on using tMysqlRow/driver to execute this specific syntax in case I need to use it for something else.