Home Automation Domotica Forum Europe, Bwired Forum

I finally have the watermeter working.

Now I can read it with digitemp, and get a value (last value)
[root@kroonen ~]# digitemp -aq
0 360506 36255290

How can I make a mysql database that stores the values with a time? and make a script to put the value into it.
database called watermeter example with table meterstand, and muts there be a sperate table for time?

any help would be nice

its the beginning works, just look for a tutorial that does perl + mysql.. basicly you can make a script which calls those values..

so "perlinput.pl `digitemp -aq`" and the in perl you have $ARGV[0] [1] and [2] as values, then use those to do a "insert into db etc etc "

Get the idea?

// Erik (binkey.nl)

Hi,

i have tried the following, the last value must be stored.
what is worng?

my $debug = 0;

# Connect to the database
my $dbh = DBI->connect("dbi:mysql:$db_name","$db_user","$db_pass")
or die "I cannot connect to dbi:mysql:$db_name as $db_user - $DBI::errstr\n";


# Gather information from DigiTemp
# Read the output from digitemp

open( DIGITEMP, "$digitemp_binary -q -a -c $digitemp_rcfile |" );

while( <DIGITEMP> )
{

($water=$ARGV[2]

my $sql="INSERT INTO digitemp SET water=$water";
print "SQL: $sql\n" if($debug);
$dbh->do($sql) or die "Can't execute statement $sql because: $DBI::errstr";
}

close( DIGITEMP );

$dbh->disconnect;

i assume you have the $db_name and such values defined, and that you have a database created for the digitemp values?

Besides that, you are trying to read in a file, sort of keeping the program open. Why not first try it where you run it as a cron, every 5 minutes? By getting the values first and then only insert the last entry.

so:

$data = `$digitemp_binary -q -a -c $digitemp_rcfile`;

Then you have your data stored. Now you only want the value you want.

You can use split for example:

($water,$watervalue2,$watervalue3)=split(' ',$data);

Then you have $water, but you want watervalue2, or 3.. just pick..

then do the:

my $sql="INSERT INTO digitemp SET water=$watervalue2"; <- or another value.
print "SQL: $sql\n" if($debug);
$dbh->do($sql) or die "Can't execute statement $sql because: $DBI::errstr";
}

$dbh->disconnect;


then run that in a crontjob every 5 minutes, it will do the same. Its better to do it via crontab, so you dont have to start it after reboot, and it doesn't keep any memory, and it doesn't create any memory leacks since you kill the process once its done. (maybe a bit overkill, but its nice logic to keep in mind)

// Erik (binkey.nl)

thanks that works..

mysql> select * from digitemp;
+-------+---------------------+----------+
| dtKey | time | water |
+-------+---------------------+----------+
| 1 | 2007-12-12 22:08:33 | 36256230 |
| 2 | 2007-12-12 22:09:26 | 36256230 |
+-------+---------------------+----------+

Now I want an daily graph, what use in the hours, so i run it as cron job once an hour.
If I want to create a daily graphs with chardirector must I make an extra table in mysql what does a curret value minus last value divide 2, because my watermeter gives a pulse every half liter?

any help is agan welcome

You can grab the 25th entry (25 hours ago), then do a select on 24 hours.. but do a minus the 25th hour for every entry. Then you would have the number of pulses for each hour.

Instead of storing the "complete" number, you could also grab the last entry, when the crontab wants to insert the new one, take the last value from that entry before doing the sql.

You know what i mean? Perhaps there are better ways, but my sql could need some work ;-0

// Erik (binkey.nl)

I know what you mean, but my sql is also worse, also my perl

i wrote this, but it cannot behind the komma for halve liters, it is very amateur. I don#180;t know how to write it in perl and if it is that possible to gt correct values like 3.5, and that store it in mysql table

reading=`/usr/bin/digitemp -c /root/.digitemprc -a -q -r 1500`

# Diagnostic output
water1=`echo $reading | gawk '{print $3}'`
water2=`cat water.reading`
echo $reading | gawk '{print $3}' > water.reading
water3=`expr $water1 \- $water2`
deel=2
water=`expr $water3 \/ $deel`
echo $water

rrdtool fetch /var/lib/rrd/temperaturethingy/average.rrd AVERAGE -r 3600 -e `perl -e 'print int(time/3600)*3600;'` -s e-1d

another way.. dont know if there is any use for that.

so just tell me what you want in which format.. since your talking a bit dutch/english all together

// Erik (binkey.nl)

The last reply I don't get.

But to get back where i am. I was putting the values in the database. Its nice to put the right value of use a hour in the database.

I have your script:

my $debug = 0;

# Connect to the database
my $dbh = DBI->connect("dbi:mysql:$db_name","$db_user","$db_pass")
or die "I cannot connect to dbi:mysql:$db_name as $db_user - $DBI::errstr\n";

# Gather information from DigiTemp
# Read the output from digitemp

$data = `$digitemp_binary -q -a -c $digitemp_rcfile`;

($water,$watervalue2,$watervalue3)=split(' ',$data);

my $sql="INSERT INTO digitemp SET water=$watervalue3";
print "SQL: $sql\n" if($debug);
$dbh->do($sql) or die "Can't execute statement $sql because: $DBI::errstr";

$dbh->disconnect;

I want to do first run the $data, than $watervalue3 than minus this sql statement
my sql2="select water from digitemp order by time desc limit 1";

and that INSERT INTO digitemp SET verbruik=$watervalue3-$sql2

How do I add this correct to my script?

your almost there. You got the: my sql2="select water from digitemp order by time desc limit 1";
to get the value do something like:
$sql2->execute();
($oldread) $sql2->fetchrow_array();


then put the result in a value , say $oldread. Then just do $newreadout = $oldread - $watervalue3; That should work.

If not let me know.

// Erik (binkey.nl)