Module sdk - run internal query - convert result to a dataset

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().

1 Like