Think I tried every way possible based on your suggestion, but nothing works. It's really quite odd. If it helps, the process I've got currently is:
import a list of values from a file to a table component
use a js transformer to format that list so I can use it in a sql IN statement. ie creates a string for the values that looks like this: 'value1', 'value2', 'value3'
use a sql statement to then query an ms sql table for information where those values are in the ms sql table.
sounds simple enough - works great if I just manually copy and paste the transformer result into the sql query (not query sql with json) and run it; but if I try to reference the transformer results in the IN statement "... where webcode IN ({{jstransformer.data}}) ... " or variations thereof - it just won't work.
even tried this (sql resource) - which didn't work of course:
select web_code
from dbo.sql_table
where web_code in ({{ js_transformer.data}});
Ah, please ignore my earlier reply as I hadn't understood the resource type you were querying. While you could get a roundabout method working down that path of querying json with sql... I would suggest filtering based on your resource type's go-to where clause style
I probably won't be of anymore help than this (sorry!), but you should find something to work that might look similar to this. I imagine if you aren't filtering on an id, rather a string, select * would do the trick and maybe string_split would still work the same as the example given too, but not too sure really without access to a sql server db
select web_code
from dbo.sql_table
where web_code IN ( SELECT * FROM string_split({{ ['value1', 'value2', 'value3'] }}, ',') )
Addressing the error, and, I don't mean to back track, (or suppose the native sql server is inconvenient and query json with sql is preferred), I'm not able to reproduce the error you're seeing about enabling prepared statements on query json with sql. Is the sql_data_table populated with a valid value? Are you on cloud running 3.9.0?
Like I said it's odd. By all accounts, everything should work fine. In answer to the query, yes, on cloud running 3.9.0. The sql_data_table is populated with valid data. So far, the only way I've been able to get things to work, is using Query JSON with SQL, but it produces that error.
I did try just a standard query, direct to another SQL table using an appropriate resource (same database) with the following code, but it doesn't work either; but it will work, if I manually copy and paste the js.transformer data results into the SQL IN statement.
Well, string_split wont work as the server in question is pre-21016. With the alternative pre-2016 code, the query ran, but no results were given - basically the same results as trying to pass in the {{js_transformer.data}}, in my previous post.
I also tried just using a text field and manually typing the example values in, and referencing that - but again, same result, when query the resource directly.
With the {{js_transformer.data attempt}}, if it was included the double quotes at the open & close, I'd understand the result, but it doesnt. So at the moment, it seems the only option is to use Query SQL with JSON, and get results, while accepting the error displayed.
