Well cant see what I might be doing wrong here but the query below runs just fine in SSMS but wont run in the data bindings for a data table in ignition. Am I missing something here? Or can ignition not process a multi step query like this?
DECLARE @DistroTemp TABLE(
[HL1Amps] INT)
INSERT INTO @DistroTemp
SELECT [HL1Amps]
FROM [IGN_Dragline].[dbo].[TG_2570_PeakPower]
DECLARE @DistroCountTemp TABLE(
[count] INT,
[range] VARCHAR(25))
DECLARE @step INT
DECLARE @min INT
DECLARE @max INT
DECLARE @current INT
SET @step = 100
SELECT @max = Isnull(Max([HL1Amps]), 0)
FROM @DistroTemp
SET @min = @max - 1000
SET @current = @min
WHILE @current <= @max
BEGIN
INSERT INTO @DistroCountTemp
SELECT
Count(*),
Cast(@current AS VARCHAR(255)) + ‘-’ + Cast(@current + @step - 1 AS VARCHAR(255))
FROM @DistroTemp
WHERE [HL1Amps] BETWEEN @current AND @current + @step - 1
SET @current = @current + @step
END
SELECT *
FROM @DistroCountTemp
1 Like
Do you get any errors when you try to run this binding? You might have better luck creating a stored procedure and calling that from within Ignition.
Also, what version of Ignition are we dealing with here?
1 Like
Here is the error it throws up.
On: RackLayout.Root Container.Table.data
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:316)
at java.lang.Thread.run(Unknown Source)
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:313)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:244)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:712)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:676)
at com.inductiveautomation.factorypmi.application.gateway.SQLQuery.execute(SQLQuery.java:48)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:690)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.access$500(QueryManager.java:663)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:314)
… 1 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:655)
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:398)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(null)
Ignition v7.5.6 (b1317)
Java: Oracle Corporation 1.7.0_15
Kind of figured I might need to put it over in a SP but was just curious as to why it wouldn’t run in ignition query.
1 Like
Well it could be one of a couple different things:
1)The jdbc driver may not allow you to run multi-statement queries. You could test this by running a simple multi-statement query where you simply declare a variable and then select 1. If you get the same error then this is likely the problem.
2)Sometimes (seen occasionally when running stored procedures) the database is returning multiple results. The first being the number of rows that were updated by the query and the second being the resultset. Adding the line “SET NOCOUNT ON” at the top of the query usually resolves this. You can then set it off again at the end of the query.
If you do end up putting this in a stored procedure just keep in mind that you may still need to include the SET NOCOUNT ON at the top of the stored procedure for this to work.
1 Like
Thanks Dave ill give that a go tomorrow
1 Like