rjapala
3 years agoNew Contributor III
My sql execute snap
Am facing this error when am trying to update column data in SQL dynamically please can anyone give me a solution how to update with correct syntax using with expressions or without
Am facing this error when am trying to update column data in SQL dynamically please can anyone give me a solution how to update with correct syntax using with expressions or without
You would want to enable the expression (click the =
next to the “SQL Statement” input) and then set the input field to:
"Update rakesh_sf_customer_order" +
" SET Error_reason = " + $Error_reason +
" Where Customer_ID = " + $Customer_ID + ";"
@rjapala: If you’re copy pasting the expression shared by @ddellsperger, you might see an error again.
Please put the double quotes by yourself as copy pasting the quotes throws an error on the snaps.
Even after modifying your query, you see an error, can you please confirm whether the $Error_reason and $Customer_ID is a valid field? We may be overlooking the typos if you have named it differently.
Best Regards,
Darsh
Thank you for suggestion @darshthakkar I have tried with different approach using Pipeline execute snap and i have kept the My sql execute snap in child pipeline it is working successfully now
Thanks for the support @ddellsperger @darshthakkar
Here’s an expression that I’ve been using to shorthand the string concatenation in generating an sql statement
"update my_table set my_field = '\$my_update_field', my_null_field = '\$my_null_field' where my_field = '\$my_condition_field'".replace(/\$\w+/gi, m=> eval(m) ).replace(/'null'/gi, 'null')
In your query, whatever string values you have pls quote them. If Set has string value then say Error_reason=‘$Error_reason’. And if where has string value then do WHERE Customer_ID=‘$Customer_ID’.
Thank you for reply @smudassir
I tried with this scenario also but still it is throwing error
Agreed with @ddellsperger
@rjapala: At any point you want to use the fields of input/output schema in your “execute” snaps, you’ll have to select the “=” decorator.
And when you do that, your query which is a string needs to be put under " " quotes.
Thank you so much for your reply @ddellsperger and @darshthakkar
But i am facing same issue again after i toggle the = expression
you’ll probably have to put single quotes around the string for $Error_reason
"Update rakesh_sf_customer_order " +
"SET Error_reason = '" + $Error_reason + "' "
"Where Customer_ID = " + $Customer_ID + ";"
Thank you for your reply @ddellsperger i tried giving quotes as well for string but facing same issue again
@darshthakkar yes the both fields and datatypes are valid but also the same issue am facing
@ddellsperger @darshthakkar one thing when i just validate the pipeline it is not throwing an error when i execute then it is throwing an error
This is really odd @rjapala.
Can you please try the following:
That’s good news @rjapala, congratulations.
I’m still amused on why your previous query didn’t work, I will try it out on my end when I find a use case and share the findings here.
Thank you @darshthakkar