Hi, i have this project where we log different measurement values, and the customer would like to see fancy graphs over some options they select on the page.
I have tried to tie a SQL-String where it returns a list, and tried to connect a power table data results (PT shows the list as expected). But no matter what i have tried, nothing shows up in for example easy chart…
What i first would like to do, is group all sumOK and sumNOK according to date (sum up all records for the same day) and then display all results as a line over time, where ok and nok is 2 different lines (or which ever type of graph they select on the project screen).
I have checked the “both help pages” about the charts (support.inductive… and docs.inductive…) and neither shows a way to list the graphs (imo).
Hi, i worked out an SQL statement that worked for me, with the bar chart. But only worked once.
I added this SQL:
if OBJECT_ID('tempdb..#st') IS NOT NULL DROP TABLE #st
SELECT cast(addDate as date) as dateDay, SUM(CAST(sumOk AS INT)) AS ok, sum(CAST(sumNok AS int)) as nok INTO #st FROM Statistik
WHERE addDate IS NOT NULL
GROUP BY addDate
ORDER BY dateDay ASC
SELECT dateday, SUM(ok) as stok, SUM(nok) as stnok FROM #st
GROUP BY dateDay
ORDER BY dateDay ASC
Even if i runt it fast and as many times i want, in SQL management studio, it displays as expected
The bar chart draws all bars from all rows, but the red/pink layover, giving this error
[code]java.lang.Exception: Error running query:
SQLQuery(query=if OBJECT_ID(‘tempdb…#st’) IS NOT NULL DROP TABLE #st
SELECT cast(addDate as date) as dateDay, SUM(CAST(sumOk AS INT)) AS ok, sum(CAST(sumNok AS int)) as nok INTO #st FROM Statistik
WHERE addDate IS NOT NULL
GROUP BY addDate
ORDER BY dateDay ASC
SELECT dateday, SUM(ok) as stok, SUM(nok) as stnok FROM #st
GROUP BY dateDay
ORDER BY dateDay ASC, database=)@0ms
On: Grafer.Root Container.Bar Chart.data
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:317)
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: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:772)
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: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(Thread.java:745)
I Turned the SQL statement into a Stored Procedure and it now works, wierd that Ignition cant handle such simple sql-statement.
However, the help databases is not helpfull at all.
calling the SP with parameters, doesnt seem to work as the little “help” that could be searched up
[code]Exception: Error running query:
SQLQuery(query=sp_statistik(0,“11”,0,“22”,1,0,10101,0,“2016-12-01”,“2016-12-12”), database=)@0ms
On: Grafer.Root Container.Bar Chart.data
caused by GatewayException: Incorrect syntax near ‘0’.
caused by SQLServerException: Incorrect syntax near ‘0’.
Ignition v7.8.2 (b2016030813)
Java: Oracle Corporation 1.8.0_111
[/code]
Those not with “” around are declared as BIT in the sp, and the rest is VARCHAR(30) or DATE format.