topic Re: mysql syntax error for a basic operation in Talend Studio

mysql syntax error for a basic operation

Anonymous — Tue, 03 Apr 2018 20:24:33 GMT

I have two jobs which are almost identical:

1 fetch data from source DB. Source DB 1 and DB 2 have the same collation UTF8 etc, so there shouldn't be any differences.

2 trim a source column data with FTRIM()

3 update or instert into target DB by Talend key. So Action on Data is "Update or insert".

For job 1, it's working as expected.

For job 2, am getting strange MySQL syntax error after tMap -> mysqlOutput and inserting:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `name` = '<company name>' "

I don't understand why Job1 works but Job2 doesn't. Attached two screenshots: first having the working Job schema for the output before MysqlOutput, second having for Job2.

Anyone having any idea? The only suspicious data I can see is a string having 'Oü' in it but according to tests, it's not an issue..

Re: mysql syntax error for a basic operation

Anonymous — Wed, 04 Apr 2018 00:59:09 GMT

Unfortunately you have not given enough information for me to help. Can you show the error (a screenshot) and show the configuration of your tMap and your database insert component (the one which fails)? The error that you have posted looks more like it is linked to a query component, so can you show the configuration of your query component as well?

Re: mysql syntax error for a basic operation

Anonymous — Wed, 04 Apr 2018 14:29:26 GMT

okay here you go

source:

ERROR

TMAP:

INSERT COMPONENT

Re: mysql syntax error for a basic operation

Anonymous — Wed, 04 Apr 2018 14:37:36 GMT

I think you may have a couple of issues here. Have you selected "insert or update" for a reason in the component settings? If so, have you configured the "field options" in the advanced settings? You need to specify update keys and whether fields can be inserted and/or updated. Also your input component looks like the schema may not be aligned with your query.

By the way, editing out information is not really necessary when it comes to database queries and error messages unless you are dealing with top secret data. In which case it makes sense to replace it with something different to protect your client rather than making it unreadable for anyone trying to help 🙂

Re: mysql syntax error for a basic operation

Anonymous — Wed, 04 Apr 2018 15:30:39 GMT

- insert and update is for a reason. goal is to extract data from that one field in source DB (company name) and then update insert it to target DB Companies -table.

- key is configured to Company.name of target DB, as the update or insert must follow that company.name

- the input schema is more excessive than target, need only one field, but in other jobs I haven't seen it matter.

As reference, I have 99,9 % similar job that works fine. Only difference is that the source DB is physically a different DB than here in this problem case.

Re: mysql syntax error for a basic operation

Anonymous — Wed, 04 Apr 2018 15:35:27 GMT

hmm I selected name column as "key in update" in the Field options, now it seems to process without error!

So seems good.

BUT, I checked the other 99,9% similar job and it DOESNT have Field options modified at all and it processes the similar task well. So there company.name DOESNT have "key in update" selected....

=> I can't understand why this Job 2 (under discussion here) seemed to require this checkbox, while the job1 didnt. Any idea? They insert/update to SAME target DB..

Re: mysql syntax error for a basic operation

Anonymous — Wed, 04 Apr 2018 15:45:15 GMT

What is the difference between the two jobs? This could be several things. It could be that the first job (that works) didn't need to update and was able to insert as the primary key on the table allowed it. It could be that it makes sense for you the first job to update using primary key. It could be a number of things. However it is not a good idea to update without using the field options. You need to specify how you want to update. Your 1st job (that works) is lucky to be working. That is how you need to be think about it.