According to execute_array in DBI, it sounds very much like this will accomplish what you want. Can you show us the code you tried to implement this that failed? Caveat: I've never tried to do this.
Update: I did a little prototyping for my own edification based on the code examples in the above link. I successfully got column-wise insertions with:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $db = DBI->connect("dbi:SQLite:dbname=junk.db","","",
{
PrintError => 0,
RaiseError => 0,
});
$db->do(<<EOSQL);
DROP TABLE content
EOSQL
$db->do(<<EOSQL) or die "Table create failed: ", $db->errstr;
CREATE TABLE content (
one VARCHAR2(10) NOT NULL,
two VARCHAR2(10) NOT NULL,
three VARCHAR2(10) NOT NULL,
four VARCHAR2(10) NOT NULL,
CONSTRAINT pk PRIMARY KEY (one)
)
EOSQL
my @data = (['a' .. 'd'],
['e' .. 'h'],
['i' .. 'l'],
['m' .. 'p'],
);
my $query = $db->prepare(<<EOSQL);
INSERT INTO content ( one, two, three, four )
VALUES (?,?,?,?)
EOSQL
my $tuples = $query->execute_array(
{ ArrayTupleStatus => \my @tuple_status },
@data,
);
print $query->errstr if defined $query->errstr;
if ($tuples) {
print "Successfully inserted $tuples records\n";
} else {
for my $tuple (0 .. @tuple_status-1) {
my $status = $tuple_status[$tuple];
$status = [0, "Skipped"] unless defined $status;
next unless ref $status;
printf "Failed to insert (%s, %s): %s\n",
$data[0][$tuple], $data[1][$tuple], $data[2][$tuple], $dat
+a[3][$tuple], $status->[1];
}
}
If instead you have a row-wise data structure, you can swap the above execute_array for:
my $tuples = $query->execute_array(
{ ArrayTupleStatus => \my @tuple_status,
ArrayTupleFetch => sub { return shift @data; },
},
);
|