SQL Tag Fallback Value

Is there a way to define a fallback value for a client tag?

Previously I had a dynamic property bound to a query with a fallback value. I then used this property in other bound queries. This all worked great since I had the fallback value.

I recently changed these dynamic properties to bind them to SQL tags. The problem is when the tag does not return a value. I know I can use the if() function in a dynamic property but this would not be necessary with a fallback value.

So… unless you have another trick for me, the options as I see it are:

  1. SQL Tag fallback, How? (Feature request?)
  2. Use an if() function in a dynamic property referencing the SQLTag
  3. Go back to having the query in the dynamic property.

I don’t understand, what does this mean:

Tags do not “return a value”. They are not functions. They always have a value (that value might be null, but it is still a value)

If protecting against null is what you’re talking about, I recommend the coalesce expression function

Ok, I’ll bite…

from wikipedia.

Hence, the query (which the tag is bound to) does not return a value but a ‘null’. So by definition a null is an absence of value. But I do get your point, an ascii value of 0 is still a value.

Thanks for the tip on coalesce().
Since I am trying to do this at the SQLTag level, I have this as my query. I will let this run and see what happens.

SELECT COALESCE(OrderNumber,99999) FROM L8CurrentOrder

You are looking at too precise a definiton of null - see the full page of:

and you’ll see that the term null can have many meanings. In our case (which is the FactoryPMI environment, not a SQL database), null is a value. It is a special value that may mean “no valid value” (depending on context - designers can use the special null value to mean whatever they please).

The fallback feature that you’re talking about doesn’t come into play when null is returned, it comes into play when nothing at all is returned. This means a SQL query that returns zero rows. This is different than a query returning one or more rows that may have null values in them.

Thanks for the lesson Carl, ignorance is a hard thing to overcome!

I was basing this post on the definition of null being an absence of value. Looking at this problem again after reading your post it is clear that I do not know what is happening yet. I did not differentiate between a null value and no records returned.

Here is the gateway error that ocurred after switching to an SQLTag. Notice the WHERE clause, OrderNumber = .
Since I do not ‘see’ a value in this message I made an assumption it was null.

[quote]2009-04-28 1:58:35AM ERROR ERROR(301): SQL error for “SELECT RollSequence FROM L8MachineInspection WHERE OrderNumber = AND OrderLineNumber = 1 AND t_stamp = ‘2008-08-12 16:20:15’”: Incorrect syntax near the keyword ‘AND’. [/gateway]
java.sql.SQLException: Incorrect syntax near the keyword ‘AND’.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1258)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at com.inductiveautomation.factorypmi.gateway.servlets.Gateway$2.run(Gateway.java:1940)
at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doDBAction(Gateway.java:2364)
at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.runQuery(Gateway.java:1925)
at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doPost(Gateway.java:404)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at com.inductiveautomation.factorypmi.gateway.ErrorReportValve.invoke(ErrorReportValve.java:95)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)

Let’s look again at the new SQL binding of the tag.

SELECT COALESCE(OrderNumber,99999) FROM L8CurrentOrder

Typically there is only one row in ‘L8CurrentOrder’ and it is written to by another program. I do not know if it just overwrites the data or if it deletes the row and writes a new one. I did verified that I am getting errors when the other program is updating the table.

Based on this new information, will the coalesce function work for both null and zero records?

No, coalesce will only work when there is a null value. If there are no rows in L8CurrentOrder, then the SELECT COALESCE… will also return no rows.

For cases where there are no rows, you can use the fallback value feature. I feel like we’re going in circles now though… :scratch:

So do I.

Please read and answer the first question in the first post.


I was testing the tag’s query and found out that:

if the record ‘has no valid value’ then tag contains a null
if no rows exist then tag contains a null

Therefore I played around with solving this in the query and came up with this.

Declare @count as int; set @count = (select count(*) from L8CurrentOrder); if @count > 0 select COALESCE(OrderNumber,99999) FROM [L8CurrentOrder] else SELECT 99999 AS OrderNumber

I looked and still could not find a fallback value option. It would be a nice feature.

Yeah, you’re right, the SQL binding on tags should definitely have a fallback value. All this time I thought you were using a tag value in a standard SQL query property binding. Sorry about the confusion.

Do you know when the “fallback value” feature will be added for SQL tags?

I just added a SQL query tag with a write back value to an OPC item. What happens to the OPC write if there are no rows returned?