• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1356
  • Last Modified:

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

0
_KrYPt0_
Asked:
_KrYPt0_
  • 2
1 Solution
 
_KrYPt0_Author Commented:
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

0
 
Justin MathewsCommented:
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

0
 
Justin MathewsCommented:
Since you are using strict, add my ($e, $tmp):
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);

my ($e, $tmp);
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

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now