Query problems in 7.9.6

Ignition
1

I’m having trouble with a SQL query that doesn’t work in the Ignition database query browser, but works directly in the database. I’m using Postgresql, 9.6.13. The query is:

with linedates as (
	select generate_series('2021-04-05'::date, '2021-04-09'::date, '1 day'::interval) as linedate
	)

select linedates.linedate, count(evl.id)
FROM linedates
left join employee_vacation_log evl on vacationdate = linedates.linedate
group by 1
order by linedates.linedate

The error that I’m getting from the database query browser is:

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "with linedates as (
	select generate_series('2021-04-05'::date, '2021-04-09'::date, '1 day'::interval) as linedate
	)

select linedates.linedate, count(evl.id)
FROM linedates
left join employee_vacation_log evl on vacationdate = linedates.linedate
group by 1
order by linedates.linedate": A result was returned when none was expected.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runUpdateQuery(GatewayInterface.java:576)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runUpdateQuery(GatewayInterface.java:560)
	at com.inductiveautomation.ignition.designer.querybrowser.QueryBrowser$6.run(QueryBrowser.java:501)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: A result was returned when none was expected.
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:307)
	at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
	at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)
	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:857)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunUpdateQuery.runUpdateQuery(RunUpdateQuery.java:35)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:37)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:77)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:405)
	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(Thread.java:748)

Ignition v7.9.6 (b2018012914)
Java: Oracle Corporation 1.8.0_201

Running the query in pgAdmin gives the query results as expected.

image
image1269×614 45.8 KB

Is this a bug? Or some other limit that I’m running into? JBDC driver limitation?

2

Huh, just occurred to me to try it in the script console, and it works there.

query = "with linedates as (select generate_series('2021-04-05'::date, '2021-04-09'::date, '1 day'::interval) as linedate) select linedates.linedate, count(evl.id) FROM linedates left join employee_vacation_log evl on vacationdate = linedates.linedate group by 1 order by linedates.linedate"
args = []
db = "postgres"
system.db.runPrepQuery(query, args, db)

Result:

Jython 2.5.3 (v2.5.3:3d2dbae23c52+, Nov 17 2012, 11:51:23) 
[Java HotSpot(TM) 64-Bit Server VM (Oracle Corporation)] on java1.8.0_201

>>> 
<PyDataset rows:5 cols:2>
>>>
3

I think this is because the query browser is dumb and since your query doesn’t literally begin with “SELECT” it isn’t expecting results.

4

Ok. It seems to be working where it counts (scripting and property bindings), so no big deal :slight_smile: