Re^2: Using variables in a DBI do queriy

Yea the placeholders where a typo on my part, I'll admit that. If it helps clarify the situation I am wanting to reduce these queries down to one if possible:

$dbh->do("insert into batchpay (source,contract, invoice, substatus, o
+riginal,imported) values (?,?,?,?,?,?)", {}, "$dir-$filename", $data[
+1], $data[2], $status, $line, $datetime );
$dbh->do("insert into batchpay (source,transaction,contract,amount,app
+lied,account,substatus,original,imported) values (?,?,?,?,?,?,?,?,?)"
+, {}, "$dir-$filename", $data[1], $data[2], $data[3], $data[4], $data
+[7], $status, $line, $datetime );
$dbh->do("insert into batchpay (source,contract,amount,tax,applied,nam
+eonpayment,invoice,substatus,original,imported) values (?,?,?,?,?,?,?
+,?,?,?)", {}, "$dir-$filename", $data[1], $data[2], $data[3], $data[5
+], $data[6], $data[7], $status, $line, $datetime );
$dbh->do("insert into batchpay (source,nameonpayment,account,amount,ap
+plied,invoice,substatus,original,imported) values (?,?,?,?,?,?,?,?,?)
+", {}, "$dir-$filename", $data[1], $data[2], $data[4], $data[5], $dat
+a[6], $status, $line, $datetime );
[download]

Everything is in a while loop (looping through files within a directory) and each one of those queries is for a different file.

Below is snipplet of code as to what I am doing:

opendir my $fixeddirs, $directory or die "Can't open dir $directory: $
+!\n";
my @files = readdir($fixeddirs);
closedir $fixeddirs;
while (<@files>) {
    if ( ( $_ ne '.' ) and ( $_ ne '..' ) and ( $_ ne '.svn' ) ) {
    my $filename = $_;
    my $datname = $filename;
    $datname =~ s/\.txt//g;
    if ( $datname =~ /^BAYAHIBEELE/ ) {
        $datname = substr $datname, 0, 11;
    }
    my $length = q[];
    my $temp1 = q[];
    my $temp2 = q[];

    open my $datfile, '<', "/home/juice/juice38/connectors/bankimporte
+r/datafiles/$dir/$datname.dat" or die "Can't open dat file $datname.d
+at: $!\nPun intended\n";
    while (<$datfile>) {
        chomp;
        s/^\s*//;
        s/\s*$//;
        if ( $_ ne q[] ) {
            ( $length, $temp1, $temp2 ) = split ';', $_;
        }
    }
    close $datfile;

    my $rec = chr(0) x $length;

    my @garbage = split '\|', $temp1;
    my @fields1;
    my @type1l;

    my $i = 0;
    foreach (@garbage) {
        ($fields1[$i], $type1l[$i]) = split ':', $_;
        $i++;
    }
    
    my $n = 0;
    my @type1o = map{
        $n += $_; $n - $_;
    } @type1l;

    my @type1 = map \substr( $rec, $type1o[ $_ ], $type1l[ $_ ] ), 0 .
+. $#type1o;

    my @type2;
    my @fields2;
    if ( defined $temp2 ) {
        @garbage = split '\|', $temp2;
        my @type2l;

        $i = 0;
        foreach (@garbage) {
            ($fields2[$i], $type2l[$i]) = split ':', $_;
            $i++;
        }
        
        $n = 0;
        my @type2o = map{
            $n += $_; $n - $_;
        } @type2l;

        @type2 = map \substr( $rec, $type2o[ $_ ], $type2l[ $_ ] ), 0 
+.. $#type2o;
    }

    my ( $linelayout1, $linelayout2) = q[];
    open my $guide, '<', "/home/juice/juice38/connectors/bankimporter/
+datafiles/$dir/$datname" or die "Can't open guide file $datname: $!\n
+";
    while (<$guide>) {
        chomp;
        s/^\s*//;
        s/\s*$//;
        if ( $_ ne q[] ) {
            ( $linelayout1, $linelayout2 ) = split '\|';
        }
    }
    print "\nOpening file $directory/$filename...\n";
    open my $fixedfile, '<', "$directory/$filename" or die "Can't open
+ fixed file $directory/$filename: $!\n";
    my @data;
    my @names;
    my @layout;
    my $errorcount = 0;
    while (<$fixedfile>) {
        $_ = q[] if ( $. == 1 );
        if ( $_ ne q[] ) {
            substr( $rec, 0 ) = $_;
            if ( ! defined $temp2 ) {
                @data = map $$_, @type1;
                @names = @fields1;
                @layout = split ',', $linelayout1;
            } else {
                if ( /^03/ ) {
                    @data = map $$_, @type1;
                    @names = @fields1;
                    @layout = split ',', $linelayout1;
                } else {
                    @data = map $$_, @type2;
                    @names = @fields2;
                    @layout = split ',', $linelayout2;
                }
            }
            my $line = $_;
            foreach (@data) {
                chomp;
                s/\r|\n//g;
                s/^M//g;
                s/^\s*//;
                s/\s*$//;
                s/\'//g;
                s/\"//g;
                s/\\//g;
                s/\(//g;
                s/\)//g;
                s/\+//g;
                if ( $_ eq q[] ) {
                    $_ = 0;
                }
                if (/^\d+$/) {
                    $_ = $_ * 1;
                }
            }

            my $columns = q[];
            my $values = q[];
            my $placeholders = q[];
            foreach (@layout) {
                print "$names[$_]: $data[$_]\n";
                $columns .= "$names[$_],";
                $values .= "$data[$_],";
                $placeholders .= "?,";
            }
            $columns =~ s/,+$//;
            $values =~ s/,+$//;
            $placeholders =~ s/,+$//;
            print "$line\n\n";
            my $status = q[];
            $dbh->do("insert into batchpay (source,$columns,substatus,
+original,imported) values (?,?,?,?,$placeholders)", {}, "$dir-$filena
+me", $values, $status, $line, $datetime );
            }
        }
    }
    print "Data parsed with $errorcount errors\n";
    close $fixedfile;
}
[download]

Comment on Re^2: Using variables in a DBI do queriy Select or Download Code

Replies are listed 'Best First'.
Re^3: Using variables in a DBI do queriy by wind (Priest) on May 04, 2011 at 20:47 UTC
If you're trying to group those 4 queries into a single one, then I would do build the query programmatically. There are a couple things you have to worry about, not all the columns are being initialized in every insert, and it would be very easy to assign a value to the wrong field. I would therefore create a list of hashes that contain field name -> value pairs for each record you're wanting to create. Then compile the list of all possible field names and build the single insert statement. The following code should work for the sample code you provided, but obviously this is untested my %common = ( source => "$dir-$filename", substatus => $status, original => $line, imported => $datetime, ); my @records = ( { contract => $data[1], invoice => $data[2], %common, }, { transaction => $data[1], contract => $data[2], amount => $data[3], applied => $data[4], account => $data[7], %common, }, { contract => $data[1], amount => $data[2], tax => $data[3], applied => $data[5], nameonpayment => $data[6], invoice => $data[7], %common, }, { nameonpayment => $data[1], account => $data[2], amount => $data[4], applied => $data[5], invoice => $data[6], %common, }, ); my @fields = do { my %seen; grep {! $seen{$_}++} map {keys %$_} @records; }; my $fields = join ',', @fields; my $placeholders = '(' . join(',', ('?') x @fields). ')'; my $all_placeholders = join ',', ($placeholders) x @records; my @values = map {@{$_}{@fields}} @records; $dbh->do("insert into batchpay ($fields) values $all_placeholders", {} +, @values) or die $dbh->errstr; [download]	[reply] [d/l]
Re^4: Using variables in a DBI do queriy by vendion (Scribe) on May 05, 2011 at 14:26 UTC
This looks very interesting although I'm not quite sure I know what is going on in it, my major question is how would the hash key value pairs be correctly matched to the @data array is it set in the order that the files are read? so the first file read would match the: `{ contract => $data[1], invoice => $data[2], %common, },` [download] and so on?	[reply] [d/l]
Re^5: Using variables in a DBI do queriy by wind (Priest) on May 05, 2011 at 17:26 UTC
You showed me 4 insert queries that I assumed were ran sequentially one after another. If that's the case, than the code I provided would programmatically build the same queries, but run then in a single insert statement. Someone might want to do this if they were concerned about the number of database accesses and if there was an index on the table that was updated every time an insert was performed. If those insert statements you showed me are not actually run immediately, one after another as shown, than my sample code is meaningless. Either way mate, I believe I've helped you as much as I can.	[reply]
Re^6: Using variables in a DBI do queriy by vendion (Scribe) on May 05, 2011 at 18:14 UTC