#!/usr/bin/perl -w
#
#
use strict;
use DBI qw(:sql_types);


####
# Set global dbase connection info
#
#
####

my $dsn="dbi:mysql:database=sfiemsmerc";
my $user="root";
my $passwd="";

my $dbh = DBI->connect($dsn, $user, $passwd, { RaiseError =>1, PrintEr
+ror => 1, AutoCommit => 0 });
$dbh->trace(1);

my @files = <*.dump>;
foreach my $file (@files) {
    my $table = "$file";
    $table =~ s/\.dump//g;

####
# Delete current data from hobby table
# Separate connection!!
#
####

my $delete00 = $dbh->prepare("DELETE FROM $table");
$delete00->execute();

$dbh->commit();

####
# create and prepare insert connection and action
# call q_marks to insert "?" into $stmnt
# 
#
####

$dbh = DBI->connect($dsn, $user, $passwd, { RaiseError => 1, PrintErro
+r => 1, AutoCommit => 0 });

$dbh->trace(1);


my $csvfile="$file";
open (CSV2,"$csvfile")|| die "Cannot open $csvfile, $!";
while (<CSV2>) {
    chomp;
    if ( /rows$|^$table/ ) {
        next;
    }else{
        s/'//g;
        my (@data) = split /,/;
        chomp @data;
        my $stmnt = "INSERT INTO $table VALUES ()";
        $stmnt = q_marks($stmnt,\@data);

        my $insert00 = $dbh->prepare("$stmnt");
        
        my $count = @data;
        my @values;
        for (my $i = 0;$i <= $count - 1;++$i) {
            my $word = "$data[$i]";
            unshift(@values,$word);
            }
        $count = @values;
        if (! $count ) {
            next;
        }else{
            my $values = join(',',reverse(@values));
            chomp $values;
            #print "$values\n";
            $insert00->execute("$values");
            }
        }

####
# Close and commit dbase changes
#
#
####

close CSV2|| die "Cannot close $csvfile, $!";
$dbh->commit();
$dbh->disconnect();
}

####
# 
#
####
#unlink $csvfile|| die "Cannot remove $csvfile, $!";
#unlink $csv2file|| die "Cannot remove $csv2file, $!";
}

my $date = localtime time;
print "$date:hobby_update:Update Complete\n";

sub q_marks {
    my($stmnt,$data) =@_;
    my @marks;
    foreach (@$data) {
        unshift(@marks,"?");
        }
    my $line = join(',',@marks);
    $stmnt =~ s/(\()(\))/$1$line$2/;
    return $stmnt;
    }
[download]

There are four data fields that need to be inserted. The data files are comma-delimited, one row per line, "'" qualified. I'm stripping the "'" qualifier. Do I need to bind_param the fields? If so, what would be a good way to do that? Any suggestions anyone has would be greatly appreciated. Thanks, Dalin Where ever there is confusion to be had... I'll be there.

Edit kudra, 2002-04-16 Added readmore