When creating a named query, having parts of the query commented out can be useful. Especially during debugging and for helping other developers.
When a parameter is referenced within a query (non-commented), then also in a comment, the query fails. Example:
-- SELECT :id as fail
SELECT :id
Where :id is a parameter that is a String. Value does not matter as it fails during parsing. When executing the query on the test screen, the following error is presented:
org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1.
I have been using Postgres for quite some time, and I suspect that the way ignition is pre-parsing the query string is inconsistent with Postgres. Note how the error states it is being passed two parameters, but is only expecting one. I believe Ignition is parsing the commented parameter, when it should be ignored.
To summarize:
Expected Behavior
The following query parses and properly runs in Postgres
-- SELECT :id as fail
SELECT :id
Actual Behavior
Postgres rejects the query due to parameter mismatch
JDBC is not equivalent to your database’s management studio. Comments are part of SQL scripting, where JDBC works on single SQL statements. Some JDBC drivers implement some SQL scripting support, but it is not widespread.
This isn’t something Ignition is imposing, it is just the way JDBC works. Ignition doesn’t pre-parse except for trivial injection of TOP or LIMIT clauses.
Don’t use comments. Don’t use DECLARE or script parameters.
I get Ignition’s JDBC runs in single statement mode. The recommendation to just not use comments really doesn’t work with complex queries, future developers would cry 
. Comments work just fine with the Postgres driver, just the parameters therein causing an issue.
Are you sure about Ignition not doing pre-parsing? AFAIK, JDBC only supports ? and ?? for parameters, and all implementations of named parameters are managed by custom pre-parsing or a higher level JDBC library. Ex. NamedParameterJdbcTemplate from Spring
If there is any way to do a pre-check for it, it would provide for easier debugging. My co-worker spent a while trying to fix this bug and we eventually discovered it was the comment. Generally I would assume the last issue it could possibly be would be a comment.
If this would be better to post to the ideas page, let me know.
Simple string search and substitution. (Well, allowing for quoted strings.} Noting where they are substituted to construct the correct order for the question marks. It is not a SQL-aware parser, so has no recognition of comments.
It doesn't hurt to ask. But don't hold your breath waiting for this.
Perhaps a middle ground solution would be to allow the developer to leave comments on a named query in a separate text box akin to how custom properties on a component let you leave notes regarding what its there for.
1 Like
We’re treating this as a bug internally. Phil’s larger point is correct (we don’t, and won’t attempt to, truly parse your query) but we’re planning to change things so that at least leading -- are properly interpreted.
3 Likes