Having a simple named query like this:
INSERT INTO test_table (c1, c2, c3)
VALUES (:c1_value, :c2_value, :c3_value)
ON DUPLICATE KEY UPDATE c3 = :c3_value
Running this in the Testing window works as expected, however, when trying to bind this query as the UPDATE query with any of the parameters set to a CLIENT tag, this fails as the client tag value doesn’t seem to change in the query unless the window is closed and opened!
Forgot to mention, binding the client tag to e.g. a numeric text field then the query parameter to the text fields value property instead makes the query work as expected.
Named queries are Gateway scoped.
You will have to use a tag in the gateway scope.
Using a client scope tag does work, just the window that needs to be closed and opened before recognizing the client tag has changed. Also, as mentioned binding the client tag to another property in the window and then bind the named query parameter to this instead solves the issue.
Calling names queries from the client tags should be implemented in 7.9.6:
https://docs.inductiveautomation.com/display/DOC79/New+in+this+Version
I have no experience with 7.9.6 yet though, so I don't know how good it works.
I’ll take a look at OP’s issue later today, but I wanted to clear up some things about named queries.
Named queries exist in all scopes, but they are always executed on the Gateway. Since the original purpose of named queries is to let you avoid letting clients execute random queries, they definitely have to work in client scope. In the Gateway and Client scopes, the query always executes the latest published version of the query – in Designer scope it executes the version that’s in the designer, even if it hasn’t been saved to the gateway yet.
For right now, the only way to have a named query in a regular gateway tag is to use an expression tag with runscript. (We do plan to add this in the future, but there’s quite a bit of back end work needed to make this happen.) We did add named queries to client tags in 7.9.6.
I haven’t checked to see, but I suspect you might need to make sure a named query is defined as an update query before it can update values in a binding. The regular SQL queries try to guess whether something is an update query or not, and that sometimes causes problems when update masquerade as selects and vice versa, so we made that choice explicit in named queries.
Hello Kathy,
The query is set to be an update query.
Also, the query works fine in all other cases than when using a client tag as parameter
I can’t remember where I found it but, I found runNamedQuery documentation with syntax:
rowsChanged = system.db.runNamedQuery( ‘myProjectName’, ‘namedQueryPath’, params )
Providing the project name allows global scripts or non-project scripts to find the named query and execute it.
It may not be a best practice, but it worked for me in testing.
I haven’t used it in a production setting yet.
@Claus_Nielsen, I think we’re going to need to get more info from you to find out if there’s a bug here and what the nature of it really is. Can you please contact support with this? Thanks!