SYNTAX: Jython SQL Scripting: SELECT * FROM (table) WHERE x = 1 AND y = 2

Ignition
1

Hello,
I want to use a complex WHERE statement and I am having trouble finding anything about syntax, I can successfully do it on one condition, I just get jammed up on the second condition.

my code:
system.db.runPrepUpdate(""" DELETE FROM PanelShop.dbo.ActiveList WHERE (?) in (PONum) AND (?) in (JobNum)""", [myValue, ‘{Root Container.Power Table.SelectedJobNum}’])

I have tried several variations, such as;

WHERE (?,?) in (PONum, JobNum)

WHERE (?,?) in (PONum AND JobNum)

Does anyone know the correct syntax?

2

You can't use the curly-brace relative reference syntax in scripting - only in expressions. Depending on where you're running this code (say, a button's actionPerformed event) your property reference would be something like event.source.getParent().getComponent("Power Table").SelectedJobNum to read the same property.

3

Thank you for the response PGriffith, could you weigh in on the syntax of the query?
Thank you either way.

4

You probably want to just use where <col> = <value>, not an in comparison; that is normally used to check membership of or in a list of values. Also, when using triple quotes, you can break your query out onto multiple lines, which I like for future readability:

query = """
DELETE 
FROM 
	PanelShop.dbo.ActiveList 
WHERE 
	PONum = ? 
	AND JobNum = ?
"""

args = [myValue, event.source.rootContainer.getComponent("Power Table").SelectedJobNum]

system.db.runPrepUpdate(query, args)
3 Likes