BMW ConnectDrive - Bypass MariaDB persistence using ExecCommandLine fails with error 36 filename too long

1

Hi!
I got openhab 3.1M4 together with MariaDB persistence on a Synology running. I use this configuration because of the new BMW ConnectedDrive binding coming up with 3.1. There are several values which I would like to save in a “real” DB, so I setup the persistence with a MariaDB on my synology while openHab is running on a RPi4.
The problem is now that the persisted data is not useable for my purpose. The BMW ConnectedDrive binding is offering last trip values which I would like to save in the DB. It’s a set of 7 values which is each time updated when the trip has finished (TripEndTime, Duration, Distance since last charge, …)
Since each value is now saved in an own table and there is no link between I cannot restore the complete set of data. The timestamp is different in all tables and some values are not updated, because there is no change. If I drive from point A to B, make a break and drive back from B to A the duration is the same and so there is no entry done.
My idea was no to bypass this problem and save the complete recordset with all 7 values in a row but I got always the same error 36 filename too long. Anyone has an idea what’s wrong?
This is my rule
rule “testrule”
when
Item TestSwitch changed from OFF to ON
then
var string mysql_host=“192.168.178.44”
var string mysql_db=“openhabuser”
var string mysql_user=“openhabuser”
var string mysql_pw=“XXXXXXX”

logInfo("testrule", "Starting testrule")
//var string sql_query = "mysql@@-h@@" + mysql_host + "@@-P@@3307@@-u@@" + mysql_user + "@@-p" + mysql_pw + "@@-e@@\"INSERT INTO BMWLastTrip(TripDate,TripDurationMinutes,Distance,DistanceSinceLastCharge,AverageConsumption,AverageRecuperation,Efficiency) VALUES (" + i3TripDateTime.state.toString + "," + i3TripDuration.state.toString + "," + i3TripDistance.state.toString + "," + i3TripDistanceSinceCharge.state.toString + "," + i3AvgTripConsumption.state.toString + "," + i3AvgTripRecuperation.state.toString + ",0);\"@@-D@@" + mysql_db
var string sql_query = "mysql -h " + mysql_host + " -P 3307 -u " + mysql_user + " -p" + mysql_pw + " -e \"INSERT INTO BMWLastTrip(TripDate,TripDurationMinutes,Distance,DistanceSinceLastCharge,AverageConsumption,AverageRecuperation,Efficiency) VALUES (" + i3TripDateTime.state.toString + "," + i3TripDuration.state.toString + "," + i3TripDistance.state.toString + "," + i3TripDistanceSinceCharge.state.toString + "," + i3AvgTripConsumption.state.toString + "," + i3AvgTripRecuperation.state.toString + ",0);\" -D " + mysql_db

logInfo("SQL","SQL query to be executed: " + sql_query)
var String mysql = executeCommandLine(sql_query)

if(mysql!="") {
	logInfo("SQL","Result SQL query: " + mysql)
}
logInfo("testrule", "done")

end

And this is the result:

2021-05-19 20:43:08.936 [INFO ] [org.openhab.core.model.script.SQL ] - SQL query to be executed: mysql -h 192.168.178.44 -P 3307 -u openhabuser -pXXXXXXX -e “INSERT INTO BMWLastTrip(TripDate,TripDurationMinutes,Distance,DistanceSinceLastCharge,AverageConsumption,AverageRecuperation,Efficiency) VALUES (2021-05-19T19:46:00.000+0200,12.0 min,9.0 km,51.0 km,13.1 kWh,8.0 kWh,0);” -D openhabuser

2021-05-19 20:43:08.945 [WARN ] [rg.openhab.core.io.net.exec.ExecUtil] - Error occurred when executing commandLine ‘[mysql -h 192.168.178.44 -P 3307 -u openhabuser -pXXXXXXX -e “INSERT INTO BMWLastTrip(TripDate,TripDurationMinutes,Distance,DistanceSinceLastCharge,AverageConsumption,AverageRecuperation,Efficiency) VALUES (2021-05-19T19:46:00.000+0200,12.0 min,9.0 km,51.0 km,13.1 kWh,8.0 kWh,0);” -D openhabuser]’

java.io.IOException: Cannot run program “mysql -h 192.168.178.44 -P 3307 -u openhabuser -pXXXXXXX -e “INSERT INTO BMWLastTrip(TripDate,TripDurationMinutes,Distance,DistanceSinceLastCharge,AverageConsumption,AverageRecuperation,Efficiency) VALUES (2021-05-19T19:46:00.000+0200,12.0 min,9.0 km,51.0 km,13.1 kWh,8.0 kWh,0);” -D openhabuser”: error=36, File name too long

2

For OH3 the syntax of executeCommandLine changed.
All parameters of the command need to be separated by commas.
As your SQL command itself already contains commas these might have to be escaped.
I think it will be easier to let a shell script execute the SQL command.

3

If you set up your .persist file properly you can have it save the updates even if the values are the same. There is an everyUpdate strategy.

4

What would be a proper setup of the persist file? Currently, I have set up this items. I have a group for all last trip items:

Group sgi3LastTrip “BMW i3 Letzte Fahrt” (mgi3)

and my last trip items are:

