RE: Inserting data file into table

[Kipp, James]

> > I slurped a data file into an array and I know there are 
> many ways of
> > inserting the fields into a table. I was just wondering 
> what the most
> > efficient way would be.
> > Here are the relevent code snippets.
> > --
> > my $id, $fname, $lname, $phone, $deptid, @table;
> > my $file = "c:\\test\\people.csv";
> > open(FILE, "<$file") or die "can't open file: $!";
> > while (<FILE>) {
> >         @table = split(/,/ , $file );
> > --
> > my $sth = $dbh->prepare( "INSERT INTO tbl_people VALUES 
> (?,?,?,?,?)" );
> >
> > foreach (@table){
> >         ($id, $fname, $lname, $phone, $deptid) = split;
> >         $sth->execute($id, $fname, $lname, $phone, $deptid);
> > }
> > --
> >
> > or should I us a do() or something else.
> >
> 
> That's about as good as it gets if you use DBD::Informix.
> 
> YMMV if you use a different database driver, but probably not by much.

Yes, thanks. I found a blurb in the dbi book about the disavantages of do(),
so I am sticking with the above. Here is the revised code in case anyone
else need to do something sililar:

use strict;
use DBI;

my ($id, $fname, $lname, $phone, $deptid, @table);
my $dbh = DBI->connect("dbi:Oracle:prj_test.ftbee01a","xxxxx","xxxxxx")
  or die "Cannot open $DBI::errstr\n";

my $file = "c:\\test\\people.csv";
open(FILE, "<$file") or die "can't open file: $!";

@table = <FILE>;

my $sth = $dbh->prepare( "INSERT INTO tbl_people VALUES (?,?,?,?,?)" )
        or die "Cannot prepare SQL statements from $DBI::errstr\n";

foreach (@table){
        chomp;
        ($id, $fname, $lname, $phone, $deptid) = split( /,/ );
        $sth->execute($id, $fname, $lname, $phone, $deptid);
}