
1arryb
User
Sep 8, 2010, 5:06 PM
Post #2 of 2
(4768 views)
|
Re: [vinayaseshu] Problem with loading data into table from file
[In reply to]
|
Can't Post
|
|
Hi, Vinayaseshu, My comments in bold.
#c:\perl\bin use DBI; use strict; use warnings; #extablishing connection to database my $dbname = "oracle11gr2"; my $dbuser = "scott"; my $dbpass = "tiger"; # AutoCommit => 1 obviates the commit statements in your code. # RaiseError => 1 ensures DBI will 'die' if a statement fails (so you can test for $@ after eval {}). my $dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser, $dbpass, { AutoCommit => 1, RaiseError => 1 } ) || die "Unable to connect to $dbname: $DBI::errstr\n"; #truncating table for fresh loading # No need to prepare single-use statments. eval { $dbh->do("truncate table emp_test"); }; if ( $@ ) { die "DBI threw an exception attempting to truncate emp_test!: $@"; } #declaring required variables. my $no=""; my $abc=""; my $ins =""; my $inssth=""; my $totalrecords=0; my $success=0; my $fail=0; #looping through all rows of data and insert into table my $data_file="./examples/names.txt"; open(NAMES, $data_file) || die("Could not open file!"); # There's no benefit to using prepared statements unless you # re-use them. $inssth = $dbh->prepare("insert into emp_test values(?,?)"); foreach my $line (<NAMES>) { $totalrecords +=1; chomp($line); ($no,$abc)=split(/\|/,$line); eval { $inssth->execute($no, $abc); }; # Here are your missing stats. if ( $@ ) { # Error print STDERR "DBI threw an exception inserting values ($no, $abc): $@\n"; $fail++; } else { # Success $success++; } } # Tidy is as tidy does. close(NAMES); print "total Number of records in file is: " . $totalrecords . "\n"; print "total Number of successfull loaded records is: " . $success . "\n"; print "total Number of failed records from file is: " . $fail , "\n"; #commiting at end of program Cheers, Larry
|