[pm-h] DBI Bulk Inserts and Updates

[Michael R. Davis]

Todd,
>> Our DBA would like for me to investigate bulk inserts. 
> Can you provide code snippets or an example of what you're trying to do?
 
It looks like DBI supports execute_for_fetch.  To me it appears that it was built for pushing data from one database to another. 
 
Anyway, I put this method together.  It should be database portable as it uses DBI not DBD but DBDs can optimize the call under the hood which Oracle states that it does.
 
Here's a method I put together to translate the data format that I use. The method uses DBIx::Array under the hood. 
=head2 bulkinsertarrayarrayname
Insert records in bulk.
  my @arrayarrayname=(
                      ["Col1", "Col2", "Col3", "Col4", ...],
                      [data1, $data2, $data3, $data4, ...],
                      [@row_data_2],
                      [@row_data_3], ...
                      [@row_data_n],
                     );
  my $count=$dbx->bulkinsertarrayarrayname($table, \@arrayarrayname);
=cut

 
sub bulkinsertarrayarrayname {
  my $self=shift;
  my $table=shift or die("Error: table name required.");
  my $arrayarrayname=shift;
  die('Error: $arrayarrayname parameter must be an array reference') unless ref($arrayarrayname) eq "ARRAY";
  my $columns = shift @$arrayarrayname;
  die("Error: columns must be array reference") unless ref($columns) eq "ARRAY";
  my $sql     = sprintf("INSERT INTO $table (%s) VALUES (%s)", join(",", @$columns), join(",", map {"?"} @$columns));
  my $sth     = $self->dbh->prepare($sql) or die($self->errstr);
  my $size    = @$arrayarrayname;
  my @tuple_status=();
  my $count   = $sth->execute_for_fetch( sub {shift @$arrayarrayname}, \@tuple_status);
  unless ($count == $size) {
    warn map {"$_\n"} @tuple_status;
  }
  return $count;
}

 My tests for DBD::CSV and DBD::XBase pass with no issues.
 
  $dba->dbh->do("CREATE TABLE $table (F1 INTEGER,F2 CHAR(1),F3 VARCHAR(10))");
  is($dba->bulkinsertarrayarrayname($table, [[qw{F1 F2 F3}], [0,1,2], [1,2,3], [2,3,4]]), 3, 'bulkinsertarrayarrayname');

Now onto performance testing!  
 
Thanks,
Mike
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/houston/attachments/20140417/c83f0e69/attachment.html>