Vision: Convert dataset column type

Ignition
1

I have a dataset that I’m returning from an SQL query, however I need to post-process it to convert a few of the fields to java.awt.Color object types. They’re incoming as strings e.g. “java.awt.Color[r=25,g=23,b=123]”

Is there a simple way to do this?

What I’m doing at the moment is super clunky:

  1. get the headers from the dataset
  2. get the first row as a list
  3. convert all of the colour columns in the list to a colour object using e.g.: system.gui.color(“java.awt.color([…])”). Now this list contains colour objects rather than colours as strings.
  4. create a dataset from these lists
  5. join the SQL dataset to this new dataset, thereby forcing the SQL dataset to use the new java.awt.Color type
  6. delete the original column…

I tried using system.dataset.clearDataSet() in between steps 3 and 4, but this seemed to not retain the column type.

EDIT:
This logic however doesn’t work sometimes, especially if the first row has some NoneType values in it, I think… I’m pulling my hair out trying to get this working :confused:

2

Use Ignition’s DatasetBuilder. Something like this:

from com.inductiveautomation.ignition.common.util import DatasetBuilder
from java.lang import Integer, String, Object
from java.awt import Color

def forceTypes(sqlDS):
    builder = DatasetBuilder.newBuilder()
    builder.colNames(sqlDS.columnNames)
    originalTypes = list(sqlDS.columnTypes)
    i = #some column index for a color
    originalTypes[i] = Color
    builder.colTypes(originalTypes)
    for r in range(sqlDS.rowCount):
        builder.addRow([sqlDS.getValueAt(r, c) for c in range(sqlDS.columnCount)])
    return builder.build()
2 Likes
3

Thanks Phil, didn’t know this existed!