Comparing SQL Data. 1

[maximo1970]

I'll try and explain the best I can on this one. I have a MySQL table (tbl_disk_space) that I want to populate :-

Device_Name Drive Size Used Free
server1 C
server1 D
server2 C 10 1 9
server2 D 5 4 1

My data source is split into the $drive $size $used $free $hostname

I want to select all of the data in the columns Device_Name and Drive from tbl_disk_space so that I can then check if the Device_Name matches $hostname and Drive matches $drive. I can then either insert or update the data in tbl_disk_space. I now that I should be able to do something like this using fetchall_arrayref but just can't get my head around it.

Any pointers would be great.

Thanks.

 

[philote]

After you've made your database connection and gotten your database handle, use it to prepare your statement and get a statement handle. With that you execute the statement. Then you can get each row with, as you said, fetchrow_arrayref. So to get all the rows, you have to loop until fetchrow_arrayref no longer returns a row. You can do this with a while statement. Then test the drive and device name and update or add as appropriate. Here's an example:

...
my $sth = $dbh->prepare("select Device_Name, Drive from tbl_disk_space");
$sth->execute;
while(my $row = $sth->fetchrow_arrayref){
  if($hostname eq $row->[0] && $drive eq $row->[1]){
    &updaterow($drive, $size, $used, $free, $hostname); 
  }
  else{&addrow($drive, $size, $used, $free, $hostname);}
}

I'll leave the adding/updating of the records for you to work on.

 

[maximo1970]

Got my code working but it's causing a number of duplicate errors when passing the data to my table, it clearly something todo with the way my code is looping. Any ideas would be most welcome.

my $srvinfo;

my @srv;

my $sth = $dbh->prepare("SELECT Device_Name, IP_Address FROM tbl_admin_systems WHERE Current_Ping_status LIKE 'ALIVE'"
$sth->execute();

while(@systemsrow = $sth->fetchrow_array()) {
print "Collecting Disk Space Data From @systemsrow[0]\n";

foreach my $line (@systemsrow[1]) {
my $srvinfo = "srvinfo -ns \\\\@systemsrow[1]";

my @info = `$srvinfo`;

my @volinfo = grep (/\$/, @info);

my $sth = $dbh->prepare("SELECT Device_Name, Drive FROM tbl_disk_space"

foreach my $server (@volinfo) {
chop($server);
(my $rubbish, my $drive, my $fs, my $size, my $free, my $used) = split(/\s+/, $server);
$drive =~ s/\$//;
my $tablestate = $dbh->do("SELECT Device_Name COUNT FROM tbl_disk_space"
if ($tablestate ne "0E0&quot {
$sth->execute();
while (my $row = $sth->fetchrow_arrayref()) {
if (@systemsrow[0] eq $row->[0] && $drive eq $row->[1]) {
$dbh->do("UPDATE tbl_disk_space SET Used=\"$used\", Size=\"$size\", Checked=\"$date $time\" WHERE Drive=\"$drive\" and Device_Name=\"@systemsrow[0]\""
} else {
$dbh->do("INSERT INTO tbl_disk_space SET Device_Name=\"@systemsrow[0]\", IP_Address=\"@systemsrow[1]\", Drive=\"$drive\", Used=\"$used\", Size=\"$size\", Free=\"$free\", Comments=\"New drive found. $date $time\", Checked=\"$date $time\""
}
}
} else {
$dbh->do("INSERT INTO tbl_disk_space SET Device_Name=\"@systemsrow[0]\", IP_Address=\"@systemsrow[1]\", Drive=\"$drive\", Used=\"$used\", Size=\"$size\", Free=\"$free\", Comments=\"New drive found. $date $time\", Checked=\"$date $time\""
}
}
}
}
$dbh->do("UPDATE tbl_disk_space SET Percent_Free=(Free/Size*100)"