create mysql database how

Forum regarding Linux Software and Home Automation Domotica.

create mysql database how

Postby Kroonen » Mon Dec 10, 2007 7:25 pm

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
Kroonen
Member
Member
 
Posts: 302
Joined: October 2007
Location: Netherlands

create mysql database how

Postby Snelvuur » Mon Dec 10, 2007 9:07 pm

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)
User avatar
Snelvuur
Forum Moderator
Forum Moderator
 
Posts: 3155
Joined: April 2007
Location: Netherlands

create mysql database how

Postby Kroonen » Wed Dec 12, 2007 7:23 pm

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;
Kroonen
Member
Member
 
Posts: 302
Joined: October 2007
Location: Netherlands

create mysql database how

Postby Snelvuur » Wed Dec 12, 2007 8:49 pm

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)
User avatar
Snelvuur
Forum Moderator
Forum Moderator
 
Posts: 3155
Joined: April 2007
Location: Netherlands

create mysql database how

Postby Kroonen » Wed Dec 12, 2007 10:23 pm

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
Kroonen
Member
Member
 
Posts: 302
Joined: October 2007
Location: Netherlands

create mysql database how

Postby Snelvuur » Thu Dec 13, 2007 1:13 pm

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)
User avatar
Snelvuur
Forum Moderator
Forum Moderator
 
Posts: 3155
Joined: April 2007
Location: Netherlands

create mysql database how

Postby Kroonen » Thu Dec 13, 2007 7:22 pm

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
Kroonen
Member
Member
 
Posts: 302
Joined: October 2007
Location: Netherlands

create mysql database how

Postby Snelvuur » Thu Dec 13, 2007 9:14 pm

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)
User avatar
Snelvuur
Forum Moderator
Forum Moderator
 
Posts: 3155
Joined: April 2007
Location: Netherlands

create mysql database how

Postby Kroonen » Fri Dec 14, 2007 10:47 pm

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?
Kroonen
Member
Member
 
Posts: 302
Joined: October 2007
Location: Netherlands

create mysql database how

Postby Snelvuur » Sun Dec 16, 2007 7:24 pm

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)
User avatar
Snelvuur
Forum Moderator
Forum Moderator
 
Posts: 3155
Joined: April 2007
Location: Netherlands


Return to Linux Forum

Who is online

Users browsing this forum: No registered users and 1 guest