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…