• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1424
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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