Persistence not working for GPS location formatted XX.XXX75788937955,-X.XXXX170208076405

Add-ons Bindings
, ,
21

The issue might be a difference in the two existing influxDB persistence services

conversions for org.openhab.persistence.influxdb

    /**
     * Converts {@link State} to objects fitting into influxdb values.
     *
     * @param state to be converted
     * @return integer or double value for DecimalType, 0 or 1 for OnOffType and OpenClosedType,
     *         integer for DateTimeType, String for all others
     */
    private Object stateToObject(State state) {
        Object value;
        if (state instanceof HSBType) {
            value = ((HSBType) state).toString();
            logger.debug("got HSBType value {}", value);
        } else if (state instanceof PointType) {
            value = point2String((PointType) state);
            logger.debug("got PointType value {}", value);
        } else if (state instanceof DecimalType) {
            value = convertBigDecimalToNum(((DecimalType) state).toBigDecimal());
            logger.debug("got DecimalType value {}", value);
        } else if (state instanceof OnOffType) {
            value = (OnOffType) state == OnOffType.ON ? 1 : 0;
            logger.debug("got OnOffType value {}", value);
        } else if (state instanceof OpenClosedType) {
            value = (OpenClosedType) state == OpenClosedType.OPEN ? 1 : 0;
            logger.debug("got OpenClosedType value {}", value);
        } else if (state instanceof DateTimeType) {
            value = ((DateTimeType) state).getCalendar().getTime().getTime();
            logger.debug("got DateTimeType value {}", value);
        } else {
            value = state.toString();
            logger.debug("got String value {}", value);
        }
        return value;
    }

and for org.openhab.persistence.influxdb08

  /**
   * Converts {@link State} to objects fitting into influxdb values.
   * 
   * @param     state to be converted
   * @return    integer or double value for DecimalType, 
   *            0 or 1 for OnOffType and OpenClosedType,
   *            integer for DateTimeType,
   *            String for all others
   */
  private Object stateToObject(State state) {
    Object value;
    if (state instanceof DecimalType) {
      value = convertBigDecimalToNum(((DecimalType) state).toBigDecimal());
    } else if (state instanceof OnOffType) {
      value = (OnOffType) state == OnOffType.ON ? 1 : 0;
    } else if (state instanceof OpenClosedType) {
      value = (OpenClosedType) state == OpenClosedType.OPEN ? 1 : 0;
    } else if (state instanceof HSBType) {
      value = ((HSBType) state).toString();
    } else if (state instanceof DateTimeType) {
      value = ((DateTimeType) state).getCalendar().getTime().getTime();
    } else {
      value = state.toString();
    }
    return value;
  }
22

Thanks very much @MartinKo !, but didn’t work either after trying it out now. One question in this line of code:

StringLocation.postUpdate(LocationItem.state.toString())

Is LocationItem a method or a typo and should it be StringLocation?

Is there something I could do to get it to work or should I wait for an update/fix?

23

Sorry for being unclear in the example
 LocationItem is just a name of a Location item. Your LocationItem was IPhone7Plus_L ?

Then for you it will be

StringLocation.postUpdate(IPhone7Plus_L.state.toString())

StringLocation is just an item of type String with the name StringLocation

24

Oh! I see now. Thanks again @MartinKo for clarifying. The item name is IPhone7Plus_Location

I tried it now, but no luck :pensive: I paste below the code converted to my system.

ITEM

String IPhone7Plus_LocString "iPhone Location String"

RULES

rule "iPhone"
when
   Item IPhone7Plus_LocString changed
then
   IPhone7Plus_LocString.postUpdate(IPhone7Plus_Location.state.toString())
end

PERSIST


Strategies {
    everyMinute : "0 * * * * ?"
    everyHour   : "0 0 * * * ?"
    everyDay    : "0 0 0 * * ?"
}

Items {
    ZWaveNode4FGMS001MotionSensor_SensorTemperature                     : strategy = everyChange, everyHour
    ZWaveNode2FGKF601Keyfob_SceneNumber                     : strategy = everyChange
    ZWaveNode3FGWP102MeteredWallPlugSwitch_ElectricMeterWatts           : strategy = everyChange
    IPhone7Plus_LocString                     : strategy = everyChange, everyHour
    ZWaveNode3FGWP102MeteredWallPlugSwitch_Switch       : strategy = everyChange
}
25

Your RULES file never fires right
 I think it should be

rule "iPhone"
when
   Item IPhone7Plus_L changed
then
   IPhone7Plus_LocString.postUpdate(IPhone7Plus_L.state.toString())
end

This way the item IPhone7Plus_LocString gets updated when the item IPhone7Plus_L gets updated

1 Like
26

Thanks @MartinKo and that made the trick! At least now I can get this to persist. I learned a lot with this too.

1 Like
27

I believe that you’re right, and given this I think that something should be fixed, but at least the workaround posted by @MartinKo seems to push to influxDB the location.

28

As I understand it, LocationItem is new in OH2/ESH. The persistence service, being an OH1 service, only supports OH1 items/types.

29

Hi, I got the following error on Mqsql, how can I solve it.

2020-11-21 14:36:04.052 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not create table for item ‘iPhone5_LocString’ with statement ‘CREATE TABLE Item4875 (Time DATETIME, Value VARCHAR(20000), PRIMARY KEY(Time));’: Column length too big for column ‘Value’ (max = 16383); use BLOB or TEXT instead

2020-11-21 14:36:04.054 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Item ‘iPhone5_LocString’ was not added to the table - removing index

2020-11-21 14:36:04.113 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item ‘iPhone5_LocString’ in database with statement ‘INSERT INTO Item4875 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;’: Table ‘OpenHAB.Item4875’ doesn’t exist

30

I’m sorry I don’t have a solution as it seems to be the way OpenHAB tries to create the table. It’s trying to make a column with the length on 20000 characters but MySQL is limited to 16383.
I don’t think there’s much you can do. Unless of course you create the table yourself.
You’d need to run ‘ CREATE TABLE Item4875 (Time DATETIME, Value VARCHAR(16383), PRIMARY KEY(Time));’ into MySQL.

31

I tried but it didn’t work