I have two named queries
Last30DaysDowntime (contains a string parameter named plant) [see attached file named Last30DaysDowntime]
Last30DaysNoParam (same query as Last30DaysDowntime but with no parameter) [see attached file named Last30DaysNoParam]
Both named queries when testes run successfully.
I have a table with a onRowDoubleClick action script that executes the named queries [Last30DaysDowntime] or [Last30DaysNoParam] on a TimeSeriesChart. The double click gets a location/plant from the table, which is used as the parameter in named query [Last30DaysDowntime].
The onRowDoubleClick action script produces results successfully in object TimeSeriesChart if I run the named query with no parameters [Last30DaysNoParam]
location = event.value["Location"]
params = {"plant":location}
self.getSibling("TimeSeriesChart").props.series[0].data = system.db.runNamedQuery("Last30DaysNoParam", {})
If I run the named query that has the parameter [Last30DaysDowntime] I get an error. See attached file named Results_Last30DaysDowntime.
location = event.value["Location"]
params = {"plant":location}
self.getSibling("TextField").props.text = params
self.getSibling("TimeSeriesChart").props.series[0].data = system.db.runNamedQuery("Last30DaysDowntime",params)
If I do a print (system.perspective.print(location)), the location parameter is printed successfully.
If I setup a textfield and write the parameter to the textfield (see the following code), it shows the {“plant”;“CEW”} in the text field (which can be seen in several of the screenshots):
location = event.value["Location"]
params = {"plant":location}
self.getSibling("TextField").props.text = params
Question: What is causing the named query with the parameter to error?
This error is telling you something very specific. Check the details for the full stacktrace, and post the exact code you’re running in this event.
This is the onRowDoubleClick script code:
def runAction(self, event):
"""
Fired when a row in the table is double clicked.
Arguments:
self: A reference to the component that is invoking this function.
event: An object with the following attributes:
row (int): The unique row index as it is represented in the source
data. Also known as the row ID.
rowIndex (int): The row index as it is represented in the current
visible data.
value (dict): The row's value as a JSON object.
"""
location = event.value["Location"]
params = {"plant":location}
self.getSibling("TextField").props.text = params
self.getSibling("TimeSeriesChart").props.series[0].data = system.db.runNamedQuery("Last30DaysDowntime",params)
As noted in my original post, if I change to running the named query with no parameters, it succeeds. That script code, like the above, is posted in my original post.
The following is the sql code in my named query having the parameter, which runs successfully when manually tested:
DECLARE
@Date datetime = GETDATE(),
@DateKeyBegin int,
@DateKeyEnd int;
SELECT @DateKeyEnd = CONVERT(varchar(10), @Date, 112) --Convert datetime to YYYYMMDD
SET @Date = DATEADD(dd,-30,@Date);
SELECT @DateKeyBegin = CONVERT(varchar(10), @Date, 112) --Convert datetime to YYYYMMDD
SELECT
DateLocalizedString AS [Day],
SUM(DowntimeMinutes) / 1440.0 AS Downtime
FROM dbo.DTView
WHERE DateKey >= @DateKeyBegin AND DateKey < @DateKeyEnd
AND Plant = :Plant
AND Widget LIKE '%anchor%'
GROUP BY
DateLocalizedString
ORDER BY
CAST(DateLocalizedString AS Date);
The results, when tested, are seen in a screenshot in my original post.
I had spaces in my Python code, which caused the error. That has been fixed.
Now when I run the named query with parameters I do not get results in TimeSeriesChart when the RunNamedQuery is executed. I get a warning which can be seen in the attached file.
