Problems changing item type

Hey guys,

i have a problem after changing an item from String to Number. I have included this item in jdbc persistence and an item group, that i want to visualise as chart. The creation of the chart was done when the item was StringType, so the persistence table was created with the value-column as varchar. In the tabe the values were stored correctly but in the chart that item was displayed with zero.
Then I changed the item type to Number in the items file. Unfortunately, the persistence did not work anymore. So i deleted the table but it was not re-created automatically. So I created the table manually with the value column as double. The persistence worked again.
When displaying the chart i got a “ClassCastException” as it tries to cast the item from varchar to number. I have no idea where this logic is configured and where I can reset or recreate it.
I have tried to

  • remove and add the item from the group
  • remove and add the item from the items file
  • remove the chart from the sitemap
  • remove the table and recreate it in the database
  • restart the persistence bundle

nothing worked. Following the log (it still says the item is StringType):

12:56:16.060 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is GarageBat
12:56:16.061 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDecimalcount='3' table='garagebat_0036' item='GarageBat (Type=StringItem, State=37)' itemName='GarageBat'
12:56:16.061 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = org.openhab.core.persistence.FilterCriteria@a4a8d8e, numberDecimalcount = 3, table = garagebat_0036, simpleName = GarageBat
12:56:16.061 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM garagebat_0036 WHERE TIME<'2017-08-26 13:56:15' ORDER BY time DESC  LIMIT 0,1
12:56:16.061 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM garagebat_0036 WHERE TIME<'2017-08-26 13:56:15' ORDER BY time DESC  LIMIT 0,1
12:56:16.061 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: query for GarageBat returned 0 rows in 0 ms
12:56:16.062 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is GarageBat
12:56:16.062 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDecimalcount='3' table='garagebat_0036' item='GarageBat (Type=StringItem, State=37)' itemName='GarageBat'
12:56:16.062 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = org.openhab.core.persistence.FilterCriteria@710644c5, numberDecimalcount = 3, table = garagebat_0036, simpleName = GarageBat
12:56:16.062 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM garagebat_0036 WHERE TIME>'2017-08-26 13:56:15' AND TIME<'2017-12-24 12:56:15' ORDER BY time ASC
12:56:16.062 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM garagebat_0036 WHERE TIME>'2017-08-26 13:56:15' AND TIME<'2017-12-24 12:56:15' ORDER BY time ASC
12:56:16.070 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '56.0', getClass = 'class java.lang.Double', clazz = 'Double'
12:56:16.070 [WARN ] [eclipse.jetty.servlet.ServletHandler] - /chart java.lang.ClassCastException

Is there a documentation of the persistence where I can change the configuration or has anybody changed an item type before?

Thanks
Peter

I guess you will have to log in to the database and remove the table belonging to the item manually.

Let openHAB recreate the table

When does openHAB recreate the table?
I tried that and it did not recreate the table. Instead it threw an error that the table does not exist. So I had to recreate the table manually.

Besides: After checking the items in the console, it said that the affected item is StringType. And while checking the items files for the 20th time I finally saw that the first word in the line was indeed String instead of Number :frowning:

So many places to configure :slight_smile:

It seems to me that I have to create a topic before I am able see my own mistakes.

Well, if deleting a table, of course you have to delete the entry in the “which table is which item” table, too.