http://qs321.pair.com?node_id=6742

Kiko has asked for the wisdom of the Perl Monks concerning the following question:

Hey, I got the script down to this: 
#!/perl/bin/perl
use Win32::ODBC;
use win32;
use CGI qw(:standard);

$data="data.csv";

my (@statement); # array of sql statements
my $db=new Win32::ODBC("DSN=test;UID=;PWD=");

### report connection status
if (!$db) {
    die "\nunable to connect to database! $!";
}

### Open File for Reading
open(FR, "< $data") || die "cannot open $data for reading: $!";

while(<FR>) {
    
    my ($d1, $d2, $d3, $d4, $d5, $d6, $d7, $d8) = split /\,/;
    ($d1) = "$d1\@test\.com";
    ($d2) =~ s/LBAM\///;
    ($d2) =~ s/LBAS\///;
    ($d2) =~ s/LBEU\///;

    ### Write to Database
    push @statement, "
    INSERT INTO NEW
        (mail_alias, NT_login, firstname, middle_init, surname, 
                 profit_loss, dept, org_unit_name)
    VALUES
        ('$d1', '$d2', '$d3', '$d4', '$d5', '$d6', '$d7', '$d8')
    ";
}

### Close the files
close(FR) || die "cant close $data: $!";

### Close the database
$db->Close();

[download]
And it still doesn't work. I don't know what's wrong because i'm not getting any errors. Can anyone help?

Replies are listed 'Best First'.
Re: Reading from a file
by chromatic (Archbishop) on Apr 03, 2000 at 23:23 UTC
    You're not doing anything with the SQL statements after you push them onto @statement. At the very least, do something like: 
    foreach my $statement (@statement) {
    if ($db->Sql($statement)) {
        print "Execution failed for $statement\n";
        # die?  Return?  Carry on?
    }
}

    [download]
[reply]
[d/l]
Re: Reading from a file
by Anonymous Monk on Apr 04, 2000 at 00:30 UTC
    Plus, that may not be a valid insert statement... Try:  insert into TABLE_NAME values (... Zak
[reply]
[d/l]
      Actually, that is a valid insert statement. The field names specified after the 'INSERT INTO NEW' specifies which fields to insert into, saving the insert statement from having to provide initial values for each field in the database, and making it a lot easier to manage if a field with a default were to get added later on.
[reply]

Back to Seekers of Perl Wisdom