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_______________________________________________
Houston mailing list
[email protected]
http://mail.pm.org/mailman/listinfo/houston
Website: http://houston.pm.org/
