I want to extract a text field from a database and insert it into some other database. So while extracting I used the REPLACE(message_text,'\'', '"') while selecting the test. I gave me an error. I changed that from my select statement and did it while initiating the global variable. etl.globals['message_text'] = message_text;
still I'm getting an error at the insert statement
insert into lcs_al_user_likes(user_id,liked_user_id,post_content,loop_id) values('${etl.globals['posted_by']}','${etl.globals['liked_user_id']}','${etl.globals['message_text']}',?batchLoopCounter);
saying
*You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'message_text']}')' at line 1*
I think it is not getting the global variable. That I say because when i print its value using log it just gives me
${etl.globals['message_text']}
as output. So please help me out here.
<query connection-id="lcsDBConnection">
SELECT forum_topic_post_id AS forum_topic_post_id, posted_by AS posted_by,message_text as message_text FROM lcs_tbl_forum_topic_post WHERE like_count>0 LIMIT ?batchSize OFFSET ?queryCounter ;
<script connection-id="jexl">
etl.globals['forum_topic_post_id'] = forum_topic_post_id;
etl.globals['posted_by'] = posted_by;
etl.globals['message_text'] = message_text.replace('\'', '"');
</script>
It looks like the problem is in INSERT statement, you should use prepared statement parameters escaping:
INSERT INTO lcs_al_user_likes(user_id,liked_user_id,post_content,loop_id) values(?{etl.globals['posted_by']},?{etl.globals['liked_user_id']},?{etl.globals['message_text']},?batchLoopCounter);
BTW As I understand, your original problem was quotes breaking the insert statement, so in this case with ?{parameter} syntax you don't need to use replace(...) function at all.