I am trying to use a variable in a SQL SELECT query with an IN() operator in the WHERE clause. This is written in scripting on an action performed event handler of a button component. I am able to make this work with only one variable, but when multiple variables are selected the query return nothing. I believe when I have multiple selections, the value string is being entered into one arg vs. filling out the IN() operator as needed. Any ideas to make this work? Or other ways to use an variable amount of variables in a WHERE IN() statement?
Script:
data = event.source.parent.getComponent(âSelected CN Tableâ).data
values = ââ
for i in range(data.getRowCount()):
if i == 0:
cnlist = data.getValueAt(i,âCNâ)
else:
cnlist = ", " + data.getValueAt(i,âCNâ) + ââ
values += cnlist
value = values
result = system.db.runPrepQuery(âSELECT POI_HEADER.SERIAL_NUMBER, POI_HEADER.MATERIAL_DESCRIPT, POI_OPTION.PART FROM POI_HEADER INNER JOIN POI_OPTION ON POI_HEADER.ORDER_NUMBER = POI_OPTION.ORDER_NUMBER WHERE POI_OPTION.PART = (?)â, [value], âNX_POIâ)
event.source.parent.getComponent(âMachine Selection Power Tableâ).data = result
You cannot pass a list of values for the IN operator to a single question-mark substitution parameter. You will need to dynamically construct your SQL with the correct number of question marks and concatenate the list of values with any other question-mark values to supply. (In the correct order.)
JDBC prepared statement value substitution doesnât work the way youâre attempting to. For a variety of reasons (one primary is security), the ? placeholder in a query given to the driver can only be replaced by a single value. You need to format your query to have the correct number of placeholders, then insert each value separately; something like:
data = event.source.parent.getComponent(âSelected CN Tableâ).data
values = []
for i in range(data.getRowCount()):
values.append(data.getValueAt(i,'CN'))
query = """
SELECT
POI_HEADER.SERIAL_NUMBER,
POI_HEADER.MATERIAL_DESCRIPT,
POI_OPTION.PART
FROM
POI_HEADER
INNER JOIN
POI_OPTION
ON POI_HEADER.ORDER_NUMBER = POI_OPTION.ORDER_NUMBER
WHERE POI_OPTION.PART = (%s)""" % ", ".join("?" * len(values))
result = system.db.runPrepQuery(query, [values], âNX_POIâ)
event.source.parent.getComponent(âMachine Selection Power Tableâ).data = result
A couple of Python tricks in there:
Triple quotes (can be ''' or """, as long as youâre consistent) let you escape line breaks in one literal string. I find that syntax much more readable for potentially long lines like SQL queries.
The str.join(sequence) function. str can be any string, and it will automatically be used as the separator between any elements of sequence; so:
>>> ", ".join("?" * 5)
'?, ?, ?, ?, ?'
Values can be substituted into an existing string with the %s placeholder and a trailing %; see http://pyformat.info for more info (ânew-styleâ formatting is also available, if youâre on Ignition 8.x); so: (%s)""" % ", ".join("?" * len(values))
means "substitute the result of ", ".join("?" * len(values)) into the string, at the exact point I have the %s placeholder.
Thanks for the info. I was successful at inserting the correct number of placeholders(?) into the query based on the number of rows of data in the table component with your help. How do I insert more than (1) value value into the query to match the place holders without receiving the below error? Is the incorrect syntax related to the â,â following the (2) values?
caused by Exception: Error executing system.db.runPrepQuery(
SELECT
POI_HEADER.SERIAL_NUMBER,
POI_HEADER.MATERIAL_DESCRIPT,
POI_OPTION.PART
FROM
POI_HEADER
INNER JOIN
POI_OPTION
ON POI_HEADER.ORDER_NUMBER = POI_OPTION.ORDER_NUMBER
WHERE POI_OPTION.PART = (?, ?), NX_POI, [CN400463, CN400564], )
caused by GatewayException: Incorrect syntax near ','.
caused by SQLServerException: Incorrect syntax near ','.
Sorry - looks like I overlooked something; your query also needs to be modified slightly. Since youâre passing a series of placeholders as possible comparisons, you need to switch the operator to IN instead of =; so make the last line of the query: WHERE POI_OPTION.PART IN (%s)""" % ", ".join("?" * len(values))
Making some assumptions on how you need to retrieve your possible model values:
data = event.source.parent.getComponent(âSelected CN Tableâ).data
parts = []
models = []
for i in range(data.getRowCount()):
parts.append(data.getValueAt(i,'CN'))
models.append(data.getValueAt(i,'MODEL'))
part_values = ", ".join("?" * len(parts))
model_values = ", ".join("?" * len(models))
query = """
SELECT
POI_HEADER.SERIAL_NUMBER,
POI_HEADER.MATERIAL_DESCRIPT,
POI_OPTION.PART
FROM
POI_HEADER
INNER JOIN
POI_OPTION
ON POI_HEADER.ORDER_NUMBER = POI_OPTION.ORDER_NUMBER
WHERE POI_OPTION.PART IN (%s)
AND POI_HEADER.MODEL IN (%s)""" % (part_values, model_values)
result = system.db.runPrepQuery(query, parts + models, âNX_POIâ)
event.source.parent.getComponent(âMachine Selection Power Tableâ).data = result
The key part is swapping the string substitution to a tuple. Technically, you donât need to make the separate part_values and model_values variables - again, I just find it more readable past a certain point to move things out to a new line. See âbasic formattingâ for more context.
Another option is to use named placeholders and a dictionary to hold the replacement values. This is, again, a choice to make based on readability; in my opinion itâs basically a wash with only two, but could be a lot more usable if you had a lot more strings to replace:
"""
WHERE POI_OPTION.PART IN (%(parts)s)
AND POI_HEADER.MODEL IN (%(models)s)""" % {"parts": part_values, "models": model_values}
No, you can construct/supply the string containing the SQL any way you like in python. For short queries, I often just put the string constant right in the system.db.whatever() call. Using a query variable as shown is simply a convenient way to use pythonâs triple-quote syntax for multi-line string constants. And no, the variable doesnât have to be named âqueryâ.