Query binding giving "Syntax Error on Token: 'End of Expression'" error, but works when testing the Named Query

Ignition
1

image
image1306×928 84.1 KB

For easier reading, here’s the query:

DECLARE @downtimeDivisor AS INT = 1

IF :downtimeFormat = 'hour' OR :downtimeFormat = 'hours'
	SET @downtimeDivisor = 3600;
ELSE IF :downtimeFormat = 'minute' OR :downtimeFormat = 'minutes'
	SET @downtimeDivisor = 60;

SELECT TOP(:limit)
	stops.first_fault 	AS fault,
	COUNT(*) 			AS count,
	(SUM(stops.stop_duration) / @downtimeDivisor) AS downtime
FROM stop_history stops
WHERE 	stops.first_fault_time BETWEEN :startDate AND :endDate
		AND stops.first_fault NOT IN {hiddenFaults}
GROUP BY stops.first_fault
ORDER BY count DESC

Shown above is a screenshot of my Query Binding setup. The Error I’m seeing is “Syntax Error on Token: ‘End of Expression’ (Line 0, Character 0)” and I’m completely stumped as to what’s causing it.

Queries with these same startDate, endDate, limit and hiddenFaults parameters are being used elsewhere with no problem. The main difference here between all my other queries is the use of DECLARE for local variables. Could that be causing a problem?

The more bizarre thing is that this query works just fine in the Named Queries query testing area, with manually provided parameters.

2

I’ve been making some changes and the error is different now. Now it’s “Syntax Error on Token: IDENTIFIER (Line 1, Character 43)”

If that’s to be believed, it’s at the end of the first “IF” statement on my updated query, here:

DECLARE @downtimeDivisor AS INT = 1

IF :downtimeFormat = 'hours'
	SET @downtimeDivisor = 3600;
ELSE IF :downtimeFormat = 'minutes'
	SET @downtimeDivisor = 60;

SELECT TOP(:limit)
	stops.first_fault 	AS fault,
	COUNT(*) 			AS count,
	(SUM(stops.stop_duration) / @downtimeDivisor) AS downtime
FROM stop_history stops
WHERE 	stops.first_fault_time BETWEEN :startDate AND :endDate
		AND stops.first_fault NOT IN {hiddenFaults}
GROUP BY stops.first_fault
ORDER BY count DESC

Again, this error is only occurring in the binding, the Named Query runs just fine when I test it in the Testing tab.

3

It might not be a problem with the query, but rather the parameters you’re providing. trying removing the values from the “Value” column, then re-applying them, and taking extra care to hit Enter after you are done supplying each value.

If you are using the GUI to select property and/or tag values from the tree, the value isn’t actually supplied to the query until you then also hit Enter.

1 Like
4

Well, that fixed it.

I had tried completely removing and recreating the binding, but I guess I had made the same mistake twice when doing so.

Thanks!

1 Like
5

String type parameters with string constant parameters will not work with surrounding quotes when testing the named query, but will ONLY work with surrounding quotes when used in a query binding. If the string constant parameter is provided without surrounding quotes in the query binding, it will result in Error_Configuration(“RuntimeException: Syntax Error on Token: ‘End of Expression’ (Line 0, Character 0)”)

11 Likes
6

Thanks @james.vosler This was driving me crazy. Is there a reason for this or just an issue?

7

I presume they're based on separate libraries under the hood with different assumptions. I'd prefer them to be consistent, or barring that to have a useful error message. I'll defer to @cmallonee for any real answers here.

8

In the testing panel in the named query editor, you're passing literal strings that will be placed directly into the query - so "some string" is getting to your DB as a string literal including the quotes.

In a named query binding in Perspective, you're authoring an expression that will be evaluated and substituted into your query - expressions don't allow bare quotes because it's a pseudo programming language, and it relies on the quotes to identify string literals.

2 Likes