# A better way would be to prepare you statement only once
# $dbh->do runs a prepare everytime, so that is un-needed processing time for the DBMS
$sql = "INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),?,?,?,?,?,?,?,?,?,?)";
$sth = $dbh->prepare($sql);
while (<FHD>) {
chomp; #removes \n
chop; #removes trailing pipe
@line = split(/\|/, $_, 502); #The line has 502 "fields" so
#them into an array
$sth->execute($line[0],$line[4],$line[5],$line[6],$line[10],$line[11],$line[14],$line[18],$line[22],$line[25]);
$dbh->commit();
}
$sth->finish();
---- This is what you wrote me ----
:Hello all,
:
:I'm writing a script that will read pipe delimited data from a text file
:and insert various fields into a Postgres table. Below is some code I'm
:trying to optimize:
:
:
:while (<FHD>) {
: chomp; #removes \n
: chop; #removes trailing pipe
:
: @line = split(/\|/, $_, 502); #The line has 502 "fields" so
: #them into an array
: $dbh->do("INSERT INTO cdl_16master VALUES(nextval('cdl_16_seq'),\'" .
:join("\',\'",
:$line[0],$line[4],$line[5],$line[6],$line[10],$line[11],$line[14],$line[18],$lin
:e[22],$line[25]) . "\')");
: $dbh->commit();
:
:} #end while
:
:
:Just wondering if anyone has a better way of accessing the data in the
:array or of storing the few fields I need temporarily until it gets
:inserted into the database.
:
:There's a better way to do this, but I'm just not thinking right.....any
:suggestions are appreciated.
:
:Thanks,
:
:Kevin
:kold@carolina.rr.com
:
:
:
:---------------------------(end of broadcast)---------------------------
:TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
:
