I was trying to comment out a line in a query to try something different, and found that commenting this line causes an "index is out of range" error. Here is the query that causes the error:
--SELECT TOP(:limit*(SELECT COUNT(DISTINCT(stop_history.first_fault_path)) FROM stop_history))
SELECT TOP(:limit)
stops.first_fault_path AS path,
stops.first_fault AS fault,
COUNT(*) AS count
FROM stop_history AS stops
WHERE stops.downtime_start BETWEEN :startDate and :endDate
AND (stops.first_fault NOT IN {hiddenFaults})
GROUP BY stops.first_fault_path,
stops.first_fault
ORDER BY count DESC
No matter where the commented line is in the query, the error occurs.
The exact error I see is:
com.microsoft.sqlserver.jdbc.SQLServerException: The index 4 is out of range.
I tried reproducing this error in SQL Server Management Studio and it wasn't reproducible there.
Removing the commented line fixes the issue.
What version of the SQLServer JDBC Driver do you have installed? You might try upgrading that and seeing if it changes anything.
It appears itās version 4.2, if Iām reading things properly.
Iām trying to upgrade it but each time Iām getting āField āClassnameā must be uniqueā in the Database Drivers & Settings setting for the Microsoft SQLServer JDBC Driver driver.
Iām following the instructions in the docs to upgrade, is there something Iām missing?
Youāre just editing the existing entry and seeing it when saving?
What version of Ignition is this?
Yes.
Iām running one of the nightlies. Sorry if I shouldāve put this in the Ignition 8 category.
Hmm. Let me ask aroundā¦ might be a bug 
1 Like
Are you supplying the same name for the driver at this point as you supplied for the older version?
Yes, Iām just uploading a new JAR file for the upgraded driver.
Can you send us a gateway backup?
I think you need a space after the two hyphens in the comment. so ā-- Selectā¦ā
The file is too large for the forum, so I hosted it on my Google Drive for the time being. (removed)
That didn't fix the issue.
Ok, I downloaded it and removed the link.
1 Like
Alrightā¦ your internal DB has duplicate entries for the SQLServer JDBC Driver and for Postgres JDBC Driver.
Thereās a unique constraint on the classname column now (always?) so when you try to save itās failingā¦
Not sure how you got into this state. Do you see multiple entries on the driver page in the gateway?
This is what my drivers page shows:
Iām not sure either. For context, my organization is testing out Ignition and actively trying it out for new projects, and this gateway was set up a few months before I started working here. I backed up that one and created a local copy, and havenāt changed any settings regarding database drivers since I started using it.
Delete one of each of the extra driver entries and try editing again after thatā¦
I deleted all but āMicrosoft SQLServer JDBC Driverā and was able to upgrade with a new .jar.
I tried upgrading the Driver to version 7.2 and the query error issue with the comment persists.
Well that was a lot of work for nothing! I'll just make a bug ticket for this because I really don't know if it should work or whether Ignition or the JDBC Driver is responsible.
Where are you defining and running this query?
Haha, oh well, it happens!
In a Named Query, right now I'm running it via the Testing tab and the error also happens in property bindings that call the query.
I think I found out what causes the bug EXACTLY. It seems to be having a parameter in a comment.
Value type parameters in comments cause the error, QueryString parameters do not. Having a string that looks like a parameter but isnāt declared as one in a comment does not cause the error.
6 Likes
re: seems to be having a parameter in a comment.
Iāve noticed this in NamedQueryās myself. Oracle driver seems to be particularly fussy.
