SQL Update based on transformer

Queries and Resources
,
1

I want to have a SQL UPDATE run whenever any switch in a group of switches is flipped. I setup a "Run query automatically when inputs changes" query, and use a transformer to turn the switch group values into an update statement

conditionsStates Transformer

var conditions = {{conditionsSwitches.values}}
var conditionsThatAreTrue = {{conditionsSwitches.value}}
return conditions.map( v => `${v} = ${conditionsThatAreTrue.includes(v) ? "'true'":"'false'"}`).join(', ')

SQL

UPDATE list
SET {{conditionsStates.value}}
WHERE id = {{entity_id.value}}

However, when I preview the above query it says syntax error at or near "$1". I have a feeling that it's because the transformer is returning a string, and that is quoted? To check my theory, I previewed the transformer, grab it's output and pasted it into the SQL. It works great.

So two questions

  1. How can I use a transformer inside an update query
  2. If I do, and the values inside the transformer change, will the query automatically run?
2

Try putting your query into GUI mode not SQL mode and pass in an object (ie the value from your transformer) as the changeset

3

That worked, thanks! But sometimes I would like to write custom SQL. Why didn't this work? Is there are way to use SQL with inline JS?

1 Like
4

To protect you and your apps from sql injection attacks all SQL queries use prepared statements. This is good practice and not something to be dismissed.
It does however mean it's hard to make custom SQL statements because column names will be wrapped in quotes to escape them.

In a nutshell, it's to stop this:

3 Likes