Question

How to handle single quotes in between data

  • 21 July 2022
  • 3 replies
  • 34 views

Hi Team

 

am using query executor stage and its generating the insert statement below way but when the single 

 

INSERT INTO ${SCHEMA_NAME}.${TABLE_NAME}(a,b,c) 
VALUES ('${record:value("/a")}', '${record:value("/b")}', '${record:value("/c")}')

 

if records are getting single quotes the insert fails how to handle such case


3 replies

Userlevel 3
Badge

hi @akanshajain6793 

 

Replacing the single quotes with 2 single quotes should do the trick.

 

Adding something like this in an Expression Evaluator will do the replace for you

${str:replace( record:value('/a'),"'","''")}

 

You will have to do this for all the columns that are suspected to have the single quotes. 

 

If you use JDBC Producer then you dont need this workaround. It is handled automatically.

 

thanks @saleempothiwala  that helped i have one more issue where query executor stage is making null to empty string but in ms sql i have to load as null only . Am using kafka as my source how to make sure it doesnt get converted to empty string if its coming as null from source or even if that column not even coming from source

Userlevel 3
Badge

@akanshajain6793, please mark the post as ‘answer’ and open another post for the query executor. This will help other community users when they search for a specific query.

Reply