Using a parameter in a CASE statement in a named query (JDBC)

Ignition Early Access
1

Hi all,

(Ignition 8.0.13, SQL Server 2017, default Microsoft SQLServer JDBC Driver)

In my quest for mobile performance, I’m attempting to limit query results on a table when a user is on a mobile device while not impacting desktop users. To that end, I’ve tried to make a named query like the following (where :isMobile is a boolean param):

SELECT TOP (CASE :isMobile WHEN 1 THEN 250 ELSE 9223372036854775807 END) -- no performance penalty for huge TOP() 
    <my_columns>
FROM <my_table>

However, this produces an error:

com.microsoft.sqlserver.jdbc.SQLServerException: The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.

Casting :isMobile to bool/int/what have you doesn’t help, nor does storing it in a variable.

This query works in the Database Query Browser when I replace isMobile with a constant, which leads me to believe the named query parameter replacement is what’s not playing nice with the JDBC.

Is there a way around this, or am I just barking up the wrong tree with this solution? Any insight appreciated.

Thanks!

2

You’ll have to use a querystring parameter that actually contains the 1 or 250 you desire: a TOP clause is structure, and the number with it apparently is too. At least in SQL Server. Your problem is not that the parameter is in a CASE statement, but that you are trying to use it in a TOP clause.

1 Like
3

Thanks Phil! Changing isMobile to a QueryString did in fact do it. For future readers’ sakes, here’s what it looks like now (keeping in mind I’ll have to pass in ‘1’ into isMobile now when true):

SELECT TOP (CASE WHEN {isMobile}=1 THEN 250 ELSE 9223372036854775807 END) --
    <my_columns>
FROM <my_table>

(aside: thanks for constantly being a great presence on the forums. I greatly respect your dedication and willingness to spread knowledge and help others! :slight_smile: )

1 Like