CODEWITHSUNDEEP
perl
Rahul Singh
Rahul Singh

Reputation: 13

update a column in input file by taking value from Database in perl

input file:

1,a,USA,,
2,b,UK,,
3,c,USA,,

i want to update the 4th column in the input file from taking values from one of the table.

my code looks like this:

my $number_dbh = DBI->connect("DBI:Oracle:$INST", $USER, $PASS ) or die "Couldn't
connect to datbase $INST";
my $num_smh;
print "connected \n ";
open FILE , "+>>$input_file" or die "can't open the input file";
print "echo \n";
while(my $line=<FILE>)
{
   my @line_a=split(/\,/,$line);
   $num_smh = $number_dbh->prepare("SELECT phone_no from book where number = $line_a[0]");
   $num_smh->execute() or die "Couldn't execute stmt, error : $DBI::errstr";
   my $number = $num_smh->fetchrow_array();
   $line_a[3]=$number;
}

Upvotes: 0

Views: 377

Answers (2)

Nylon Smile
Nylon Smile

Reputation: 9436

Looks like your data is in CSV format. You may want to use Parse::CSV.

Upvotes: 1

CanSpice
CanSpice

Reputation: 35808

+>> doesn't do what you think it does. In fact, in testing it doesn't seem to do anything at all. Further, +< does something very strange:

 % cat file.txt
1,a,USA,,
2,b,UK,,
3,c,USA,,
 % cat update.pl
#!perl

use strict;
use warnings;

open my $fh, '+<', 'file.txt' or die "$!";
while ( my $line = <$fh> ) {
    $line .= "hello\n";
    print $fh $line;
}
 % perl update.pl
 % cat file.txt
1,a,USA,,
1,a,USA,,
hello
,,
,,
hello
 %

+> appears to truncate the file.

Really, what you want to do is to write to a new file, then copy that file over the old one. Opening a file for simultaneous read/write looks like you'd be entering a world of hurt.

As an aside, you should use the three-argument form of open() (safer for "weird" filenames) and use lexical filehandles (they're not global, and when they go out of scope your file automatically closes for you).

Upvotes: 0

Related Questions