asked on

Perl DBI Insert Array of Arrays

I am attempting to insert an array of arrays using a perl DBI statement.

I have tried both $sth->execute(@array) and $sth->execute_array(@array)

I know how to do this if I were to break the each sub-array into individual arrays, but the data is not being presented in that fashion.

What I have so far is:

use strict;
use warnings;
use DBI;

my @array = (
               ['NC', 1, 2, 3 ],
               ['SC', 4, 5, 6 ],
               ['NW', 7, 8, 9 ],
            );

my $dbh = DBI->connect("DBI:mysql:database", 
   username, password, {RaiseError => 1, PrintError => 0});

my $ins_q = "INSERT INTO Table VALUES 
    (NULL, CURRENT_DATE(), ?, ?, ?, ?)";

my $insert = $dbh->prepare($ins_q);
$dbh->execute(\@array);

Open in new window

_KrYPt0_

ASKER

So I came up with the following work around

my @array = (
               ['NC', 1, 2, 3 ],
               ['SC', 4, 5, 6 ],
               ['NW', 7, 8, 9 ],
            );

my $dbh = DBI->connect("DBI:mysql:database", 
   username, password, {RaiseError => 1, PrintError => 0});

my $ins_q = "INSERT INTO Table VALUES 
    (NULL, CURRENT_DATE(), ?, ?, ?, ?)";

my $insert = $dbh->prepare($ins_q);
foreach my $row (@array) {
  $dbh->execute(@{$row});
}

Open in new window

Justin Mathews

Another option is to use execute_array(). But then you will have to transpose the array as:

use strict;
use warnings;
use DBI;

my @array = (
               ['NC', 1, 2, 3 ],
               ['SC', 4, 5, 6 ],
               ['NW', 7, 8, 9 ],
            );

my $dbh = DBI->connect("DBI:mysql:database", 
   username, password, {RaiseError => 1, PrintError => 0});

my $ins_q = "INSERT INTO Table VALUES 
    (NULL, CURRENT_DATE(), ?, ?, ?, ?)";

my $insert = $dbh->prepare($ins_q);
for $e (0..$#array){$tmp=$array[$e][$_], $array[$e][$_]=$array[$_][$e], $array[$_][$e]=$tmp for ($e+1..$#{$array[$e]})};

$insert->execute_array(undef, @array);

$dbh->disconnect;

Open in new window

ASKER CERTIFIED SOLUTION

Justin Mathews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial