Talend community forum / Inserting quotation marks in MySQL database.

sdel · 2012-07-12 18:17:25

I have a tJavaRow connected to a tMysqlRow

I need to insert a string that contain a ‘ for esample L’some name. This needs to be escaped in the insert statment

INSERT INTO books (title) VALUES (‘L\’some name’);

However if I hard code this statement in the tMysqlRow I need to add a second \ as tMysqlRow removes the first

INSERT INTO books (title) VALUES (‘L\\’some name’);

This is ok but in my job I am using the tJavaRow to add the \\ before the ‘ because tJavaRow also removes the \ characters I have added two more \\

output_row.title = input_row.title.replace("'", "\\\\'");

This leaves \\ before the ‘ when its is sent to the tMysqlRow but this fails

Please let me know if you know why.

Uploaded Images

Last edited by sdel (2012-07-12 18:22:25)

pedro · 2012-07-13 03:17:06

Hi

Why do you add a second \ in tJavaRow?
The following string is ok.
"INSERT INTO books (title) VALUES ('L\'some name');"

Besides, I notice that your quotation is different from the normal quotation ' '.
Yours is ``.

Regards,
Pedro

sdel · 2012-07-13 10:44:24

I am not sure why the quotes display differently but they are the correct quotes.

"INSERT INTO books (title) VALUES ('L\'some name');" if used in a tMysqlRow fails I have tried it. "INSERT INTO books (title) VALUES ('L\\'some name');" with two \ works.

I only have one tJavaRow I need to use this because I do not know where in the string the ' will be.

If I add two \ using the code below and send the output to a tlog I can see that both are stripped off.

output_row.title = input_row.title.replace("'", "\\'");

If I add four then two get through but they do not get inserted to the db.

If you know why this is please let me know.

pedro · 2012-07-13 11:50:24

Hi

Code:

if used in a tMysqlRow fails I have tried it

Do you get any error? Or what result do you get?

Regards,
Pedro

sdel · 2012-07-13 12:03:29

I get "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 'some name'" and it does not insert.
Thanks

pedro · 2012-07-13 12:43:24

Hi

Here is a workaround.
First add a context variable in your job(e.g. context.new1).
Then code in tJavaRow as below.

Code:

context.new1 = "INSERT INTO books (title) VALUES ('"+input_row.title.replace("'", "\\'")+"');";

Then type context.new1 in the query field of tMysqlRow.

I test it and it works fine.

Regards,
Pedro

sdel · 2012-07-13 13:12:08

Thanks for your help.

pedro · 2012-07-16 07:31:23

Hi

Glad to help you.
Feel free to ask any question here.

Regards,
Pedro

Announcement

Unanswered posts

#1 2012-07-12 18:17:25

Inserting quotation marks in MySQL database.

#2 2012-07-13 03:17:06

Re: Inserting quotation marks in MySQL database.

#3 2012-07-13 10:44:24

Re: Inserting quotation marks in MySQL database.

#4 2012-07-13 11:50:24

Re: Inserting quotation marks in MySQL database.

Code:

#5 2012-07-13 12:03:29

Re: Inserting quotation marks in MySQL database.

#6 2012-07-13 12:43:24

Re: Inserting quotation marks in MySQL database.

Code:

#7 2012-07-13 13:12:08

Re: Inserting quotation marks in MySQL database.

#8 2012-07-16 07:31:23

Re: Inserting quotation marks in MySQL database.

Board footer