I'm taking a list of parsed items and passing them into a SQL query using a JS transformer to create a comma separated string, like:
'website1.com','website2.com','website3.com'
When I get the results, they're wrapped in double quotes, like this:
"'website1.com','website2.com','website3.com'"
If I test that same string manually without the beginning/ending double quotes, it works, so I assume the issue has something to do with the double quotes. I've tried a number of ways to remove the quotes, but have been unable to do so.
It shows up in the SQL query with double quotes? Or just the transformer output in Retool shows it that way?
Is the idea that you'll have each CSV element in single quotes in the database or do you want it to be 'website1.com,website2.com,website3.com 1'? If you want to maintain the single quotes you'll need to escape them in the insert by adding an additional quote to each single quote: '''website1.com'',''website2.com'',''website3.com 1'''. This is how the server knows that you're not ending a string when it sees a single quote.
The JS transformer output shows it in double quotes, but when I look at what goes into the SQL query, the string is wrapped in quotes.
I'm using an IN statement with my SQL query, so it needs to be formatted ('website1.com','website2.com') without the quotes. The output of the SQL query simply shows payloadSize:2, instead of a set of rows from the table that I see if I put that same data in manually without the quotes.
1 Like
Ah I see. Can you try disabling prepared statements in the resource settings and just use the untransformed value of the variable?
1 Like
Thank you - I updated the resource settings and it now seems to be working as expected. Thanks for your help!
3 Likes
Glad it worked, happy to help!
