execute_array and iterations....

From: Amonotod (amonotod_at_charter.net)
Date: 09/30/04 

To: <dbi-users@perl.org>
Date: Thu, 30 Sep 2004 14:38:09 +0000

Hello all,
  I'm still having just a bit of trouble with execute_array. I want to parse my input file, break it apart with Text::CSV_XS (thanks for the great recommendation!), then build arrays corresponding to each column. Everything works great, it really does.

  But, I don't want to parse the entire file before I start sending it to the DB. I'd like to send, say, 500 rows at a time, and then commit after every 5000. So, I used the following script:

while (<TEXT_DATA>) {
  if ($csv->parse($_)) {
    my @rets = $csv->fields;
    $colcount = 0;
    foreach my $col (@cols) {
      my $ret = $rets[$colcount];
      if ($ret eq "") { push (@{$insArrays{$colcount}}, undef); }
      else { push (@{$insArrays{$colcount}}, $ret); }
      $colcount++;
    }
    # Do we have the required number of rows?
    if ($insCount == $sendRows) {
      print "\tAdding $insCount rows...\n";
      my $temp_count = 0;
      while ($temp_count < $colttl) {
        # Bind the arrays to the columns...
        $DataInsert->bind_param_array($temp_count +1, \@{$insArrays{$temp_count}});
        $temp_count++;
      }
      unless ($DataInsert->execute_array( { ArrayTupleStatus => \my @tuple_status } ) ) {
        print LOGFILE "\nErrors were encountered during data load...\n";
        print LOGFILE "errors: $dbh->errstr \n";
      }
      # Set the row count back to zero
      $insCount = 0;
      $temp_count = 0;
      # Clear the arrays out now...
      while ($temp_count < $colttl) {
        $insArrays{$temp_count} = undef();
        # Bind an alternate value to the columns...
        $sth->bind_param_array($temp_count+1, 'NULL');
        $temp_count++;
      }
    }
    if ($commitCount == $commitNum) {
      print LOGFILE " Sent $commitCount rows of data...\n";
      $dbh->commit; $commitCount = 0;
    }
    $insCount++;
    $commitCount++;
  }
}
# Do we have any rows left over?
if (($insCount > 0) && ($insCount < $sendRows)) {
  # If we're here, we need to insert the remaining rows...
  print "\tAdding $insCount rows...\n";
  my $temp_count = 0;
  while ($temp_count < $colttl) {
    # Bind the arrays to the columns...
    # Fails here, complains that column 1's array is smaller than column 4's...
    $DataInsert->bind_param_array($temp_count +1, \@{$insArrays{$temp_count}});
    $temp_count++;
  }
  unless ($DataInsert->execute_array( { ArrayTupleStatus => \my @tuple_status } ) ) {
    print LOGFILE "\nErrors were encountered during data load...\n";
    print LOGFILE "errors: $dbh->errstr \n";
  }
  print "\tDone...\n";
  $insCount = 0;
  $temp_count = 0;
  while ($temp_count < $colttl) {
    @{$insArrays{$temp_count}} = undef;
    $temp_count++;
  }
}
if ($commitCount < $commitNum) { $dbh->commit; }

  As you can see in the comments, after the bulk of the file has been passed to the DBI, and the remainder gets passed, the DBI complains that the remaining arrays are too small. And the thing is, they ARE smaller, they're the remainder. Do I need to, maybe, commit what I've got so far, close my statement handle, open a new statement handle, insert the remainder, and then commit that part? Boy, it seems like it should be easier...

Thanks,
amonotod 

--
    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|

Relevant Pages

  • Re: Open an existing Word file?
    ... > I wrote a VB.net program that import a text file, which I will parse and ... > place into arrays. ... For office automation using .NET, ... M S Herfried K. Wagner ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Manipulating XML in Perl
    ... automatically create arrays where there are more than one element. ... So depending on the data you parse .. ... XML parser modules. ...
    (perl.beginners)
  • Open an existing Word file?
    ... I wrote a VB.net program that import a text file, which I will parse and ... place into arrays. ... My question is how to open an existing Word file without the Dialog Box that ... CopynPaste ...
    (microsoft.public.dotnet.languages.vb)