Named Queries vs. queries in script library - Best Practices

[hw79]

Somewhere in the forums I remember reading that it was a better practice to write queries in a script library instead of using named queries. I can't find the topic that it was mentioned on. So, what are everyone's thoughts on this? Arguments for and against using named queries?

[michael.flagler]

I use named queries all the time in place of scripted queries. It basically allows for resistance against SQL injection when using them properly. You can do prepared statements/queries in scripting also, but I like having a nice interface to test queries out and just call them from the script with whatever parameters I need.

[nminchin]

@michael.flagler

resistance against SQL injection when using them properly

So do the 'prep' scripting functions.

You can't create dynamic queries, perhaps obviously, using named queries. Named queries should be used for all static queries and dynamic ones must use scripted ones making sure to validate dynamic parts against an allow list

[lrose]

@hw79

I remember reading that it was a better practice to write queries in a script library instead of using named queries

Generally, named queries are considered to be the better practice however, it is situational.

runprepQuery or runNamedQuery - Best Practice

With the exception of some special cases (ahmmm QueryString parameters). You should almost always default to Named Queries. Performance wise, I doubt there is anything more than negligible gains or losses between the two functions in a scripting environment. They both use the same JDBC driver, so the same queries will basically run exactly the same. Although, I will admit that I don't know if Named Query caching applies to the scripting function or just bindings, so that could make a differe…

[louagejasper]

I always used named queries just until recently. I now use queries in scripts, mainly because my workflow changed so much in the past 2 years.

In our experience, when creating a new table in your database, you always want the same named queries and the same scripts (insert, update, upsert, delete, getById, getByfilter, ...).

So I made a AI-helper, where I just ask: 'Create me a table production_orders with fields 'no', 'line_id' -> FK to lines, 'capacity', ... and give me the jython functions.

This returns the sql command
'CREATE TABLE production_orders .... ' with the fields I want (plus extra fields like id, created_by, created_at, ...

And then it returns the jython code with all the prep queries (insert, update, upsert, delete, getById, getByfilter, ...).
I could have the AI return all the named queries, but just having 1 script makes the workflow 5 times faster, because it's just copy-paste.

We also extended this so it has function converting the query result to classes immediately, so class logic can be used.

[bkarabinchak.psi]

I can't find it at the moment but I recall making a post where I tested named vs prep query vs plain old runUpdateQuery with %s style, and named came out on top, prep second and plain was last. If performance matters named is definitely the best option.

[lrose]

@louagejasper

I could have the AI return all the named queries, but just having 1 script makes the workflow 5 times faster, because it's just copy-paste.

Except for prepQueries (afaik) do not take advantage of caching, which will give Named Queries an edge over this approach. Unless the parameters are always changing, in which case I would expect there to be no real difference. In situations where the parameters are static or don't change often, I would lean on Named Queries, despite the hit to development speed.

[bschroeder]

And in the case where there might be multiple developers in the code with different levels of familiarity, I find that named queries are easier to read and find in the code.

[louagejasper]

@lrose @bschroeder Both valid points!

[Ryan_Deardorff]

My only gripe with Named Queries is one that it seems like they could easily address:

Optional parameters with default values for Named Queries | Ignition Features...