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:
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 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.
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.
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.