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

**URL:** <https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606>\
**Category:** Module Development\
**Tags:** ignition80\
**Created:** [April 1, 2020, 8:03pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606 "2020-04-01T20:03:48Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [April 1, 2020, 8:03pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606/1 "2020-04-01T20:03:49Z")

</div>

In a module (gateway scope) I try to query the ignition internal database with:

```auto
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 ?

```auto
/*
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.)
*/

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [April 1, 2020, 8:07pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606/2 "2020-04-01T20:07:31Z")

</div>

Use `byte[]` for binary columns. Use a DatasetBuilder to iterate through the list of rows and turn it into a dataset.

---

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [April 1, 2020, 8:22pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606/3 "2020-04-01T20:22:38Z")

</div>

Yes but how to can I determine Java types from the returned

```auto
List<List<Object>>

```

to feed the colTypes ?

For example:

```auto
DatasetBuilder.newBuilder().colTypes(String.class, Color.class, Color.class, Float.class)

```

---

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [April 1, 2020, 8:28pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606/4 "2020-04-01T20:28:23Z")

</div>

Oups… runQuery provide the good Java Types…all java type implements Object…

---

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [April 3, 2020, 8:27pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606/5 "2020-04-03T20:27:27Z")

</div>

@paul-griffith, lll is fine with DatasetBuilder, but have you Idea how to obtain colums names from such of sql query ?

```auto
String query = "SELECT * FROM IMAGES"
PersistenceSession DBSession = context.getPersistenceInterface().getSession();
List<List<Object>> jData = null;
jData = DBSession.getDBInterface().runQuery(query); 

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [April 3, 2020, 9:03pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606/6 "2020-04-03T21:03:31Z")

</div>

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’.

---

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [April 3, 2020, 9:49pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606/7 "2020-04-03T21:49:20Z")

</div>

> [@paul-griffith](#):
>
> com.inductiveautomation.ignition.gateway.localdb.AbstractDBInterface#resultSetToListNatural

Ok I see, thanks a lot !  
When I try a **SELECT PATH,DATA from IMAGES** ,

I have the following error, relative to **blob** columns:

```auto
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**

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [April 3, 2020, 9:58pm UTC](https://forum.inductiveautomation.com/t/module-sdk-run-internal-query-convert-result-to-a-dataset/33606/8 "2020-04-03T21:58:43Z")

</div>

`getBlob()` isn’t supported on SQLite’s JDBC4ResultSet class. Use `getBinaryStream()`.