#!/usr/bin/perl -w
use strict;
use DBI;
use Text::CSV;
use Getopt::Long;

use constant USAGE => "usage: $0 [ -h hostname ] [ -p port ] --db dbname -u user -p passwd -t table file.csv [ file.csv ... ]";

GetOptions(
    'h|host=s'  => \( my $opt_host = "localhost" ),
    'p|port=i'  => \( my $opt_port = "3306" ),
    'db=s'      => \( my $opt_dbname ),
    'u|user=s'  => \( my $opt_user ),
    'p|pass=s'  => \( my $opt_pass ),
    't|table=s' => \( my $opt_table ),
) or die USAGE;

die USAGE
    if not( $opt_dbname and $opt_user and $opt_pass and $opt_table );

my $dbh = DBI->connect(
    "dbi:mysql:dbname=$opt_dbname;host=$opt_host;port=$opt_port",
    $opt_user,
    $opt_pass,
    { RaiseError => 1, PrintError => 0 }
) or die "Connection error: $DBI::errstr";

my $csv = Text::CSV->new();

my $sth = do {
    $csv->parse( scalar <> ); # first line
    my @field = $csv->fields();
    my $sql =
        "INSERT INTO $opt_table ("
        . join( ',', @field )
        . ") VALUES ("
        . join( ',', ( '?' ) x @field )
        . ")";
    print STDERR "Using query: $sql\n";
    $dbh->prepare( $sql );
};

# # broken
# while( <> ) {
#    $csv->parse( $_ ) or next;
#    $sth->execute( $csv->fields );
# }

my ( $buff, $lnr );

while( <> ) {
   $buff .= $_;
   if( $csv->parse( $buff ) ) {
       $sth->execute( $csv->fields );
       $buff = '';
       $lnr = $. + 1;
   }
}

die "Error on line $lnr\n" if length $buff;