Creating a duplicate dataset with different column datatypes

Ignition
1

Hello all,

I have been scratching my head at this for quite a bit this morning, and even though it's a lower priority project, it just bothers me that it feels like I'm that close, and can't figure it out.

Long story short, I have a table that pulls data from a SQL query that executes a stored procedure. This table has all of the data I need, but unfortunately, the stored procedure casts all the values as varchar data type. I am trying to allow a chart to plot values from this table, but I need to get those data into a numeric form before the chart can handle it properly.

I'd prefer not to mess with the database or stored procedures if possible. I am wondering if there is any way within the Designer to create that dataset and then somehow modify it to change the column data types to numeric ones. My first thought was to do something like creating the base dataset as a custom property of the root container, and then query that using CAST AS statements to convert the datatypes, but I'm not sure that's possible.

Any workarounds? I have seem some similar threads that show some python codes, but I'm not sure how to set that up to get it to output to a table component.

2

I don't believe that the designer natively has any "CAST AS" statements for a dataset.

Pretty sure the only way to accomplish this is to use the Dataset Builder looping through the data casting each value to the correct type.

1 Like
3

Thanks - this Dataset Builder is something I've seen mentioned in other threads.

Is there documentation for this somewhere? I've seen quite a few blocks of python code, but I'm not sure where to even begin with how to incorporate this on my window. My first thought is that it is supposed to be set as a propertyChange script and respectively update the visible table with the filtered data.

4

system.dataset.map from Ignition Extensions could do this pretty easily as a single operation.

It's also definitely possible using native dataset operations; you'll probably want to construct a typical list-of-lists PyDataset approach.

5

No worries, I can help with that.

Create a custom property somewhere on the display, I would probably add it to the component. Bind this property to the data coming from the dataset.

Then in the property change event for that property place a script similar to this to update the data property on the component itself.

Documentation DatasetBuilder

from com.inductiveautomation.ignition.common import DatasetBuilder
from java.lang import String,Integer

rawDs = system.dataset.toPyDataSet(event.source.rawData)

headers = rawDs.columnHeaders
colTypes = [String.Class,Integer.Class]

builder = DatasetBuilder.newBuilder()
builder.colNames(headers)
builder.colTypes(colTypes)

for row in ds:
    builder.addRow([colType(row[colName]) for colName,colType in zip(headers,colTypes)])

event.source.data = builder.build()
1 Like
6

I had to do something similar a while back. This might be useful to you.

7

This looks promising but I'm still running into some issues. Line 1 returns an error. Do I need an extension to import DatasetBuilder?

Traceback (most recent call last):
  File "<event:propertyChange>", line 1, in <module>
ImportError: cannot import name DatasetBuilder


Ignition v8.1.25 (b2023021409)
Java: Azul Systems, Inc. 11.0.17

Also, in the for loop, should "ds" instead be "rawDs"?

8

Okay, I'm getting a little closer. @bmeyers, thanks for sharing that thread, as that helped me diagnose some stuff. Here's the current code:

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

rawDs = system.dataset.toPyDataSet(event.source.DefaultDataset)

headers = rawDs.columnNames
colTypes = [Integer.Class,String.Class,String.Class,Integer.Class,String.Class,Datetime.Class,String.Class,String.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class]

builder = DatasetBuilder.newBuilder()
builder.colNames(headers)
builder.colTypes(colTypes)

for row in ds:
    builder.addRow([colType(row[colName]) for colName,colType in zip(headers,colTypes)])

event.source.data = builder.build()

I still get an error on line 7, saying that Integer doesn't have an attribute "Class". Any advice?

I figure I'll also have to import the relevant java.langs for float, and datetime, but one thing at a time.

9

Lowercase c; Integer.class, would be the Java syntax, but within Jython you just provide the full static name: [Integer, String, String, Integer], I'm pretty sure.

1 Like
10

You are correct.

