I am trying to run a Snowflake query from a javascript script that passes a dynamic variable to the Snowflake query, but on the Snowflake side the variable always comes through as ":1".
I've tried this using the additionalScope option in the query.Trigger() function and also by creating a retool variable and using that in the script and query.
So for example (using the retool variable for now, but I've also tried additionalScope), if I have a variable in my javascript code called "rows" and I do the following: changedRows.setValue(rows);
the retool variable does get correctly set. Then in Snowflake I'm doing the following: insert into modified_records_src values {{ changedRows.value.toString() }};
The query fails and when I look in the query history within Snowflake I see the following: insert into modified_records_src values :1;
As a side note, I have to write my own sql statement and cannot use the GUI because I'm using OAuth with Snowflake, which does not support the GUI option. Any suggestions would be greatly appreciated.
Just an update here, the :1 seems to be to prevent sql injection within Snowflake and the nature of it is to prevent what I'm trying to do by building a query in javascript and then running it in Snowflake as a parameter.
I was able to trigger individual Snowflake executions from javascript with passing in individual parameters but I was finding that the javascript would quickly loop through all of the changes and fire off all of the update statements and then trigger the full table refresh at the end and the full table refresh would ultimately finish before all of the update statements ran. This was causing confusing scenarios for users. They would see some of their changes but not all of them and have to do another manual refresh after to see all of the changes, it just wasn't a viable option.
I couldnt figure out a way to do bulk updates without the GUI option so I gave up on using OAuth with Snowflake and will revisit OAuth if the GUI option is ever supported. Not having that user audit trail within Snowflake is likely going to be a show stopper for this project unfortunately.
Yet another update incase anyone comes across this issue and needs to resolve it. I managed to find a setting in the resource settings called "Disable converting queries to prepared statements" and this resolved the issues I was having related to this post.
I'm glad to hear that you were able to resolve this issue. That said, we typically don't recommend disabling prepared statements unless absolutely necessary, as it does prevent SQL injections. You can read more about the ramifications of disabling it here.
