OSI PI JDBC creating a dataset tag

dis · September 30, 2022, 1:22pm

The PI JDBC driver is all set, configured and working.

I have the following query in the Database Query Browser

SELECT Cast(value as Float32)
FROM [piarchive]..[picomp]
WHERE tag = 'LastHour_OEE'
	AND time BETWEEN '*-12h' AND '*'

That returns the data as expected. A float value
I created a Query tag based on this query and can bring the data back as a floatarry data type or as a dataset[114R x 1C] data type.

I'd like to have the timestamps so I need 114R x 2C

SELECT Cast(value as Float32), Cast(time as DateTime)
FROM [piarchive]..[picomp]
WHERE tag = 'LastHour_OEE'
	AND time BETWEEN '*-12h' AND '*'

I tried revising the query to the following but it won't work in a dataset it errors
also tried Cast(time as Timestamp)

How can I bring the data with multiple columns so that it can be charted with a timestamp?

I verified that I can do the following query to return a dataset with 2 columns

SELECT Cast(value as Float32), Cast(value as Float32)
FROM [piarchive]..[picomp]
WHERE tag = 'LastHour_OEE'
	AND time BETWEEN '*-12h' AND '*'

PGriffith · September 30, 2022, 3:38pm

If I remember right, the PI JDBC driver hands back a "weird" datetime type that Ignition doesn't know how to deal with (and can't, because it's internal to the PI JDBC driver code).

One option might be to coerce to an epoch seconds or milliseconds timestamp in the database, which you should then be able to safely return as a longint type to Ignition. Then whenever you're using the data in the dataset, you can use system.date.fromMillis(). Or you could have a derived tag that runs a project script to map the values in a column from one type to another.

dis · September 30, 2022, 5:14pm

I found this in the PI JDBC documentation:
https://docs.osisoft.com/bundle/pi-sql-client-jdbc/page/data-types.html

Which would lead me to believe it should work?
DateTime TIMESTAMP java.sql.Timestamp

What I find interesting is the Database Query Browser appears to convert it. Here's a query returning the timestamp as different types. It appears to convert correctly here.

PGriffith · September 30, 2022, 5:24pm

I'm pretty confident this is something that has come through in support before. Are there any errors in the gateway logs when you're trying to get this dataset tag working?
It's something in the serialization handshake. I can't find any of discussion on it in our internal chat, though, unfortunately, so I can't speak to whether there are any known workarounds.

pturmel · September 30, 2022, 5:34pm

As a test, consider changing the tag to a memory tag and placing this query in a gateway timer or scheduled script. That will give you the opportunity to add some logging or other error handling for more details.

dis · September 30, 2022, 5:36pm

There are several messages about
"Error serializing response"
"Error serializing message for write to client session"

dis · September 30, 2022, 6:20pm

mis-clicked on the edit button. I'm reading the documentation on how to do this.

dis · October 4, 2022, 2:14pm

Same issue if I do it as a gateway script. It actually causes all tags under the folder to go into error. I have to delete the tag folder to recover.

Error serializing message for write to client session.

pturmel · October 4, 2022, 2:17pm

Show your gateway script. Show the complete error from the wrapper log. Use the pre-formatted text button (</>) so the pasted code and error are properly indented.

dis · October 4, 2022, 2:32pm


query = "SELECT value,  time FROM [piarchive]..[picomp] WHERE tag = 'LINE1.LastHour_OEE'	AND time BETWEEN '*-12h' AND '*'"
system.tag.write("_Test/dbgTag",system.db.runQuery(query,"Historian"))

