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
Energy monitoring, SQL and chart questions
Energy monitoring, SQL and chart questions
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.
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!
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
If you got this going we will go further, any questions just let me know!
http://www.bwired.nl Online Home, Domotica, Home Automation. Weblog. http://blog.bwired.nl
Energy monitoring, SQL and chart questions
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
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
Energy monitoring, SQL and chart questions
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.
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.
Energy monitoring, SQL and chart questions
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
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
Energy monitoring, SQL and chart questions
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.
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.
http://www.bwired.nl Online Home, Domotica, Home Automation. Weblog. http://blog.bwired.nl
Energy monitoring, SQL and chart questions
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:
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
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
MySQL is the database.
Can someone please help me little on the way?
Regards
Jonny Larsson