CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Problem with loading data into table from file

 



vinayaseshu
New User

Sep 8, 2010, 7:52 AM

Post #1 of 2 (2157 views)
Problem with loading data into table from file Can't Post

Hi Everyone,

I'm new to Perl scripting .I'm facing small problem while loading data from file into table.
i need to create Perl script for loading data from file with "|" separated into oracle table.
At the end of process,
i need to display statistics like
Total Number of records in file :- 10
total successfull records : 6
Total failed records : 4

Now i'm not able to print statistics at the end of process.
for your reference i have attached script i have written for this.
Please help me on this,i need this as soon as possible.

Thanks in advance.
Vinay
Attachments: loadfilelinebyline.pl (1.30 KB)


1arryb
User

Sep 8, 2010, 5:06 PM

Post #2 of 2 (2146 views)
Re: [vinayaseshu] Problem with loading data into table from file [In reply to] Can't Post

Hi, Vinayaseshu,

My comments in bold.

Code
#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

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives