Sharing SMA SUNNY TRIPOWER 15000TL integration using bluetooth and a mysql database

For those who are interested I am sharing the integration of a SMA inverted rectifier into openhab via bluetooth, SBF spot and a mysql data base.
The SMA inverted rectifier has a bluetooth interface which is normally used by a SMA Windows software to fetch the production data. My openhab installation runs on a VM using Debian Jessie as OS. The intention was to visualize at least the current power, the daily harvested energy and the total harvested energy:

The key software which is collecting the data from the SMA converter is called “SBF spot”. Many thanks to the guys involved in this project.

SBF spot is periodically queering our SMA 15000TL and uploading the data to a mysql database.
openhab is periodically fetching the values from this database.
The reason for this approach is to have all the data in a commonly used database so that for example the same data can be visualized in a self hosted website, not only in openhab. Also I don’t want to have “my” data at public sites.

SBF spot was built from https://github.com/sbf-/SBFspot on the same linux vm. The sql database and the SBFspot.cfg was created according to the readme.

SBF spot is periodically called using cron:

***/1 *   * * *   daemon  /opt/SBFspot/bin/Release_MySQL/SBFspot**

On the openhab machine I have the following shell script to get the data from the sql database:

#!/bin/bash
user=sql_user   
password=sql_passwd
sql_name=$1
val=$(mysql -h "sql server ip"  -u $user -p$password -N -e "select $sql_name from SBFspot.Inverters")
echo $val

I stored it in /etc/openhab2/scripts

The following “things” were created in /etc/openhab2/things/sma.things

Thing exec:command:sma_TotalPac [command="/etc/openhab2/scripts/sma.script TotalPac", interval=1, timeout=1]

Thing exec:command:sma_EToday [command="/etc/openhab2/scripts/sma.script EToday", interval=4, timeout=1]
  
Thing exec:command:sma_ETotal [command="/etc/openhab2/scripts/sma.script ETotal", interval=4, timeout=1]

Thing exec:command:sma_TimeStamp [command="/etc/openhab2/scripts/sma.script TimeStamp", interval=1, timeout=1]

Thing exec:command:sma_OperatingTime [command="/etc/openhab2/scripts/sma.script OperatingTime", interval=1, timeout=1]

Thing exec:command:sma_ertag_heute [command="/etc/openhab2/scripts/sma.script EToday", interval=4, timeout=1]

these items:

Group gSMA  "Wechselrichter"    <pie>
Group gSMA_Chart
String SMA_TimeStampStr "[%s]" (gSMA) {channel="exec:command:sma_TimeStamp:output"}
String SMA_TotalPacStr "[%s]" (gSMA, gSMA_Chart) {channel="exec:command:sma_TotalPac:output"}
String SMA_ETotalStr "[%s]" (gSMA) {channel="exec:command:sma_ETotal:output"}
String SMA_ETodayStr "[%s]" (gSMA) {channel="exec:command:sma_EToday:output"}
String SMA_OperatingTimeStr "[%s]" (gSMA) {channel="exec:command:sma_OperatingTime:output"}
String SMA_TotalPac_Msg "Aktuelle Leistung: [%s]" (gSMA)
String SMA_TimeStamp_Msg "Timestamp: [%s]" (gSMA)
String SMA_OperatingTime_Msg "Betriebsstunden: [%s]" (gSMA)
String SMA_ETotal_Msg "Energie gesamt: [%s]" (gSMA)
String SMA_EToday_Msg "Energie heute: [%s]" (gSMA)
Number SMA_Chart_Period     "Chart Period"
Number SMA_TotalPac "Aktuelle Leistung [%d]" (gSMA, gSMA_Chart)

and this rule to fetch the data:

rule "SMA Msg"
when
  System started
  or
  Item SMA_TimeStampStr changed
  or
  Item SMA_TotalPacStr changed
  or
  Item SMA_ETotalStr changed
  or
  Item SMA_ETodayStr changed
then
  var String msg
  msg = SMA_TotalPacStr.state.toString + " W"
  postUpdate(SMA_TotalPac_Msg, msg)
  msg = SMA_ETotalStr.state.toString + " kWh"
  postUpdate(SMA_ETotal_Msg, msg)
  msg = SMA_ETodayStr.state.toString + " kWh"
  postUpdate(SMA_EToday_Msg, msg)
  msg = SMA_TimeStampStr.state.toString
  postUpdate(SMA_TimeStamp_Msg, msg)
  msg = SMA_OperatingTimeStr.state.toString + " h"
  postUpdate(SMA_OperatingTime_Msg, msg)
  postUpdate(SMA_TotalPac, Integer.parseInt(SMA_TotalPacStr.state.toString))
  logInfo("SMA Wechselrichter","Actual Power: " + SMA_TotalPac.state.format("%d"))
end

The final inclusion of the items in my top sitemap:


                Text item=SMA_TotalPac_Msg icon="solarplant" label="Photovoltaik" {
                        Frame label="Ertrag" {
                                Text item=SMA_TotalPac_Msg icon="solarplant"
                                Text item=SMA_OperatingTime_Msg icon="clock"
                                Text item=SMA_ETotal_Msg icon="solarplant"
                                Text item=SMA_EToday_Msg icon="solarplant"
                        }
                        Frame {
                                Switch item=SMA_Chart_Period label="Chart Period" icon="chart" mappings=[0="Hour", 1="Day", 2="Week"]
                                Chart item=gSMA_Chart period=h refresh=600 visibility=[SMA_Chart_Period==0, SMA_Chart_Period=="NULL"]
                                Chart item=gSMA_Chart period=D refresh=3600 visibility=[SMA_Chart_Period==1]
                                Chart item=gSMA_Chart period=W refresh=3600 visibility=[SMA_Chart_Period==2]
                        }
                }

