Special Characters in Scriptella, jexl

Question

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.gl‌obals['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>

My bad, etl.globals['message_text'] = message_text.replace('\'', '"'); is the change I made while initializing the global variable. — Kranthi, Nov 12 '12 at 15:34
Please post the latest version of XML you have (the one which leads to an error - near 'message_text']}')' at line 1*) — ejboy, Nov 12 '12 at 15:41
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); That is the line where I'm getting an error. Do you want me to post the complete XML file? — Kranthi, Nov 12 '12 at 15:49
Ok, now it's more clear, but please post the whole ETL file in the body of your question! — ejboy, Nov 12 '12 at 15:53

ejboy · Accepted Answer · 2012-11-12T16:14:22.640

0

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.gl‌obals['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.

edited Nov 12 '12 at 16:14

answered Nov 12 '12 at 16:08

ejboy

4,081
5
30
32

insert into lcs_al_user_likes(user_id,liked_user_id,post_content,loop_id) values(?d57113a2-96d0-47fa-8107-71cf8b812393,?5ea8d41e-6b1d-4389-bdbc-912edf08d495,?${etl.globals['message_text']},?). Parameters: [2]. Error codes: [07001, 0] SO As i See, it is not getting message_text – Kranthi Nov 12 '12 at 16:19
Why do you need etl.globals for this case? It looks like a datatransfer task which can be managed withiut etl.globals. Why can't you put insert here: ? – ejboy Nov 12 '12 at 16:21
yeah, you are right. I'm new to scriptella, that is why I have used global variables everywhere. Thanks a lot. You saved me. – Kranthi Nov 12 '12 at 16:34
I also have another problem, I was neglecting this from a long time. I would be more than happy, if you help me in this too. The text field which I get using scriptella has a "CLOB:" string infront of every text it extracts. I dont know why it comes, That doesnt happen when I run it in mysql. – Kranthi Nov 12 '12 at 16:36
CLOB: is printed when somebody tries to print CLOB value. It looks like you are using ${} when inserting(or printing to the console) these values. Try using ?{textColumnName} instead of '${textColumnName}' – ejboy Nov 12 '12 at 16:53

Special Characters in Scriptella, jexl

1 Answers1