INFO   | jvm 1    | 2022/10/04 10:28:55 | I [Project                       ] [14:28:55]: Restarting gateway scripts... project=Host
INFO   | jvm 1    | 2022/10/04 10:29:06 | E [c.i.i.g.s.GWSession           ] [14:29:06]: Error serializing message for write to client session. request-origin=192.168.1.1>, session-user=Dis, session-project=Host, session-id=5F8FDF82
INFO   | jvm 1    | 2022/10/04 10:29:06 | java.io.NotSerializableException: com.osisoft.relational.rdsa2.remote.DataDeserializer
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeArray(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeArray(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.util.HashMap.internalWriteEntries(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.util.HashMap.writeObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/jdk.internal.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectStreamClass.invokeWriteObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.io.ObjectOutputStream.writeObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at com.inductiveautomation.ignition.common.Base64.encodeObject(Base64.java:496)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at com.inductiveautomation.ignition.gateway.servlets.GWSession.writeNotifications(GWSession.java:202)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:537)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1450)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1631)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.Server.handle(Server.java:516)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
INFO   | jvm 1    | 2022/10/04 10:29:06 | 	at java.base/java.lang.Thread.run(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | E [c.i.i.g.s.GWSession           ] [14:29:16]: Error serializing message for write to client session. request-origin=192.168.1.1, session-user=Dis, session-project=Host, session-id=5F8FDF82
INFO   | jvm 1    | 2022/10/04 10:29:16 | java.io.NotSerializableException: com.osisoft.relational.rdsa2.remote.DataDeserializer
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeArray(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeArray(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.util.HashMap.internalWriteEntries(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.util.HashMap.writeObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/jdk.internal.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectStreamClass.invokeWriteObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.defaultWriteFields(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeSerialData(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeOrdinaryObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject0(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.io.ObjectOutputStream.writeObject(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at com.inductiveautomation.ignition.common.Base64.encodeObject(Base64.java:496)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at com.inductiveautomation.ignition.gateway.servlets.GWSession.writeNotifications(GWSession.java:202)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:537)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1450)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1631)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.Server.handle(Server.java:516)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
INFO   | jvm 1    | 2022/10/04 10:29:16 | 	at java.base/java.lang.Thread.run(Unknown Source)
INFO   | jvm 1    | 2022/10/04 10:29:23 | I [Project                       ] [14:29:23]: Restarting gateway scripts... project=Host

I also tried putting the query into a view and casting it. Same error

CREATE VIEW [piarchive]..[LINE1_lasthour]
AS
SELECT value, CAST(Time as DateTime) time FROM [piarchive]..[picomp] WHERE tag = 'LINE1.LastHour_OEE'	AND time BETWEEN '*-12h' AND '*'

pturmel · October 4, 2022, 2:58pm

So, the script itself is not generating that error. The error is coming when the tag subsystem tries to send the dataset to the Perspective client's binding. Whatever java type the PI driver is supplying is not java serializable.

In your script, add a logging statement that will report all of the column types in that dataset.

dis · October 4, 2022, 3:13pm

In your script, add a logging statement that will report all of the column types in that dataset.

Looking into how to do that now.

dis · October 5, 2022, 12:24pm

It took some searching but I figured it out. Java and Python are not familiar languages to me.
value is class java.lang.Double
time is class java.util.Date

What I find interesting is that I can print the values to the logger/output. It's when I try to put it into a query tag that I have an issue. I'm thinking I need an intermediate step that converts java.util.Date to a type that the dataset tag expects.

logger = system.util.getLogger("TagLog")
logger.info("Start")
query = "SELECT value, time FROM [piarchive]..[picomp] WHERE tag = 'LINE1.LastHour_OEE'	AND time BETWEEN '*-12h' AND '*'"
myPyDataSet = system.db.runQuery(query,"Historian")
system.tag.write("_Test/dbTag", myPyDataSet)
logger.info("End")

That runs as a gateway script without any issues. However the tag is:

SOLUTION? it's not pretty but it works, had swap time, value around.


query = "SELECT value, time FROM [piarchive]..[picomp] WHERE tag = 'LINE1.LastHour_OEE'	AND time BETWEEN '*-12h' AND '*'"
myPyDataSet = system.db.runQuery(query,"Historian")

data = []
headers = ["time","value"]
for row in range(myPyDataSet.getRowCount()):
					data.append([system.date.parse( myPyDataSet.getValueAt(row, 1)),myPyDataSet.getValueAt(row, 0)])

myData = system.dataset.toDataSet(headers, data)
system.tag.write("_Test/dbTag",myData)

I can put it in a time series chart.

We have 9 lines. Can I call this script every 5 minutes to update all 9 lines? Is there any sort of cleanup I need to do like destroying variables so as not to create memory issues? I assume I could also create a tag type that could run the script?

Matthew.gaitan · October 5, 2022, 4:16pm

Does it not work if you simply change your select statement to select time, value that way you don't need to swap their positions via code?

Do also try to use that in your original query tag and see if it fixes the issue

We use the OSI PI JDBC driver and I've never noticed any issues like this

dis · October 5, 2022, 4:44pm

Yes it should but I still need to convert the time to a format that Ignition understands so 'same difference'. The issues I've seen seem to all revolve around the timestamp format which doesn't work in the tag but works fine in queries.

Matthew.gaitan · October 5, 2022, 5:04pm

What version of the PI JDBC driver are you using? AFAIK only the 2019 version works correctly on Ignition 8

I need to update our test server to verify.

Edit:
Interesting... after updating to the 2019 driver I'm seeing the same Error on the tag as you

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.invoke(GatewayInterface.java:954)
	at com.inductiveautomation.ignition.client.tags.impl.GatewayTagInterface$GatewayProviderProxy.getDiagnosticsAsync(GatewayTagInterface.java:545)
	at com.inductiveautomation.ignition.client.tags.impl.ClientTagManagerImpl.getDiagnosticsAsync(ClientTagManagerImpl.java:410)
	at com.inductiveautomation.ignition.designer.tags.editing.TagDiagnosticsPanel.refreshDisplay(TagDiagnosticsPanel.java:81)
	at com.inductiveautomation.ignition.designer.tags.editing.TagDiagnosticsPanel.setCurrentPath(TagDiagnosticsPanel.java:75)
	at com.inductiveautomation.ignition.designer.tags.editing.dialog.TagEditorDialog.selectedModelsChanged(TagEditorDialog.java:823)
	at com.inductiveautomation.ignition.designer.tags.editing.dialog.NonComplexConfigurationTracker.finishLoading(NonComplexConfigurationTracker.java:54)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(Unknown Source)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenCompleteStage(Unknown Source)
	at java.base/java.util.concurrent.CompletableFuture.whenComplete(Unknown Source)
	at com.inductiveautomation.ignition.designer.tags.editing.dialog.NonComplexConfigurationTracker.load(NonComplexConfigurationTracker.java:41)
	at com.inductiveautomation.ignition.designer.tags.editing.dialog.TagEditorDialog.editTags(TagEditorDialog.java:727)
	at com.inductiveautomation.ignition.designer.tags.editing.dialog.TagEditorDialog.refreshPaths(TagEditorDialog.java:547)
	at com.inductiveautomation.ignition.designer.tags.editing.dialog.TagEditorDialog.doApplyAction(TagEditorDialog.java:363)
	at com.inductiveautomation.ignition.designer.tags.editing.dialog.TagEditorDialog.applyButtonClicked(TagEditorDialog.java:349)
	at com.inductiveautomation.ignition.designer.tags.editing.dialog.TagEditorDialog$3.actionPerformed(TagEditorDialog.java:180)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
	at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.Component.processEvent(Unknown Source)
	at java.desktop/java.awt.Container.processEvent(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(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.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.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: org.xml.sax.SAXException: java.io.EOFException
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)
	... 53 more
Caused by: java.io.EOFException
	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)
	... 64 more

Ignition v8.1.21 (b2022092908)
Java: Azul Systems, Inc. 11.0.15

Another possible workaround is to utilize a linked server via another DB and query through that:

SELECT * FROM
OPENQUERY([LINKED_SERVER_NAME], 'SELECT time, value FROM [piarchive]..[picomp] WHERE tag = ''sinusoid'' and [time] > ''2022-10-03''')

dis · October 5, 2022, 7:23pm

I am using the 2019 version also. Our linked server is PIOLEDB Enterprise driver which points to the Asset Framework Database. I can query the assets for the same values. I'll have to try that but they query is bit more complicated so it's not my preferred method.

Matthew.gaitan · October 5, 2022, 7:31pm

I wholeheartedly agree. It sucks that they don't just work. They worked just fine on previous Ignition versions.

Now I kind of want to test the 2019 driver on an older Ignition version to see if that works or is still borked