Re: Stream data into Postgres via Perl

# 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(at)carolina(dot)rr(dot)com
:
:
:
:---------------------------(end of broadcast)---------------------------
:TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
: