#!/usr/bin/perl -wT

# batchload.pl
# reads a file a header containing database name, username, headers
# on successive lines, puts remaining lines in the database 

use DBI;
use strict;
use diagnostics;

my $delimiter = '^'

my $dbname = <>;
chomp $dbname;
my $user = <>;
chomp $user;
my $table = <>;
chomp $table;
my $headers = <>;
chomp $headers;
my @column_names = split /$delimiter/, $headers;

my $dbh = DBI->connect("DBI:Pg:dbname=$dbname", $user, '')
  or die("Unable to open database!\n$DBI::errstr\n");

# hackish - make enough placeholders for each column
my @placeholders;
foreach (@column_names) {
  push @placeholders, '?';
}

# interpolated arrays will be comma-space separated
$" = ', ';

my $sql = <<"EOSQL";
INSERT INTO $table (@column_names)
  VALUES (@placeholders);
EOSQL

# good for testing...
print STDERR "Preparing SQL statement:\n$sql\n";

my $sth = $dbh->prepare($sql) or die $dbh->errstr;

while (<>) {
  chomp;
  my @values = split /$delimiter/;
  print STDERR "Adding VALUES:\t@values\n";
  $sth->execute(@values) or die $dbh->errstr;
}

print STDERR "\n";

$dbh->disconnect;

syntax highlighted by Code2HTML, v. 0.8.11