Its my first time using the reporting module and i've been getting pretty stuck on what is the first step sadly.
I have a pretty straight forward table set up in an SQL database called "plantdata"
I can be queried just fine if i do an SQL query on a standard table data binding by a simple:
SELECT * FROM plantdata
However when i try and create a new "Data Source" in my report Data tab, i do the same SELECT ALL query and when i go the the Design tab i get a generic error:
I have no idea what that means, but its rather shocking to me that it cant do a simple select all query.
I've noticed everything in reports needs to be linked to a Datasource KEY, but there doesn't seem to be a way to set up KEYS for Basic SQL queries..? I just want each column name to be a KEY.
Checked the console in gateway and getting the following error related to it:
[quote]
Time Logger Message
WARN 11:21:54 AM Data [projectName=ReportTest,reportPath=MyReport] Simple Query could not be completed
java.sql.SQLException: Unknown system variable ‘OPTION’
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3178)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1181)
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.reporting.gateway.data.queries.SimpleSqlQuerySource.executeQuery(SimpleSqlQuerySource.java:83)
at com.inductiveautomation.reporting.gateway.data.queries.SimpleSqlQuerySource.query(SimpleSqlQuerySource.java:54)
at com.inductiveautomation.reporting.gateway.data.queries.SimpleSqlQuerySource.query(SimpleSqlQuerySource.java:30)
at com.inductiveautomation.reporting.gateway.data.QueryReportDataSource.gatherData(QueryReportDataSource.java:53)
at com.inductiveautomation.reporting.gateway.ReportingGatewayHook.getReportData(ReportingGatewayHook.java:283)
at com.inductiveautomation.reporting.gateway.ReportingGatewayHook$RPC.getReportData(ReportingGatewayHook.java:407)
at sun.reflect.GeneratedMethodAccessor63.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.ModuleInvoke.invoke(ModuleInvoke.java:123)
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: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(Unknown Source)[/quote]
I hadn’t seen this error before, but a quick Google search says that it’s coming from using an outdated version of the JDBC driver for your database. Let me know if updating the driver fixes the issue.
When you get the driver working, a query like SELECT * FROM <mytable> will definitely work in reporting. The keys will be made automatically from the data returned by your query. However, if you have column names that are the same as some of the built in keys for reporting (such as “COUNT”, “MAX”, etc) you may want to rename them in your query just to have more consistent results. (ie SELECT count AS mytable_count FROM <mytable>)
Hi Kathy,
Yes updating the JDBC driver to 5.1.40 seems to have worked. Thanks
Now i just gotta figure out why timestamp filtering works in a normal table query but not a Reporting datasource query… 
The above timestamp filter issue was solved by using an “SQL Query” instead of the “Basic SQL query” data source in the reporting module.