# prepQuery with WHERE IN() statement **URL:** \ **Category:** Ignition\ **Tags:** ignition81, scripting\ **Created:** [January 25, 2022, 3:37pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359 "2022-01-25T15:37:03Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![flavien](https://avatars.discourse-cdn.com/v4/letter/f/f475e1/32.png) [@flavien](https://forum.inductiveautomation.com/u/flavien)\ **Post date:** [January 25, 2022, 3:37pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/1 "2022-01-25T15:37:03Z")
Hi, I would like to execute query below but it return no rows : ```auto usages = [301,311] pyds = system.db.runPrepQuery("SELECT * FROM distribution_config WHERE usage_id IN (?)",[usages],"base_administrateur") ``` Do you have a solution without make a loop in my list ? ---
**Author:** ![thewebpleb](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/thewebpleb/32/27415_2.png) [@thewebpleb](https://forum.inductiveautomation.com/u/thewebpleb)\ **Post date:** [January 25, 2022, 3:46pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/2 "2022-01-25T15:46:21Z")
Which database / SQL version is this? Also, if you put `301` or `311` in the `WHERE` clause separately, does it return any rows? This should work in MySQL: ```python usages = [301,311] # see below. Use tuple instead of list pyds = system.db.runPrepQuery("SELECT * FROM distribution_config WHERE usage_id IN ?",[str(usages)],"base_administrateur") ``` **EDIT:** I forgot that MySQL doesn’t like square brackets… What you could do is make `usages` a tuple (ie. `usages = (301, 311)`) and then do `str(usages)` like I said above. However, you need to be careful with the tuple data structure. For instance, a `tuple` is immutable, unlike a `list`. The only potential limitation of @zacht’s approach is that if the `usages` list is long, your query will place a bunch of placeholders into the query and then fill them with the `args` list. I don’t know what the performance of that approach is vs casting a tuple to a string and inserting it into query once. ---
**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\ **Post date:** [January 25, 2022, 3:48pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/3 "2022-01-25T15:48:25Z")
```auto usages = [301,311] queryString = """SELECT * FROM distribution_config WHERE usage_id IN ({})""".format(','.join(['?']*len(usages))) pyds = system.db.runPrepQuery(queryString,usages,"base_administrateur") ``` ---
**Author:** ![flavien](https://avatars.discourse-cdn.com/v4/letter/f/f475e1/32.png) [@flavien](https://forum.inductiveautomation.com/u/flavien)\ **Post date:** [January 25, 2022, 3:49pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/4 "2022-01-25T15:49:27Z")
It’s MySQL. Your script not works thewebpleb. It returns a Syntax error. ---
**Author:** ![flavien](https://avatars.discourse-cdn.com/v4/letter/f/f475e1/32.png) [@flavien](https://forum.inductiveautomation.com/u/flavien)\ **Post date:** [January 25, 2022, 3:53pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/5 "2022-01-25T15:53:10Z")
Thanks zacht it works ! ---
**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\ **Post date:** [January 25, 2022, 4:24pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/6 "2022-01-25T16:24:31Z")
> [@thewebpleb](#): > > ```auto > usages = [301,311] # see below. Use tuple instead of list > pyds = system.db.runPrepQuery("SELECT * FROM distribution_config WHERE usage_id IN ?",[str(usages)],"base_administrateur") > > ``` I could be wrong (and I'm sure someone will correct me if I am), but I don't think a single `?` will ever work with multiple values. My understanding is that `runPrepQuery` will pass one literal value for each question mark. In the example above that would mean the query would only return rows where the value of `usage_id` is the string "[301,311]" for a list or "(301,311)" if you use a tuple (quotes not included in the value). Since `usage_id` is an integer (of some flavor) column, this will never be true. Casting a tuple to a string would work if you used a 'queryString' approach, but that would open the query up to SQL injection, exactly what we want to avoid with `runPrepQuery`. I can't speak authoritatively to the performance implications, but if I had to guess the two solutions are probably comparable. I doubt that dynamically building the string takes much more effort than typecasting a list/tuple, even with many arguments. ---
**Author:** ![thewebpleb](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/thewebpleb/32/27415_2.png) [@thewebpleb](https://forum.inductiveautomation.com/u/thewebpleb)\ **Post date:** [January 25, 2022, 4:46pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/7 "2022-01-25T16:46:02Z")
I agree that the approach I laid out above isn’t the best approach. However, it is valid SQL (for MySQL v8) because when you say ```auto usages = (1, 2, 3, 4, 5) query = "SELECT * FROM distribution_config WHERE usage_id IN {}".format(str(usages)) ``` the actual query that gets executed is ```auto SELECT * FROM distribution_config WHERE usage_id IN (1, 2, 3, 4, 5) ``` **NOT** ```auto SELECT * FROM distribution_config WHERE usage_id IN "(1, 2, 3, 4, 5)" ``` or ```auto SELECT * FROM distribution_config WHERE usage_id IN ("1", "2", "3", "4", "5") ``` @zacht Thank you for the feedback. I like your approach and agree with your SQL injection point. 👍 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 25, 2022, 4:49pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/8 "2022-01-25T16:49:47Z")
> [@zacht](#): > > I could be wrong (and I’m sure someone will correct me if I am), but I don’t think a single `?` will ever work with multiple values. This is correct. Or rather, correct for Ignition's generic connection environments. JDBC drivers for the popular brands extend the JDBC standard by defining methods to construct arbitrary data types, like PostgreSQL arrays. That could pass a list in a single parameter. But Ignition's pooling connection system doesn't give you access to branded extensions, and I can imagine the nightmare to even try. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 3, 2023, 3:19pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/9 "2023-08-03T15:19:20Z")
Note that at least one JDBC driver tolerates a form of this through Ignition's "Prep" queries: > [@Read/Write and Clear from an Array Tag (integer array)](https://forum.inductiveautomation.com/t/read-write-and-clear-from-an-array-tag-integer-array/74199/5): > > Does it work if you convert the list into a jarray first? v = [0,1,2,3] import jarray from java.lang import Integer v\_arr = jarray.array(v, Integer) To be honest I'm not even sure if array columns are supported or not, but see if this gets you to an error somewhere further. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 5, 2024, 7:44pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-where-in-statement/55359/10 "2024-01-05T19:44:37Z")
Follow up: > [@Taking a Dataset and Inserting it to a SQL temp table](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852/4): > > If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset: "#NAMES" "prefix","suffix" "#TYPES" "str","I" "#ROWS","4" "Hello","1" "Good Bye","2" "Welcome to","3" "Whoa!","4" To go with that, in a custom property named lookups, I created the following unbound datase…