java.sql.Timestamp value can't be added to dataset

Ignition
1

Perspective 8.1.13

We have a gateway script that creates a dataset using system.dataset.toDataSet().
The data is a collection of values from DB queries, including some date/time values.

Recently, after updating to 8.1.13, date/time values from the DB seem to randomly get returned as a java.sql.Timestamp types. When they do, system.dataset.toDataSet() complains that these types can’t be inserted into a dataset.

I added code to convert them to java.util.Date types (using system.date.fromMillis()) … but this seems to be something that changed recently and I’m trying to figure out the cause.

Any ideas? Were there changes to the default JDBC driver shipped with Ignition that might cause this? Something else?

Thanks.
-Shane

2

They were probably always sometimes returning java.sql.Timestamp for whatever reason. What changed is that strict validation on the column types of a Dataset were added in 8.1.13 because unexpected/non-serializable types would cause errors in the tag system on a subsequent restart.

1 Like
3

Please add that one to the list. If you want more precision than milliseconds, like you’d get from event recorders and similar high-speed devices, you need that column type.

1 Like
4

Just wrote up a ticket for it.

1 Like
5

This is the only comment I can find in the 8.1.13 release notes that I think covers what you’re referring to:. “Dataset types that are stored by ignition must match those allowed by the designer. This change was made to prevent serialization errors from unknown Dataset values.”

Is there any place I can find more details? For instance, what are the specific types allowed?

Thanks.
-Shane

6

Well, the tag types allowed by the Designer are listed here: Tag Data Types - Ignition User Manual 8.1 - Ignition Documentation

Right now the whitelist in the code is this:

        public static final Set<Class<?>> STORABLE_TYPES = Set.of(
            Date.class,
            String.class,
            Integer.class,
            Long.class,
            Short.class,
            Float.class,
            Double.class,
            Boolean.class,
            Color.class
        );
1 Like
7

I presume this will choke on Object.class used in scripts to allow different types by row?

8

Please consider applying the column types check to writes to tags, not to Dataset creation.

9

That’s what is happening now, you can do anything you want in scripting still.

1 Like
10

That would explain why it hasn’t blown up on me yet.

11

Except when you call system.dataset.toDataSet() in a script :frowning:

1 Like
12

The whitelist of dataset types is not going to be invoked when you create a dataset. Writing that dataset to a tag is where the operation would fail.

The whitelist is used in exactly two places:

  1. The manual dataset editing dialog, when you add a new column to a dataset.
  2. In the tag system, when a new write happens to a tag:
    image

Post a script where system.dataset.toDataSet is failing due to this type coercion, and I’ll either show you a different bug, unrelated to this whitelist, or show you where you’re writing that dataset to a tag.

14

As usual, you are of course right!

15

Hah, hardly. I just have the benefit of being able to read the code :slight_smile:

16

Not seen this, but trying to check my code for other instances of this issue:

If I have a tag setup with a ‘Query’ value source, and a ‘Dataset’ data type, and the query (for what ever reason) returns a java.sql.Timestamp type … will the tag fault as if I had tried to do a system.tag.write(ds) to the tag with a Timestamp type value in ds?

17

Yes, the error will be thrown whenever a dataset containing types outside the whitelist makes it to the tag system. So the query tag would throw it directly when the query returns, or writing from scripting would throw it.

18

Is there a known workaround for using system.tag.queryHistory in a tag script which writes the result to a dataset tag? The invalid datatype for dataset is breaking this functionality.

We are using that script to query an array of tags on a periodic basis. Those tags are then read from a remote gateway and used to display trend data. This had to be done to reduce the load on the gateway network.

19

I would wait for 8.1.15 where this is fixed (estimated release in the beginning of March).

If that’s not an option, you could use a document tag instead of a dataset, perhaps, but there’s no direct workaround.

20

Well, you can reprocess the dataset to coerce values in Date columns to be real dates.

1 Like
21

My script just broke because I tried to make a dataset column a list data type (user.getRoles).

Easily fixed by converting to a string, but it sure would be nice if an error were thrown when trying to create an invalid dataset column.