Maybe this is helpful to other openhabeners. Improvement hints are welcome.
Cheers,
Marco

5 Likes

What an excellent & simple solution to perform sql queries! I will re-use your solution for a similar problem that I was trying to solve ! :thumbsup:

By the way: You could use InfluxDB to persist the states of your items and then plot them with Grafana for cooler graphs (or use MySQL with Grafana directly?)

Hello Angelos,
thanks for the Feedback.
Agree, the best solution would be using Grafana directly with the SQL data (now the data is stored twice).
I’ll give it a try and I will update this post if I get it working.

1 Like

Simple question but im stuck on getting the script to run - says mysql - command not found - can anyone help - what do I need installed?

Have you installed the mysql-client package?

Hello,

I have SBFspot installed and it is working. I can also find listings in the database but I can´t get the script working:

2019-03-17 18:19:30.073 [WARN ] [el.core.internal.ModelRepositoryImpl] - Configuration model ‘sma.script’ has errors, therefore ignoring it: [1,1]: missing EOF at ‘#’

My script looks like:

!/bin/bash
user=XXX
password=XXX
sql_name=$1
val=$(mysql -h “192.168.188.101” -u $user -p$password -N -e “select $sql_name from SBFspot.Inverters”)
echo $val

Could anyone assist me, what I am doing wrong?

I also get this failure, when I activate the things:

2019-03-17 18:40:31.768 [ERROR] [hab.binding.exec.handler.ExecHandler] - An exception occurred while executing ‘/etc/openhab2/scripts/sma.script TotalPac’ : ‘Cannot run program “/etc/openhab2/scripts/sma.script”: error=13, Permission denied’

Hello Johannes,
does the script work from command line?
Please try:
./sma.sh ETotal
29352

Check that the script is executable.
Regards
Marco

Now it is executable, but something must be wrong in my settings. Could you please assist again:

SBFspot.cfg

###########################

SQL DB Settings

###########################
#SQLite
#SQL_Database (Fullpath to SQLite DB)
#Windows: C:\Users\Public\SMAdata\SBFspot.db
#Linux : /home/pi/smadata/SBFspot.db
SQL_Database=/home/steuerung/smadata/SBFspot.db

MySQL

SQL_Database=SBFspot
#SQL_Hostname= or
SQL_Username=sbf
SQL_Password=SBF

sma.script

#!/bin/bash
user=sbf
password=SBF
sql_name=$1
val=$(mysql -h “127.0.0.1” -u $user -p$password -N -e “select $sql_name from >SBFspot.Inverters”)
echo $val

Failure:

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user ‘sbf’@‘localhost’ (using password: YES)

Hello Johannes,
looks like no access rights to your SQL Database.
just use mysql in command line mode to debug this.

A few useful commands:

GRANT ALL PRIVILEGES ON SBFspot.* TO ‘sbf’@’%’;
FLUSH PRIVILEGES;

mysqldump -u sbf -p -h 1y.y.y.y SBFspot > SBFspot.sql

Good luck

SBFspot is excelent piece of software. And this mysql script is neat, love it @marco_hoefle ! :slight_smile:

Few months ago I’ve “hacked” original SBFspot a bit so beside mysql (and i’ve added prefixes to original tables) stores data in several files (in ram disk) which then are read by mqtt script and indeed broadcasted to mosquitto as homie3 device.
So if anyone is interested let me know

btw there is wonderfull tool which helps anyone to start with SBFspot very easily, it’s called SBFspot-config

Hi All I’m trying to breath life back into my inverter readings but I am no longer running on a pi but an X86 ubuntu NUC.

My goal is to run it all on this openhab server with the exception of storing the data on a remote MariaDB.
I cant use sbfspot-config and the tutorial must assume I have better linux knowledge and can interpret changes better than I can. Is anyone else running this config and can help limp me through this?

It is quite easy using Docker.
I run a mysql docker container + an openhab container.
There are prebuilt container images around for that.
( e.g. https://hub.docker.com/_/mariadb
https://www.openhab.org/docs/installation/docker.html)

The steps are:

  1. Install a Linux distribution (my favorites are Debian based distributions)
  2. Install Mariadb container and connect to it using command line tools. Test user and password for the SBFspot database
  3. install SBFspot and configure it. check with the sql command line tools the values.
  4. hook up the sql data to openhab

BR

@marco_hoefle is this where you are changing the mysql settings?

sql_dbtype="SQLite"
	SQL_Database="$dir_smadata/SBFspot.db"
	SQL_Hostname=""
	SQL_Username=""
	SQL_Password=""

and what do I do here - from the wiki:

Compile SBFspot with SQLite support:

cd ~/SBFspot/SBFspot
make sqlite
Install SBFspot in /usr/local/bin/sbfspot.3:

sudo make install_sqlite

I used a sql server not sql lite. In my example openhab is fetching the SBFspot data from this server.

Thats where i cant see what I need to do - do you have a point by point what you did? I am also using sql server and have everything set up from when I was running this on a Pi but now on an x86 ubuntu nuc

Can anybody tell me how the shell script should look like when using sqlite3 instead of mysql?
I’m struggeling with it for quite a while… I can read the data from the table “Inverters” with the command below, so I believe I am almost there…

[14:31:06] openhabian@openhab:~$ sqlite3 /home/openhabian/smadata/SBFspot.db “select * from Inverters”
2130065431|SN: 2130065431|SB 3000TL-21|02.50.02.N|1570969813|1039|2925|22016487|29406.8|28111.8|OK|Closed|37.89