[FreeBSD]: Send SMA SunnyBoy output into MySQL/MariaDB.

How to write regular energy output into a MySQL/MariaDB database:

Create database table in mysql/mariadb

CREATE TABLE solar (`id` int(11) NOT NULL,  
                    `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
                    dailytotal float NOT NULL,  
                    power float NOT NULL,  
                    pdc1 float NOT NULL,  
                    pdc2 float NOT NULL,  
                    dailymin float NOT NULL);

Write the perl script for reading SME SunnyBoy data from FHEM to a local file named smastp_testp.pl:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
#!/usr/bin/perl

################################################################
##
##  Copyright notice
##
##  (c) 2016 Copyright: Volker Kettenbach
##  e-mail: volker at kettenbach minus it dot de
##
##  Credits:
##  - based on an Idea by SpenZerX and HDO
##  - Waldmensch for various improvements
##  - sbfspot (https://sbfspot.codeplex.com/)
##
##  Description:
##  This is a test program for the SMA Sunny Tripower Inverter
##  with Speedwire (Ethernet) interface (-TL20 or -TL10 with Webconnect piggyback)
##  Tested on Sunny Tripower 6000TL-20, 10000-TL20 and 10000TL-10 with
##  Speedwire/Webconnect Piggyback
##
##  Use this code before you use 77_SMASTP.pm in FHEM to try,
##  if your Sunny Tripower can be quried and if the received values make sense.
##
##  Origin:
##  https://github.com/kettenbach-it/FHEM-SMA-Speedwire
##
#################################################################

use strict;
use warnings;
use IO::Socket::INET;

if (@ARGV < 2) {
        print "Usage $0 <inverterip> <inverteruserpass>\n";
        exit;
}
my $Host = $ARGV[0];
my $port = 9522;
my $Pass = $ARGV[1];

# Global vars
my $cmd_login                   = "534d4100000402a000000001003a001060650ea0ffffffffffff00017800C8E8033800010000000004800c04fdff07000000840300004c20cb5100000000encpw00000000";
my $cmd_logout                  = "534d4100000402a00000000100220010606508a0ffffffffffff00037800C8E80338000300000000d7840e01fdffffffffff00000000";
my $cmd_query_total_today       = "534d4100000402a00000000100260010606509e0ffffffffffff00007800C8E80338000000000000f1b10002005400002600ffff260000000000";
my $cmd_query_spot_ac_power     = "534d4100000402a00000000100260010606509e0ffffffffffff00007800C8E8033800000000000081f00002005100002600ffff260000000000";
my $cmd_query_spot_dc_power     = "534d4100000402a00000000100260010606509e0ffffffffffff00007800C8E8033800000000000081f00002805300002500ffff260000000000";

my $code_login                  = "0d04fdff";   #0xfffd040d;
my $code_total_today            = "01020054";   #0x54000201;
my $code_spot_ac_power          = "01020051";   #0x51000201;
my $code_spot_dc_power          = "01028053";   #0x53800201;

use constant MAXBYTES => scalar 200; #1024 #80

my $encpw = "888888888888888888888888"; # unencoded pw
for my $index (0..length $Pass )        # encode password
{
        substr($encpw,($index*2),2) = substr(sprintf ("%lX", (hex(substr($encpw,($index*2),2)) + ord(substr($Pass,$index,1)))),0,2);
}
$cmd_login =~ s/encpw/$encpw/g;         #replace the placeholder with password

my $socket = new IO::Socket::INET (PeerHost => $Host, PeerPort => $port, Proto => 'udp', Timeout => 2);
if (!$socket) {
        # in case of error
        die "ERROR. Can't open socket to inverter: $!\n";
};

my $end=0;
my $size=0;
my $error=0;
my $SpotPower=0;
my $TodayTotal=0;
my $AlltimeTotal=0;
my $PDC1=0;
my $PDC2=0;
my $code=0;

print "Sending to inverter $Host:9522\n";
print "send: Login, ";
my $data = pack "H*",$cmd_login;
$size = $socket->send($data);
print "$size bytes sent - ";

do
{
        $socket->recv($data, MAXBYTES) or die "recv: $!";
        $size = length($data);
        my $received = unpack("H*", $data);
        print "Received $size bytes: ($received) \n";

        # unpack command
        my $code = unpack("H*", substr $data, 42, 4);
        print "Got code: $code ";

        # answer to command login
        if  ($code_login eq $code)
        {
                print "send: Query total today, ";
                $data = pack "H*",$cmd_query_total_today;
                $size = $socket->send($data);
                print "$size bytes sent - ";
        }

        # answer to command total today
        if  ($code_total_today eq $code)
        {
                $TodayTotal  = unpack("V*", substr $data, 78, 4);
                $AlltimeTotal  = unpack("V*", substr $data, 62, 4);
                print "send: Query spot AC power, ";
                $data = pack "H*",$cmd_query_spot_ac_power;
                $size = $socket->send($data);
                print "$size bytes sent - ";
        }

        # answer to command AC power
        if  ($code_spot_ac_power eq $code)
        {
                $SpotPower  = unpack("V*", substr $data, 62, 4);
                # special case at night ? Inverter off?
                if ($SpotPower eq 0x80000000) {$SpotPower = 0};
                print "send: Query spot DC power, ";
                $data = pack "H*",$cmd_query_spot_dc_power;
                $size = $socket->send($data);
                print "$size bytes sent - ";
        }

        # answer to command DC Power
        if  ($code_spot_dc_power eq $code)
        {
                $PDC1 = unpack("V*", substr $data, 62, 4);
                if ($PDC1 eq 0x80000000) {$PDC1 = 0};
                $PDC2 = unpack("V*", substr $data, 90, 4);
                if ($PDC2 eq 0x80000000) {$PDC2 = 0};
                # send: cmd_logout
                print "send: Logout, ";
                $data = pack "H*",$cmd_logout;
                $size = $socket->send($data);
                print "$size bytes sent.";
                $end=1;
                $socket->close();
        }
} while (($end ne 1 ));

print "Results: \n";
print "Today Total: ". $TodayTotal . "\n";
print "All Time Total: " . $AlltimeTotal . "\n";
print "SpotPower: " . $SpotPower . "\n";
print "PDC1: " . $PDC1 . "\n";
print "PDC2: " . $PDC2 . "\n";

Write a small bash script that uses the perl script from above to write the data into MySQL/MariaDB:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/usr/local/bin/bash

SMA_IP="[IP address of your SMA SunnyBoy]"
DBHOST="[Your database hostname, e.g. localhost]"
DBUSER="[Your database user]"
DBPASS="[Your database password]"
DB_DB="[The database where you created the solar table]"
RESULT=`./smastp_test.pl $SMA_IP 0000`
DAY=`echo "$RESULT" | grep Today | cut -d " " -f 3`
POWER=`echo "$RESULT" | grep SpotPower | cut -d " " -f 2`
PDC1=`echo "$RESULT" | grep PDC1 | cut -d " " -f 2`
PDC2=`echo "$RESULT" | grep PDC2 | cut -d " " -f 2`
echo "Today: $DAY"
echo "Spot power: $POWER"
echo "PDC1: $PDC1"
echo "PDC2: $PDC2"


SQL="INSERT INTO solar (dailytotal, power, pdc1, pdc2) VALUES ($DAY / 1000, $POWER / 1000, $PDC1, $PDC2);"
mysql -h $DBHOST -u $DBUSER -p$DBPASS --database $DB_DB --execute "$SQL"

TIMESTAMP=`date "+%b %d %T"`
echo "$TIMESTAMP SOLAR day:$DAY power:$POWER pdc1:$PDC1 pdc2:$PDC2" >> /var/log/solar.log

echo $SQL

Yes, I know, you could also write to the database directly from perl, but I'm not fluent in perl and I wanted a simple, quick solution. This is why I decided to stick with a small bash script.