How to: set your power/watermeter for domoticz on rpi

  • This worked for me.
    All steps are done in command line, so you don't need screen/keyboard on the raspberry pi. To get to the commandline you need to open a ssh-session (command ssh pi@raspberrypi.local on linux and macos, use putty on windows)
    You can also do the same steps with a graphical sqlite-editor.

    1. Get a db-editor
      wget --no-check-certificate
      (please look which version is current on the sqlite page)
      cd sqlite-amalgamation-3100100
      gcc shell.c sqlite3.c -lpthread -ldl
      Get a cup of tee, this takes a while... Don't close the ssh-session!
      When the compiler is done, it has created a file named a.out, i rename it for convenience:
      mv a.out sqlit3
      this is an executable file, you can move it wherever you want.

    2. make a copy of the domoticz database in case something goes wrong!
      you can find this in the domoticz web interface at setup->settings->backup or you can copy the file directly on the rpi (need admin-rights):
      sudo cp <path-to-domoticz>/domoticz.db <some-save-path>

    3. Get domoticz and the node ready:
      Stop domoticz:
      sudo <path-to-domoticz>/ stop or in the web interface. It this does not work you might need to make the script executable: chmod +x
      Stop the power/watermeter node (e.g. unplug the power supply).
      Read the meter. Calculate the pulse-variable by multiplying the meter-reading with the pulse-factor.
      In this example the meter reads 1150.210 kWh, the pulse-factor is 500. The pulse-variable needs to be set to 575105.

    4. Do the edit.
      Open the domoticz.db:
      sudo ./sqlite3 <path-to-domoticz>/domoticz.db
      The table to edit is MySensorsVars.
      In this example the powermeter node has the id 12, the pulse variable is set to 575105.
      UPDATE MySensorsVars SET Value=575105 WHERE NodeID=12;
      Check the result with
      SELECT * FROM MySensorsVars;
      Exit sqlite with .quit

    5. Restart
      sudo <path-to-domoticz>/ start
      plug in the power/watermeter-node.

    Have Fun!

  • you know you can install sqlite using apt?

    sudo apt-get install sqlite

    saves a LOT of compile time.

  • Well actually i didn't. Thanks a lot!