Running a named query with python - help

Ignition
,
1

I am using this code:

value = 5187-1
params = {'SpecNum': value}
results = system.db.runNamedQuery('Spec/Full Spec', params)
return results

to run a named query. For test purposes value is hard coded in, but will be replaced with at variable tag read.
I am getting an error: File input, line 3 - index 2 is out of range

Here is the nameQuery:

SELECT SpecPoint.Descr AS 'Description',
	SpecPoint.ScalarValue as 'Value',
	SpecPointLimit.LowerLimit As 'Minimum',
	SpecPointLimit.UpperLimit as 'Maximum'
--,SpecPointLimit.LowerLimit, SpecPointLimit.UpperLimit, 
--, Spec.PlantID
--,SpecPoint.SpecID, SpecPointLimit.ID, Spec.ID
FROM SpecPoint
  INNER JOIN Spec ON SpecPoint.SpecID = Spec.ID
  INNER JOIN SpecPointLimit ON SpecPoint.ID = SpecPointLimit.SpecPointID
--  INNER JOIN ProcessType ON Spec.ProcessTypeID = ProcessType.ID
  INNER JOIN SpecProduct ON SpecPoint.SpecID = SpecProduct.SpecID
  INNER JOIN Product ON SpecProduct.ProductGUID = Product.GUID--,
--  Reference,
--  SpecNote
  
WHERE  :SpecNum --SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) =  :SpecNum  --Product.Number = :SpecNum
AND Spec.StatusLevelID = 2
--AND SpecPoint.ScalarValue = 100
AND Spec.PlantID = 210

ORDER BY SpecPoint.Descr ASC;

I will remove the commented out parts when I finish the query.
Any ideas on why I have an index error?

2

I would remove the commented out lines now and try it again.

They can cause issues with the JDBC driver.

3

Do you really mean to perform subtraction when assigning to value ? If not, you need quotes.

4

Alright, I have made both of the changes by lrose and pturmel. I looks like it tried to run the query and I get a new error:

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
5

Post your new query in full.

6

I saw the error. My query in full:

SELECT SpecPoint.Descr AS 'Description',
	SpecPoint.ScalarValue as 'Value',
	SpecPointLimit.LowerLimit As 'Minimum',
	SpecPointLimit.UpperLimit as 'Maximum'

FROM SpecPoint
  INNER JOIN Spec ON SpecPoint.SpecID = Spec.ID
  INNER JOIN SpecPointLimit ON SpecPoint.ID = SpecPointLimit.SpecPointID
  INNER JOIN SpecProduct ON SpecPoint.SpecID = SpecProduct.SpecID
  INNER JOIN Product ON SpecProduct.ProductGUID = Product.GUID--,
  
WHERE SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) =  :SpecNum 
AND Spec.StatusLevelID = 2
AND Spec.PlantID = 210

ORDER BY SpecPoint.Descr ASC;

I forgot to add the columns/rows to look for with the parameter. I had just the parameter:

WHERE SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) =  :SpecNum

before i had just

:SpecNum

Thanks for talking me through it,

1 Like
7

Consider passing SpecNbr and RevisionNbr separately, with separate comparisions in the WHERE. Casting and concatenating are not efficient. You probably want to make sure to have indices on some or all of the columns you use in the WHERE clause, or this won't scale.

3 Likes