I am curious about the best practices when using system.db.runNamedQuery. What are the advantages and disadvantages to using system.db.runNamedQuery instead of using any of the other system functions like system.db.runPrepQuery, runPrepUpdate, runQuery, etc.
There's a couple of advantages:
- Convenience; they are defined in a single place and can be called/referenced from multiple locations.
- Security; clients cannot modify the query in any way, they are simply passing parameters to a pre-defined query instead.
The user manual talks about them in more detail: Named Queries - Ignition User Manual 8.1 - Ignition Documentation
2 Likes
I'll add that the same named queries can be utilized from Perspective and Vision as well. Then it is only a single source to database work.
2 Likes
I am curious as to any situations it might be better to not use a named query? Anyone know of any?
If/when you have a dynamic list of columns and data to access. Sometimes you just don't know which columns you will need to access and construction that at runtime is necessary.
That being said, most of the time named queries fit the bill.
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.
8 Likes
One use case I had that was better for me to use prepQuery vs a namedQuery was when I had gateway that was attached to multiple databases with the same schemas and I needed to query from different ones.
With Named Queries, I would have had to make the same query 4 times, each with a different database connection set in the Authoring tab of the named query, but the SQL being the same.
With Prepared Queries, I wrote the query once in a library script function and modified =the database connection string as needed.
Having said that, I do aim to use named queries first and foremost now because it is more secure for vision and while it's true you can replicate the single source of truth with library functions, I like that the Named Query editor does do SQL keyword highlighting - it makes reading it the SQL itself a bit easier than when it's all pink inside a triple quote - but this is purely preferential.
As Phil said, right tool for the right job.
Pssst! NQs can parameterize the DB connection....
How would you do it ? It doesn't mention it anywhere in the docs for system.db.runNamedQuery - Ignition User Manual 8.1 - Ignition Documentation about changing the db connection when you call it. I had tried to with db=, database=, or putting the connection name at the end, but it kept using what I set in authoring so I gave up and used to prepared queries.
I believe you, I just wish I knew this before and it was in the docs.
I see it now. You can set the database connection to Parameter. Somehow I missed that. Thanks.