[Solved] Mapping MySQL table to Item (sensor)

Setup, Configuration and Use Items & Sitemaps
,
1

Hi good people!

Under the title “Database overview” in the document for MySQL-Presistens I read “The service will create a mapping table to link each item to a table”. Great! But where is this mapping table? I don’t find it in mySQL so I am lost here. I can of course figure this out by looking at the data, but I would like my graph script to be able and find this info automagically.

I also like to clean out old invalid (after renaming items) tables with a script.

Thanks!

2

The MySQL persistence service maintains a table called Items that maps an auto-generated number to an item name, and then creates a table called ItemN, where N is the auto-generated number. So you might have an item Temperature which is mapped to table Item38.

But it’s not difficult to find the mapping. For example:

SET @a = (SELECT CONCAT('Item',ItemId) AS TableName from Items where ItemName='Temperature');
SET @x := CONCAT('SELECT * FROM ', @a);
Prepare stmt from @x;
Execute stmt;
DEALLOCATE Prepare stmt;

An alternative is to use the JDBC persistence service, which can use item names as table names, and can therefore avoid complexity like above.

3

Hi watou!

That was an impressively fast response :smiley:.

I totally missed the “Items” table so now I can move forward with my scripts. Thank’s a lot!!!

1 Like