system.db.runQuery line continuation

Ignition
1

Hi guys,

I am trying to run a python executed SQL query when a dropdown list’s focus is lost (after a selection is made).

The query string is very large and I would like to code it in multiple lines.

Is there a way to do this or is 1 long string the only way?

2

Python supports wrapping strings in a bunch of different ways, but what I use most often for things like queries is a triple-comment:

query = """anything in this
block of text
is part of the same
literal string"""
1 Like
3

Hi again, I am having trouble with using placeholders with your method...

QueryString = ("""
SELECT OrderNo as [Order #],
Priority,
DueDate as [Due Date],
PartNo as [Part #],
PartDesc as [Part Description],
SUBSTRING(b.MaterialName,1, CHARINDEX('-',b.MaterialName) - 1) as [Membrane Part #],
REPLACE(m.ClassCode, 'MEMB-','') as [Membrane Material],
QueuedQty as [Quantity]
FROM vwLots l LEFT OUTER JOIN vwBOM b ON l.PartNo = b.BOMMaterialName
AND b.ItemType = 'INPUT'
AND b.MatlClassCode = 'SLITROLL'
AND b.MaterialName NOT LIKE 'MT%'
LEFT OUTER JOIN Material m ON m.MaterialName = SUBSTRING(b.MaterialName,1, CHARINDEX('-',b.MaterialName) - 1)
WHERE RouteStepName = 'VA'
AND LotID NOT LIKE 'A%'
AND LotID NOT LIKE 'OT%'
AND OrderNo = %s
ORDER BY Priority ASC, DueDate ASC""" % event.source.selectedLabel)
QueryTable = system.db.runQuery(QueryString, "MES")
event.source.parent.getComponent('TP_MPN').text = QueryTable[0][5]

Can you explain what I am doing wrong?

4

Python uses %s (and a few other identifiers) as the keyword for string replacement - so at lines in your string like AND LotID NOT LIKE 'A%' it thinks you want to replace %' using the ' format specifier - which is invalid. Replace the literal percentages in your output string with double percentages in the Python code to get the correct output:

QueryString = ("""
SELECT OrderNo as [Order #],
FROM vwLots l LEFT OUTER JOIN vwBOM b ON l.PartNo = b.BOMMaterialName 
AND b.MaterialName NOT LIKE 'MT%%'
AND OrderNo = %s""" % 1234)

Outputs:

SELECT OrderNo as [Order #],
FROM vwLots l LEFT OUTER JOIN vwBOM b ON l.PartNo = b.BOMMaterialName 
AND b.MaterialName NOT LIKE 'MT%'
AND OrderNo = 1234
2 Likes
5

Python is quite clever with this. It only interprets %-signs if you also use % substitution with that string.

In the example shown, it's using ..." % event.source.selectedLabel to insert a parameter into the query. If you change that query into a runPrepQuery, you can pass the arguments in a separate array, and the %-signs will also be interpreted correctly.

2 Likes