how to check whether data inserted into database

Question

i have written a perl script for inserting data from a text file to database but i want to know how to add a quality check to it ie hw can i check the data inserted into database or not ie it should display saying the data has been inserted successfully.. and also when the date had been inserted from text to database it just displays 0000-00-00...what is the changes to be done...

my code is--

#!/usr/bin/perl 

#---------------------------------------------------------------------
# Description: Extract Lab data from text file and insert to database
#---------------------------------------------------------------------

# Modules Required
use DBI;  # check drivers


#print "vs2-001-001-ma-sampleFile\n";


my $filename = "vs2-001-001-ma-sampleFile.txt";   

#initialize variable $count
my $count = 0 ;    
#initialise variables for parameters
my ($paraval, $paraname, $pararange, $paraunit);
#uncomment it To use keyboard input. and type filename with extension
# Ex: fileName.txt or fileName.csv    
#chomp($filename=<>);       
open (OUT,">>$filename.csv") || die print "No\t $!";
close OUT;

open (IN,"$filename") || die print "Noo Input. $!";
my @file=<IN>;

#join the lines with # dilimits
my $string = join('#', @file);

    $string =~s /[\r]//g;   # To remove space. 
    $string =~s /[\n]//g;
    $string =~s /[\t]//g;   # To remove tab


print "\n Parsing data now....\n";
# pattern under while loop will do the work. 
# it will take date as 13 Oct 2010 in $1 and rest values in $2  
# $string=~/Equine Profile Plus\s+#(.*?\s+)\s+.*?(Sample.*)##/g

 while($string=~/Equine Profile Plus\s+#(.*?\s+)\s+.*?(Sample.*?)##/g)
 {
    my($date,$line,$Sample_Type,$Patient_ID, $Sample_Id,
         $Doctor_Id,$Location,$Rotor, $Serial,$para,
         $QC,$HEM,$LIP,$ICT);
    $count++;

    $date=$1;
    $line=$2;       
    if ($line=~/Sample Type:(.*?)#/gis){
        $Sample_Type=clean($1);
    }if ($line=~/Patient ID:(.*?)#/gis){
        $Patient_ID=clean($1);
    }if ($line=~/Sample ID:(.*?)#/gis){
        $Sample_Id=clean($1);
    }if ($line=~/Doctor ID:(.*?)#/gis){
        $Doctor_Id=clean($1);
    }if ($line=~/Location:(.*?)#/gis){
        $Location=clean($1);
    }if ($line=~/Rotor Lot Number:(.*?)#/gis){
        $Rotor=clean($1);
    }if ($line=~/Serial Number:(.*?)#/gis){
        $Serial=clean($1);
    }if ($line=~/#(NA+.*?GLOB.*?)#/gis){

        $para=$1;
        $para =~ s/#/;/g;
        $para =~ s/\s\s/ /g;   #remove spaces.
        $para =~ s/\s\s/ /g;   
        $para =~ s/\s\s/ /g;  
        $para =~ s/\s\s/ /g;
        $para =~ s/\s\s/ /g;
        $para =~ s/\s\s/ /g;
        $para =~ s/ /:/g;

        if ($line=~/#QC(.*?) #HEM(.*?) LIP(.*?) ICT(.*?)  /gis){
        $QC=clean($1);
        $HEM=clean($2);
        $LIP=clean($3);
        $ICT=clean($4);
    }
        while($para =~ /(.*?):(.*?):(.*?);/g){
        $paraname = $1;
        $paraval = $2;
        $pararange = $3;
        #$paraunit = $4;    

               #data from text file written to a CSV file.
        open (OUT,">>$filename.csv") || die print "No";             
               print OUT "\"$count\",\"$date\",\"$Sample_Type\",\"$Patient_ID\",
                   \"$Sample_Id\",\"$Doctor_Id\",\"$Location\",\"$Rotor\",
                   \"$Serial\", \"$QC\",\"$HEM\",\"$LIP\",\"$ICT\",
                   \"$paraname\",\"$paraval\",\"$pararange\",\n";
        }
    }
 }
 close OUT;

 #Load csv into mysql
print "\n Inserting into data base \n";
# comment it while not loading into the database.

&loaddata('$filename.csv');      
print "\n Database insert completed \n";
 sub clean
{
my ($line) = shift (@_);
$line =~ s/\n//g;
$line =~ s/\r//g;
$line =~ s/^\s+//g; 
$line =~ s/\s\s//g;
$line =~ s/\s+$//g;
$line =~ s/#//g;
return ($line);
}



#init the mysql DB
sub init_dbh{

$db="parameters";
$host="localhost";
$user="**";
$password="**";

my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                          $user,
                          $password)
                          or die "Can't connect to database: $DBI::errstr\n";

       return $dbh;

}

#Load data to mysql table
sub loaddata{
        my ($name) = @_;
        my $DBH = init_dbh( );
        my $STH_GO = $DBH->prepare(q{
           LOAD DATA LOCAL INFILE 'vs2-001-001-ma-sampleFile.txt.csv' 
            INTO TABLE parameter FIELDS TERMINATED BY ',' ENCLOSED BY
            '"' LINES TERMINATED BY '\n'; })or die "ERROR: ". $DBI::errstr;
        $STH_GO->execute();

        }

Answer 1

Check the return value of execute, for one thing.

Answer 2

I usually load the data programmatically from my code rather than rely on the database to load it. That way I can validate the records before inserting. The other advantage is that I know if a record fails to insert and have the option of trying to figure out what the problem is and retrying the insertion, or pushing the record to another file for a manual inspection later.

In your code you're processing the data then pushing it back to a file for the DB to load. Why not load the data rows as you process them? Letting the database do a bulk load is faster but it doesn't give good granularity; Usually it's an all or nothing thing, and if it's nothing your returned error won't tell you a lot except the file didn't load.

You're also slurping the file into memory so I recommend you read PerlFaq 5, which has a good section on How can I read in an entire file all at once?. The Perl Slurp Ease page probably has more than you want to know about it.