I have connected Ignition to OSI PI Data Archive and Asset Framework Databases using JDBC drivers. All of the queries work perfectly fine within Vision but when attempting to add the queries within the report designer I receive the following error messages below. Any help would be appreciated as I’d really like to use Ignition to report on OSI PI Data.
The gateway indicates this error:
Gateway 13Nov2020 15:10:14 Error serializing response.
When viewing the database connection in the gateway the queries from Vision are shown but the Queries from the Report Designer are not present indicating they weren’t sent from Ignition to the database. It is also interesting that some queries work within the Report designer but others do not. For example changing a working query from Select to Select * will cause this error.
ExecutionException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unable to read response from Gateway.
caused by GatewayException: Unable to read response from Gateway.
caused by org.xml.sax.SAXException: java.io.EOFException
java.io.EOFException
caused by EOFException
Ignition v8.1.0 (b2020110211)
Java: Azul Systems, Inc. 11.0.7
I think you called into support about this and I worked with a rep. From the stack trace I saw them post, the key problem is that the OSI-PI JDBC driver is (sometimes) returning types that aren't Java-serializable, which is required for Ignition to send the values from the gateway back down to clients.
This is very significant - I would try to isolate exactly which columns are causing the issue (I strongly suspect it's particular data type(s) that are the culprit) - if you can isolate it to certain column types, then it's possible you could work around the JDBC driver restriction by manually casting/coercing to different types in your queries.
Thanks for the response Paul,
I was able to modify queries within the Report and cast columns to a specific type which seems to be working so far. Specifically it seems to be datetime columns causing this error. Any idea on why I’m only seeing these type casting errors within the Reporting module and not Vision displays?
All I can guess at is that the Vision module vs the reporting module are going through somewhat different code paths to send values back to the client, and it’s possible somewhere in the Vision code we’re ‘downcasting’ from whatever type the JDBC driver returns into a native Java Date object before serializing to send to the client.
I am experiencing the exact same error with datetime columns. Can I ask you what type you cast your columns to?
Running into this issue. Pulling a timestamp from PI into the reporting module causes this issue. Casting the date as a string resolved it, but then these timestamps are not able to be used in a trending component on the reports.
Here's the full error I get:
Vision.reporting.Components.Viewer - ReportLoader task could not be completed.
java.util.concurrent.ExecutionException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unable to read response from Gateway.
at java.base/java.util.concurrent.FutureTask.report(Unknown Source)
at java.base/java.util.concurrent.FutureTask.get(Unknown Source)
at java.desktop/javax.swing.SwingWorker.get(Unknown Source)
at com.inductiveautomation.reporting.components.ReportViewer$ReportLoader.done(ReportViewer.java:709)
at java.desktop/javax.swing.SwingWorker$5.run(Unknown Source)
at java.desktop/javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.run(Unknown Source)
at java.desktop/sun.swing.AccumulativeRunnable.run(Unknown Source)
at java.desktop/javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.actionPerformed(Unknown Source)
at java.desktop/javax.swing.Timer.fireActionPerformed(Unknown Source)
at java.desktop/javax.swing.Timer$DoPostEvent.run(Unknown Source)
at java.desktop/java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unable to read response from Gateway.
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:487)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:283)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.moduleInvokeSafe(GatewayInterface.java:917)
at com.inductiveautomation.ignition.client.gateway_interface.ModuleRPCFactory$DynamicRPCHandler.invoke(ModuleRPCFactory.java:53)
at com.sun.proxy.$Proxy50.getReportData(Unknown Source)
at com.inductiveautomation.reporting.components.ReportViewer$ReportLoader.doInBackground(ReportViewer.java:677)
at com.inductiveautomation.reporting.components.ReportViewer$ReportLoader.doInBackground(ReportViewer.java:614)
at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.desktop/javax.swing.SwingWorker.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.xml.sax.SAXException: java.io.EOFException
at com.inductiveautomation.ignition.client.gateway_interface.ResponseParser.endElement(ResponseParser.java:159)
at java.xml/com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
at java.xml/com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:485)
... 13 common frames omitted
Caused by: java.io.EOFException: null
at java.base/java.io.ObjectInputStream$PeekInputStream.readFully(Unknown Source)
at java.base/java.io.ObjectInputStream$BlockDataInputStream.readShort(Unknown Source)
at java.base/java.io.ObjectInputStream.readStreamHeader(Unknown Source)
at java.base/java.io.ObjectInputStream.<init>(Unknown Source)
at com.inductiveautomation.ignition.common.Base64.decodeToObjectFragile(Base64.java:983)
at com.inductiveautomation.ignition.common.Base64.decodeToObjectFragile(Base64.java:959)
at com.inductiveautomation.ignition.client.gateway_interface.ResponseParser.endElement(ResponseParser.java:155)
... 24 common frames omitted
There's probably a corresponding error on the gateway at the same time.
Thanks for pointing that out. Looks like it's the not serializable issue you were referencing prior. Casting this column as a string resolves the error but doesn't display accurately in the trending component on the report. Casting as a datetime or similar renders the same error:
java.io.NotSerializableException: com.osisoft.relational.rdsa2.remote.DataDeserializer
at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeArray(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeArray(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject(Unknown Source)
at java.base/java.util.LinkedHashMap.internalWriteEntries(Unknown Source)
at java.base/java.util.HashMap.writeObject(Unknown Source)
at java.base/jdk.internal.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at java.base/java.io.ObjectStreamClass.invokeWriteObject(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
at java.base/java.io.ObjectOutputStream.writeObject(Unknown Source)
at com.inductiveautomation.ignition.common.Base64.encodeObject(Base64.java:496)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.printSerializedResponse(Gateway.java:667)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.printSerializedResponse(Gateway.java:657)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.ModuleInvoke.invoke(ModuleInvoke.java:174)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:434)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1383)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1305)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:563)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:416)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:385)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:272)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:140)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:934)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1078)
Should string datetimes work with the component?
I wouldn't really expect them to; it might be nice, but strings are inherently lossy for datetimes and every tool under the sun has a "preferred" datetime format that they expect to output or parse.
Gotcha. I didn't expect them to either. That leads me to my next question of is there a workaround on file for getting a trending component to work with PI timestamps that cant be serialized or a path to properly serialize them?
The only "problem" here is that the PI JDBC driver is handing back a weird datatype. The proper fix is for the JDBC driver to hand back a 'native' JDBC type, especially when you're doing manual casting, which will go through the rest of Ignition fine.
You might be able to workaround with a custom module that registers a custom deserialization delegate for the class being returned by the JDBC driver.
Thanks. There's a separate SQL instance available, so I've suggested to link it to the PI database and write a stored procedure to hopefully handle pulling and converting the PI timestamp to pass it cleaning to Ignition. Suggestion #2 is ditching the trend component in the report haha I'll update this thread with the results.
You might get by with a script datasource doing all the casting from a scripted query result, so the report machinery never sees the oddball datetime type.
Update: querying PI through a separate sql server and then pulling it in to Ignition resolved the problem.
3 Likes