[Solved] Mapping MySQL table to Item (sensor)

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.


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.

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