Energy monitoring, SQL and chart questions

[Snowy]

Pieter and other users;

In my new home I also will get an energy meter with a LED pulse.
The output shown on the bwired site is exactly what I'm looking for.
Electronics are already build, using a C-Control(laying around doing nothing). The LED pulse is counted and stored.
By RS232 I can receive the data (format real simple right now, just a number of LED pulses).

SQL and scripting (ie Perl) is not my thing right now, I understand that Bwired is using the TEMP08 1-wire interface.
Questions;

- What kind of data format is used to store the data in the database (ie tables, schemes etc)
- How's the dataformat of the TEMP-08, maybe I can simulate that?
- How and with what are the energy charts made?
- Is there any sample code available for starting this project or just some hints where to start.

I have limit knowledge of programming, Visual Basic I know, but that's about it.
I know, lot's of questions, by maybe with a little help one day I can make an equaly impressing site [:D]

Menno

[Bwired]

Hi Menno,
I will try and help you step by step.
If you get the pulses with your C-Control and you have your C-Control connect with visual basic, you can sore the pulses in a variable.
So everytime a pulse (led flash) comes in the variable is added with 1. To store the data in a MYSQL database with Visual basic you can for example try this.

Code: Select all

Dim My_conn, strSql, Rsp

Set my_conn = createobject("ADODB.Connection")
my_conn.open = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=Your_server;DATABASE=Your_database;UID=root;PWD=;OPTION=3;"
Set rsp = CreateObject("ADODB.Recordset")
	
strSql = "SELECT * FROM your_table ORDER BY id LIMIT 1"
rsp.Open strSQL, my_conn, 1, 3

rsp.AddNew
rsp("Datum") = Now()
rsp("Tijd") = Time
rsp("Device") = Naam
rsp("Counter") = Pulse

rsp.Update
rsp.Close
set rsp = nothing
my_conn.close
set my_conn = nothing

You have to download or use a Client for MYsql like EMS mysqlmanager, with this client you can create databases and tables in mysql.
If you got this going we will go further, any questions just let me know!

[Snowy]

Hi Pieter,

Thanks for helping,

I did install mysql from the XAMMP package (mysql, perl, php, appache)
I did create a database with tables date, time, device and counter.

After some expirementing with SQL statements, I did download the EMS SQL manager and created a simple form with the aid of your sample.
But.... I am unable to connect to the database, also in the ODBC panel I'm unable to connect and verify the database.
From the EMS manager and also from PHP I am able to fire queries to the database.

I'm certain it should work though but for now not in VB.

A simple query like this woll give some data;

SELECT SUM(Counter) FROM `meterstanden` WHERE HOUR(`Tijd`)=17 and `Datum`=CURDATE() and `Device`="Stroom";

This are my very first steps into SQL [:D], within the query itself I wanted to dump the SUM of the counter per hour, but I wasn't (yet) succesful in that.
When replacing the '17' (hour) with a variable withing PHP I could dump a table.

Any thoughts?

Menno

[Bwired]

Hi Menno

Let's try to get it running in Visual Basic for now
Did you download and install the ODBC driver for MYSQL.
Here you can get it http://dev.mysql.com/downloads/connector/odbc/3.51.html
I think then it will run in Microsoft Visual Basic.

Pieter Knuvers
www.bwired.nl Online House in the netherlands. Domotica, Home Automation.

[janeway]

Hi Pieter,

ik ben hier nieuw en stel de vraag in het nederlands, als dit anders gewenst is hoor ik dit graag.

Het volgende ik ben bezig om mijn hijs te besturen met een plc, de gegevens worden opgeslagen in een datebase (mysql, odbc). wat ik graag zowillen weten hoe ik een grafiek op mijn site krijg. als je dit met een voorbeeld zou kunnen toelichten zou zeer welkom zijn (temperatuur grafiek)

Groet Janeway

[Bwired]

Hoi Janeway,
I use two ways for presensting my charts.
One is a self written ASP program

domoticaforum.eu/uploaded/bwired/200661 ... _chart.zip
I use Mysql as well, just fill in the right settings for your mysqlserver (servername, user, password etc). adjust the SQL select and ASP for your own data and place the imagefiles in the /image directory and it should be running. I use this graph for displaying my phone records.
The program Chartdirector http://www.advsofteng.comis also a great program to use. It comes with multiple examples.
Hope this helps.

[jompa68]

Hello freinds,

i am trying to setup charts like BWired, i really like them alot.
I am trying to follow your Phone_out_dag_charts.asp but i dont get it to work.

Here is my SQL string:

Code: Select all

select
                energy.timegroup as "Hour",
                round(sum(energy.countdiff)/10000,1) as "kWhDiff",
                round(((sum(energy.countdiff))+(energy.startcounter-input.countstart))/10000,1) as "kWhSum"
                from
                (
                SELECT i.counter_c-c.counter_c as "countdiff",
                       c.counter_c as "startcounter",
                       date_format(c.timestamp,'%Y-%m-%d %H') as "timegroup"
                FROM elhuset c
                join (select * FROM elhuset) i on c.index=i.index-1 and c.timestamp between timestampadd(hour,-24, Now()) and Now()
                ) energy,
                (
                SELECT min(s.counter_c) as "countstart"
                FROM elhuset s
                join (select * FROM elhuset) t on s.index=t.index and s.timestamp between timestampadd(hour,-24, Now()) and Now()
                ) input
                
                group by energy.timegroup

I have chartsoftware installed from advsofteng.com and it is working.
MySQL is the database.

Can someone please help me little on the way?

Regards
Jonny Larsson