DateTime i3TripDateTime “Datum [%1$tA, %1$td.%1$tm. %1$tH:%1$tM]” (mgi3,sgi3LastTrip) {channel=“bmwconnecteddrive:bev:user:i3:last-trip#date”}
Number:Time i3TripDuration “Fahrtdauer [%d %unit%]” (mgi3,sgi3LastTrip) {channel=“bmwconnecteddrive:bev:user:i3:last-trip#duration”}
Number:Length i3TripDistance “Strecke [%d %unit%]” (mgi3,sgi3LastTrip) {channel=“bmwconnecteddrive:bev:user:i3:last-trip#distance” }
Number:Length i3TripDistanceSinceCharge “Strecke seit letzter Ladung [%d %unit%]” (mgi3,sgi3LastTrip,long) {channel=“bmwconnecteddrive:bev:user:i3:last-trip#distance-since-charging” }
Number:Energy i3AvgTripConsumption “Durchschnittsverbrauch [%.1f %unit%]” (mgi3,sgi3LastTrip) {channel=“bmwconnecteddrive:bev:user:i3:last-trip#avg-consumption” }
Number:Energy i3AvgTripRecuperation “Durchschnitts Rekuperation [%.1f %unit%]” (mgi3,sgi3LastTrip) {channel=“bmwconnecteddrive:bev:user:i3:last-trip#avg-recuperation” }

and for the persistence I have setup
Strategies {
everyMinute : “0 * * * * ?”
everyHour : “0 0 * * * ?”
everyDay : “0 0 0 * * ?”
default = everyChange
}

Items {
sgi3LastTrip* : strategy = everyChange, restoreOnStartup
}

I would expect that each change on the group will trigger the persistence of all items in the group. But I have these timestamp values:
trip consumption 2021-05-19 19:47:48.933
recupation 2021-05-19 19:47:48.687
trip date 2021-05-19 19:47:37.485
distance since last charge 2021-05-19 19:47:49.039
trip distance 2021-05-19 17:47:48.320 (even worse, this was my drive from A to B, B to A was not persisted)
duration 2021-05-19 17:47:48.471 (seems that it took me the same time back)

@Wolfgang_S … What exactly should be changed, something like -pXXXXXX change to ,pXXXXXX and escaping the coma inside the statement?

5

No, that’s not how it works. It will persist any item that is a member of that group whose state changes when it changes. It doesn’t persist all members of the group is one item changes.

If you want to persist the states even if they are there same, use everyUpdate instead of everyChange Then it will persist the value even if the item is updated to the same state as it already had before.

6

Yes, that would work. Unfortunately this causes a lot of redundant data. I have this in mind for plan B. I could do a job which is using this staging area and merges then this each xx minutes in a final table of my own format. After that, cleaning the “staging area” to keep the table size small…
But the TripDate is the perfect trigger for my purpose. Ony when this changes there is a new trip. I will see if I could go for a script file and call this by the execCommandLine…

7

OK, I got now a working prototype :slight_smile:
First I defined my final Table on the Synology MariaDB

TableStructure
TableStructure1552×858 159 KB

Then I created a new rule
triggers:

  • id: “1”
    configuration:
    itemName: i3TripDateTime
    type: core.ItemStateChangeTrigger
    conditions: []
    actions:

  • inputs: {}
    id: “2”
    configuration:
    type: application/javascript
    script: >+
    //create connection

    var conn = java.sql.DriverManager.getConnection("jdbc:mariadb://192.168.178.44:3307/openhabian", "openhabian", "XXXXXXXXXXXXXXXXXXX");


var stmt = conn.createStatement();


var tripDateTime = itemRegistry.getItem("i3TripDateTime").getState().toString() //2021-05-19T19:46:00.000+0200

var tripDateTimeString = tripDateTime.replace("T", " ").split(".")[0]//2021-05-19 19:46:00

var tripDuration = itemRegistry.getItem("i3TripDuration").getState().toString().replace("min","").trim()

var tripDistance = itemRegistry.getItem("i3TripDistance").getState().toString().replace("km","").trim()

var tripDistanceSinceCharge = itemRegistry.getItem("i3TripDistanceSinceCharge").getState().toString().replace("km","").trim()

var avgTripConsumption = itemRegistry.getItem("i3AvgTripConsumption").getState().toString().replace("kWh","").trim()

var avgTripRecuperation = itemRegistry.getItem("i3AvgTripRecuperation").getState().toString().replace("kWh","").trim()

var rs = stmt.executeQuery("Insert into BMWi3LastTrip (TripDateTime,TripDuration,TripDistance,DistanceSinceCharge,AvgTripConsumption,AvgTripRecuperation) VALUES ('" + tripDateTimeString + "'," + tripDuration + "," + tripDistance + "," + tripDistanceSinceCharge + "," + avgTripConsumption + "," + avgTripRecuperation + ");")

    type: script.ScriptAction

I know, it looks awful, no error handling, string manipulating , but for the first prrof of concept it is ok…

8

Assuming you didn’t need them all in the same table, another way to only save the values when one of the Items you care about changes is to not include the Items in the .persist file. Then in a rule that triggers when the Item you care about changes, call .persist() on the Items you want to save.

The timestamps will still be a few msec off between them but they’ll all be saved in the same hundred msec or so.

9

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.