Sqlth_te: Update the "id" manually

Ignition
1

Dear @Kevin.Herron / @Kevin.McClusky,

We have already a tag historian developed (Using Delphi) and used it for a long time.
Due to this, we have a reference ID for each DCS signal. All our internal applications are using the aforementioned reference ID’s.

Now we are migrating to Ignition based historian. With this in mind, we wanted to use the old reference ids for all our DCS tags.

Is there a way to manually update the “id” column in “sqlth_te” table?

Thanks!

2

I can tell you that the historian by default will generate new ids automatically all the time. That is why there is a retired column to mark when a tag path gets retired. So you could have 10 id’s corresponding to one tagpath.

1 Like
3

Thanks @brandon1 :slightly_smiling_face:

I agree, But can we generate our own ID’s?

4

As @brandon1 said, you could end up with multiple rows for the same tagpath but each row would have its own unique id. The id values in sqlth_te have to be unique for each row, so you cannot force two rows with the same tagpath to the same id value. So if you ever retire a tag and then re-add at a later date, you would not be able to set both rows to the same id value.

What MIGHT be worth trying is to create an intermediate table that has your old reference id and the tagpath that should be associated with it, you could then JOIN this new table to the sqlth_te table on matching tagpaths. This will allow you to convert from ignition ids to your old reference ids and vice versa. If tagpaths change, you could add additional rows for the old reference ids in this new table, making it a many-to-many relationship table.

There is probably a better option, but just what came to mind…

1 Like
5

Thanks, :slightly_smiling_face: @WillMT10

I have tried a similar solution, I think this should work for the requirement. As you suggested I should maintain the old ref number and tag path in the mapping table.

In the sqlth_te table I have inserted a reference number column (This will be the foreign key)

Am I going in the right direction?

Regards,
Saravanan.

6

All I can say is everything for native tag history has to use tagpaths. So I guess if you were running a query on that intermediate table to produce the correct tag path it would work. I just don’t see what the real payoff is. In Ignition all tags used for animates are going to be paths not a reference ID. So why not just document what those reference IDs were and just not use them at all in the new system? Just my two cents.

1 Like
7

Concur. Avoid editing sqlth_te, as your reference IDs won’t be copied when Ignition retires an entry. Instead, create a separate index table of your reference ID and the corresponding Ignition tag path. Perform lookups and/or joins as needed.

2 Likes