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:
SQL Tag fallback, How? (Feature request?)
Use an if() function in a dynamic property referencing the SQLTag
Go back to having the query in the dynamic property.
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
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)
[/quote]
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?
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.
