In a module (gateway scope) I try to query the ignition internal database with:
GatewayContext context;
...
PersistenceSession DBSession = context.getPersistenceInterface().getSession();
List<List<Object>> data = null;
try {
data = DBSession.getDBInterface().runQuery(query);
...
I would like to return the result in a Dataset,
what is the best way to have the columns types to create a BasicDataset
runQuery only return Object ???
For BLOB data columns what is the java column type to use ?
/*
public BasicDataset(java.util.List<java.lang.String> columnNames,
java.util.List<java.lang.Class<?>> columnTypes,
java.lang.Object[][] data)
Constructor that takes all of the information needed to create a populated dataset.
Parameters:
columnNames - The column names of the dataset. Must match the length of columnTypes and data.length
columnTypes - The types of each column. Must match the length of columnNames and data.length
data - The raw data. An array of columns of data. (NOT rows.)
*/
Use byte[] for binary columns. Use a DatasetBuilder to iterate through the list of rows and turn it into a dataset.
Yes but how to can I determine Java types from the returned
List<List<Object>>
to feed the colTypes ?
For example:
DatasetBuilder.newBuilder().colTypes(String.class, Color.class, Color.class, Float.class)
Oups… runQuery provide the good Java Types…all java type implements Object…
1 Like
@PGriffith, lll is fine with DatasetBuilder, but have you Idea how to obtain colums names from such of sql query ?
String query = "SELECT * FROM IMAGES"
PersistenceSession DBSession = context.getPersistenceInterface().getSession();
List<List<Object>> jData = null;
jData = DBSession.getDBInterface().runQuery(query);
You can’t, through the runQuery() method. You would need to getDBInterface().getConnection(), and then run your queries yourself, dealing with the ResultSet object directly. Take a look at/decompile com.inductiveautomation.ignition.gateway.localdb.AbstractDBInterface#resultSetToListNatural to see what runQuery is doing ‘under the hood’.
1 Like
Ok I see, thanks a lot !
When I try a SELECT PATH,DATA from IMAGES,
I have the following error, relative to blob columns:
GatewayDBUtils
03Apr2020 23:43:43
Exception thrown while querying database
java.sql.SQLFeatureNotSupportedException: null
at org.sqlite.jdbc4.JDBC4ResultSet.unused(JDBC4ResultSet.java:347)
at org.sqlite.jdbc4.JDBC4ResultSet.getBlob(JDBC4ResultSet.java:388)
at com.inductiveautomation.ignition.gateway.localdb.AbstractDBInterface.resultSetToListNatural(AbstractDBInterface.java:47)
at com.inductiveautomation.ignition.gateway.localdb.AbstractDBInterface.runQuery(AbstractDBInterface.java:153)
at com.inductiveautomation.ignition.gateway.localdb.AbstractDBInterface.runQuery(AbstractDBInterface.java:164)
at com.bouyguesenergiesservices.ignition.gateway.utils.GatewayDBUtils.runQueryInternal(GatewayDBUtils.java:41)
at com.bouyguesenergiesservices.ignition.gateway.utils.GatewayDBUtils.runQueryImpl(GatewayDBUtils.java:32)
at com.bouyguesenergiesservices.ignition.common.utils.AbstractDBUtils.runQuery(AbstractDBUtils.java:23)
at com.bouyguesenergiesservices.ignition.gateway.utils.GatewayRPCHandler.runQuery(GatewayRPCHandler.java:26)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.ModuleInvoke.invoke(ModuleInvoke.java:165)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:411)
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:86)
at org.eclipse.jetty.servlet.ServletHolder$NotAsyncServlet.service(ServletHolder.java:1391)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:760)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:590)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1607)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1577)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:500)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
at java.base/java.lang.Thread.run(Unknown Source)
I obtain the same error with:
SELECT PATH,DATA FROM IMAGES WHERE TYPE IS NOT NULL
getBlob() isn’t supported on SQLite’s JDBC4ResultSet class. Use getBinaryStream().
2 Likes