Named queries for module developers

Module Development
, ,
#1

The 7.9.4 beta 1 is up as of today. The focus of this release is security, and one of the new features that can affect module developers is named queries.

Named queries are a way to define database queries in advance, so that clients can’t run random queries. An added benefit is that queries become easy to reuse throughout your project.

As a module developer, here’s what you need to know about named queries:

  • Clients may or may not have legacy db access enabled. If they don’t, the client is restricted to only using named queries. Your module needs to handle this gracefully. (If legacy db access is disabled, things like tag history, transaction groups and alarm history will still work, because those queries are predefined.)
  • The API for report datasources has changed slightly. If you have written a custom datasource in your module, you can probably use null for the new argument.
  • If your module has a panel for authoring db queries, similar to the component binding panel in Vision, you will need to also accommodate named queries.
1 Like
Client security for module developers
#2
#3

I’ll have to run some tests, but my time-series DB caching module probably bypasses these restrictions. It doesn’t use any of the client-side query infrastructure.

1 Like
#4

:grinning: Really cool feature. Do you plan to enable to use named query :

  • in Query Tag or in a new type of “Named Query tag” … it means to have named query in the gateway scope !
  • in a script function : invoke a named query from client scope and from gateway scope
#5

7.9.4 has a new scripting function, system.db.runNamedQuery(), which takes the path of the named query resource and a dictionary of your parameters. (Also the project if in gateway scope).

There’s also a named query datasource type for reports, and a named query binding type for Vision components (with a button to allow you to convert your existing SQL binding to a Named Query binding). No named query tag type yet, but that’s a good suggestion.

2 Likes
#6

When I run a namedQuery with mysql, I have the following issue.
The Query is ok in the database query browser !

namedquery.jpg1920×1080 256 KB

Query :

SELECT client.num,
  client.id,
  client.description
FROM client

08:42:22.105 [SwingWorker-pool-2-thread-4] ERROR com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$5$1 - null
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: NamedQueryFunctions.test: Argument class not valid.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:332)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:306)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:263)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:889)
	at com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$5$1.doInBackground(NamedQueryTestingPanel.java:314)
	at com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$5$1.doInBackground(NamedQueryTestingPanel.java:292)
	at javax.swing.SwingWorker$1.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at javax.swing.SwingWorker.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayResponse$GatewayThrowable: com.adbs.syntax.MySQLSyntaxProvider
	at java.net.URLClassLoader.findClass(null)
	at java.lang.ClassLoader.loadClass(null)
	at java.lang.ClassLoader.loadClass(null)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(null)
	at java.io.ObjectInputStream.resolveClass(null)
	at java.io.ObjectInputStream.readNonProxyDesc(null)
	at java.io.ObjectInputStream.readClassDesc(null)
	at java.io.ObjectInputStream.readClass(null)
	at java.io.ObjectInputStream.readObject0(null)
	at java.io.ObjectInputStream.defaultReadFields(null)
	at java.io.ObjectInputStream.readSerialData(null)
	at java.io.ObjectInputStream.readOrdinaryObject(null)
	at java.io.ObjectInputStream.readObject0(null)
	at java.io.ObjectInputStream.readObject(null)
	at com.inductiveautomation.ignition.common.Base64.decodeToObjectFragile(Base64.java:1052)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:152)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:410)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
	at org.eclipse.jetty.server.Server.handle(Server.java:518)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
	at java.lang.Thread.run(null)
#7

It’s getting a class not found exception, but since I can’t see any details of your named query, I can’t do much troubleshooting. Can you post that or email it in to support please? Thanks!

#8

The query is just above the exception logs.

SELECT client.num,
  client.id,
  client.description
FROM client

Buider Synthax : MySQL or universal

The Same Query run fine in the database query browser.

#9

Looks like one of our support reps had discovered the same issue, and we’re targeting a fix for the final 7.9.4 release. I haven’t confirmed it, but apparently it only happens on the first named query you make after upgrading a project?

#10

Tried it on a fresh install and I don’t have any problems.

This is a really cool feature.

I do have some questions:

Where does the query run?
Does it run on the gateway and get pushed to the client? (Similar to if you ran a query on a dataset tag)

What was the reasoning behind making the parameters a non-optional parameter? Seems kinda silly passing in an empty dictionary if you don’t have parameters…

#11

Yes, the query always runs on the gateway, and the result is returned to where it was requested (gateway/designer/client). One nice thing about that is the ability to cache the results – if you have 100 clients, each running a query every second, and you set the results to be cached for one second, you run the query a total of once a second instead of 100 times a second. This can really reduce the load on your database.

What was the reasoning behind making the parameters a non-optional parameter? Seems kinda silly passing in an empty dictionary if you don’t have parameters…

We assumed, rightly or wrongly, that the vast majority of named queries would not be parameter-less queries run from scripts. If that winds up being wrong, we can always make it optional in a future version. :slight_smile:

2 Likes
#12

@KathyApplebaum Thank you!!!

Seriously excited right now. :heart_eyes:

1 Like
#13

Interesting. Is this cache keyed to the parameters supplied?

#14

Is this cache keyed to the parameters supplied?

Yes it is.

#16

Yes we upgrade from 7.9.3 to 7.9.4-beta1.
The third query I’ve created with the same sql is ok.

#17

Question:

If I have a table that typically never changes such as customers, except when a new customer is added, I want to have the cache setting set pretty high so the query isn’t always running or perhaps only runs once

When a new customer is added I want to see it right away.

How should the we the developer handle this scenario? Could there be a way to force a refresh on the named query?

#18

There’s currently no way to force a refresh, except by saving the project, changing the query itself or changing the parameter values.

#19

And to clarify about the parameter values, there’s a cached copy for each set of parameter values that have been used. That means changing a parameter to a new value and then back to an old doesn’t clear the cache for you.

#20

You might want to consider adding a “forget” operation to the named query interface. I had to do this in the Time Series Caching module: forgetCachedSpan(). /-:

1 Like
#21

I’ve added a bug ticket to add a scripting function to clear the cache for a specific named query. No guarantees, but that one is looking good for 7.9.5.

1 Like