The original purpose of Named Queries was to make it possible to pass values into Vision Query bindings in a secure and efficient way, solving the unreliability of parameter stringification (looking at you, java.util.Date), and allowing the huge "legacy DB access" security hole to be closed.
The maintenance advantages of NQs can be matched by multi-line strings for SQL in library scripts, so I tend to discount that item.
However, not all is well. Some things to consider:
-
NQs permit the use of
QueryStringparameters, which is the insecure stringification behavior brought back from the dead. And it passes through without the Legacy DB security control, so you aren't necessarily as safe with NQs as one might think. -
NQs perform value parameter substitution with colon-delimited placeholders instead of the JDBC-compliant question mark placeholders. Which means Ignition itself is responsible for that operation, not any JDBC driver. Where JDBC drivers support SQL scripts, they work completely correctly, with comments and everything, via any of the
system.db.*Prep*()functions. NQs often break with SQL scripts. -
As noted, NQs can only handle variable numbers of parameters via the insecure QueryString mechanism. Prep queries can be constructed with variable numbers of question-mark placeholders, and matching variable-length lists of securely-handled parameters.
You should be comfortable with both approaches, so you can apply the right tool for any particular task.