I have the script shown below that tries to run an SQL query against an MS SQL database.
The query runs fine in Management Studio.
The code fails every time with the error shown below.
The problem line is:
insert into @timeRanges values(@baseDate, @baseDate + @interval)If I get rid of the ‘Insert’ statement the rest of the query works fine.
I suspect it’s something to do with having an ‘Insert’ statement in the ‘runQuery’ function.
Even though one of the error lines says "The statement did not return a result set.’ It actually does!
Any help appreciated.
Here is the script exert:
query = """declare @baseDate datetime, @interval datetime
select @baseDate = '%s', @interval = '%s'
declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
declare @i int
set @i = 1
while @i <= %d
begin
insert into @timeRanges values(@baseDate, @baseDate + @interval)
set @baseDate = @baseDate + @interval
set @i = @i + 1
end
SELECT
tr.beginIntervalInclusive,
tr.endIntervalExclusive,
COUNT(*) AS [count]
FROM %s join @timeRanges as tr
on %s.[timestamp] >= tr.beginIntervalInclusive
and %s.[timestamp] < tr.endIntervalExclusive
GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive
ORDER BY tr.beginIntervalInclusive""" % (sDateStart, sInterval, int(numOfBuckets), table, table, table)
groupedTotals = system.db.runQuery(query, dataDB)And here is the error I get when run in the Script Console:
Java Traceback:
at org.python.core.Py.JavaError(Py.java:495)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:335)
at sun.reflect.GeneratedMethodAccessor350.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:427)
at org.python.core.PyObject.__call__(PyObject.java:404)
at org.python.core.PyObject.__call__(PyObject.java:408)
at org.python.pycode._pyx477.GetData$3(<module:shared.CheckWeigher.CW_Rates>:144)
at org.python.pycode._pyx477.call_function(<module:shared.CheckWeigher.CW_Rates>)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyBaseCode.call(PyBaseCode.java:166)
at org.python.core.PyFunction.__call__(PyFunction.java:338)
at org.python.core.PyMethod.__call__(PyMethod.java:139)
at org.python.pycode._pyx475.f$0(<buffer>:5)
at org.python.pycode._pyx475.call_function(<buffer>)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at org.python.core.Py.exec(Py.java:1319)
at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:215)
at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:89)
at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:70)
at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:417)
at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:405)
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: java.lang.Exception: Error executing system.db.runQuery(declare @baseDate datetime, @interval datetime
select @baseDate = '2016-04-24 00:00:00:000', @interval = '1900-01-01 00:15:00:000'
declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
declare @i int
set @i = 1
while @i <= 96
begin
insert into @timeRanges values(@baseDate, @baseDate + @interval)
set @baseDate = @baseDate + @interval
set @i = @i + 1
end
SELECT
tr.beginIntervalInclusive,
tr.endIntervalExclusive,
COUNT(*) AS [count]
FROM P70330_bags join @timeRanges as tr
on P70330_bags.[timestamp] >= tr.beginIntervalInclusive
and P70330_bags.[timestamp] < tr.endIntervalExclusive
GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive
ORDER BY tr.beginIntervalInclusive, KEN_Packing_REP, )
... 32 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: The statement did not return a result set.
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:327)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:301)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:258)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:803)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:767)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runQuery(ClientDBUtilities.java:185)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:331)
... 30 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:794)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:685)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:620)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeQuery(DelegatingStatement.java:63)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeQuery(SRConnectionWrapper.java:711)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:98)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:76)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)
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:816)
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:1156)
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:1088)
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:119)
at org.eclipse.jetty.server.Server.handle(Server.java:517)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:306)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:242)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:245)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:75)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:213)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:147)
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)
Traceback (most recent call last):
File "<buffer>", line 5, in <module>
File "<module:shared.CheckWeigher.CW_Rates>", line 142, in GetData
select @baseDate = '2016-04-24 00:00:00:000', @interval = '1900-01-01 00:15:00:000'
declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
declare @i int
set @i = 1
while @i <= 96
begin
insert into @timeRanges values(@baseDate, @baseDate + @interval)
set @baseDate = @baseDate + @interval
set @i = @i + 1
end
SELECT
tr.beginIntervalInclusive,
tr.endIntervalExclusive,
COUNT(*) AS [count]
FROM P70330_bags join @timeRanges as tr
on P70330_bags.[timestamp] >= tr.beginIntervalInclusive
and P70330_bags.[timestamp] < tr.endIntervalExclusive
GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive
ORDER BY tr.beginIntervalInclusive, KEN_Packing_REP, )
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:335)
at sun.reflect.GeneratedMethodAccessor350.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(declare @baseDate datetime, @interval datetime
select @baseDate = '2016-04-24 00:00:00:000', @interval = '1900-01-01 00:15:00:000'
declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
declare @i int
set @i = 1
while @i <= 96
begin
insert into @timeRanges values(@baseDate, @baseDate + @interval)
set @baseDate = @baseDate + @interval
set @i = @i + 1
end
SELECT
tr.beginIntervalInclusive,
tr.endIntervalExclusive,
COUNT(*) AS [count]
FROM P70330_bags join @timeRanges as tr
on P70330_bags.[timestamp] >= tr.beginIntervalInclusive
and P70330_bags.[timestamp] < tr.endIntervalExclusive
GROUP BY tr.beginIntervalInclusive, tr.endIntervalExclusive
ORDER BY tr.beginIntervalInclusive, KEN_Packing_REP, )
>>> >>>