I was able to get the code to work with that edit, along with a couple of others.

Posting full code below for others who may run into the same issue.

I replaced Datetime (does not exist) with String for the time being, and must now diagnose another issue with where this data goes down the line. But the code successfully transforms the dataset to have the correct data types.

from com.inductiveautomation.ignition.common.util import DatasetBuilder
from java.lang import String,Integer,Float

rawDs = system.dataset.toPyDataSet(event.source.DefaultDataset)

headers = rawDs.columnNames
colTypes = [Integer,String,String,Integer,String,String,String,String,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float]

builder = DatasetBuilder.newBuilder()
builder.colNames(headers)
builder.colTypes(colTypes)

for row in rawDs:
    builder.addRow([colType(row[colName]) for colName,colType in zip(headers,colTypes)])

event.source.data = builder.build()

This is running as a propertyChange script on the component, with DefaultDataset being a custom property with the dataset that needs to be transformed, and Data being the transformed dataset.

2 Likes
11

A couple brief notes that may help you down the road:

  1. You can use Python's operator overloading to make constructing the repetitive list a bit nicer/easier to maintain:
colTypes = [Integer, String, String, Integer] + ([String] * 4) + ([Float] * 30)
  1. You can take advantage of the 'fluent' API on DatasetBuilder to avoid some repetition:
builder = DatasetBuilder.newBuilder().colNames(headers).colTypes(colTypes)
  1. Accessing columns by name is "expensive" compared to accessing by column index, so you'll get a not-insignificant performance boost using enumerate over zip:
for row in rawDs:
    builder.addRow([colType(row[colIndex]) for colIndex, colType in enumerate(colTypes)])
12

You might find it easier to put this expression binding on your data property, after installing my latest Simulation Aids module (assuming that last bug is fixed):

unionAll(
	forEach(
		columnsOf({Root container.path.to.custom.prop}),
		asList(
			it()[0],
			if(
				idx() < 8,
				asList('I', 'str',  'str', 'I', 'str', 'str', 'str', 'str')[idx()],
				'F'
			)
		)
	),
	{Root container.path.to.custom.prop}
)

:grin:

{ The unionAll function is really just a glorified wrapper around the DatasetBuilder class. }

1 Like
13

This would just be java.lang.Date.

14

java.util.Date

2 Likes
15

By that do you mean the following?

from java.util import Date

...

colTypes = [...,Date,...]

If so, I might just be running into a problem with the code interpreting the string as a datetime, as I get an error. To be fair, whatever the stored procedure is doing, it spits out a string formatted as hh:mm:ss, so I don't blame it for not being able to get to a proper datetime from there.

16

Wow, I would have someone take a very close look at that stored procedure. I don't know your data, but it would seem to me that loosing the context of the day that the time occurred on is less than desirable. This also begs the question of how the "Date" is actually being stored in the Database.

That being said, you should be able to use system.date.parse() to get a valid java.util.Date object.

system.date.parse('dateString','hh:mm:ss')

This will produce a date set to Jan 1 1970 with the time as supplied.

If you care about the date and you want it to be "today's" date then you could do something like:

hour,mins,sec = [int(part) for part in'dateString'.split(':')]
system.date.setTime(system.date.now(),hour,mins,sec)
17

And that's a fair point. I am meeting with someone later in the week to take a look, since SQL is unfamiliar to me and I have a few other related questions.

Regarding the dates, I know that the datetime is stored on the database in accessible manner. I think the bigger issue here is me using a stored procedure for an unintended purpose. I believe the stored procedure I am referencing is intended for preparing this specific set of data for printed reports - which I believe is why everything ends up as a varchar. It just so happens that this specific set of data is exactly what I needed for an ignition project, and I figured I could use the existing stored procedure instead of reinventing the wheel.

Ultimately, I need to get a little more familiar with SQL so in the future I can investigate these kinds of issues without as much fear of walls of code.