Limit to system.tag.queryTagHistory tag path array?

Ignition
1

Is there a limit to the size of the tag path array that can be passed into the system.tag.queryTagHistory function? I have a query that seems to work fine up to 2096 tags, as soon as I perform a query for 2097 tags I get strange results (all zeroes for my aggregated data). Here is a snip of the code:

queryPaths = tagPaths #List of tags in a list, seems to work fine up until the list is >2096
queryAggregation = 'Count'
queryColumnNames = tagPaths #List of tags in a list
queryReturnSize = 1 #Return 1 aggregated value
	
queryResults = system.tag.queryTagHistory(paths = queryPaths , startDate=startDateIn, endDate=endDateIn, returnSize=queryReturnSize, aggregationMode=queryAggregation, columnNames=queryColumnNames)
2

There's nothing explicitly encoded, but I could see a variety of ways this could break down.

Where are you calling the function from?

3

I was trying to run it with an asynchronously called method (utilizing system.util.invokeAsynchronous) in Perspective.

4

Any errors in the gateway logs about exceeding message size limits? In your async thread, can you confirm the list is as long as you expect it to be and has all the paths you expect? If you set tag-related loggers to TRACE, can you see the paths you expect being output? Maybe the tags.history.query.dataloader logger, specifically.

5

Sounds suspiciously close to the limit on the number of ? parameters in some JDBC drivers....

6

Yeah, it looks like I'm hitting a limit on parameters (2100). Sounds like @pturmel was on the right track. See the top line of the logs below.

For some insight/clarification:

  • Where does this limit come from? For example, is it from the driver that the system.tag.queryTagHistory function calls automatically?
  • Is there a way around it?
  • Any other thoughts?
com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
7

The limit comes from the server. Incorrect parameters list size · Issue #1980 · microsoft/mssql-jdbc · GitHub
The MSSQL JDBC driver translates the error returned by the server into a useful Java exception.

Probably not. Maybe some way to tweak MSSQL, but seems unlikely, from some quick googling.

8

Very interesting, I see that this seems to be tied to MSSQL specifically. So I imagine there are other similar limitations on other databases?

9

Yes

You could batch the tag list and then use system.dataset.appendDataset() to return the complete dataset.

10

IIRC, it's around 4k or so for PostgreSQL.

11

Surprisingly nice alternative for PostgreSQL: