[BUG-14268] Comment in query causes "index is out of range" error

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 :confused:

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.

1 Like

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.