Solved

Perl DBI Insert Array of Arrays

Posted on 2010-11-09
3
1,241 Views
Last Modified: 2012-05-10
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
Comment
Question by:_KrYPt0_
  • 2
3 Comments
 

Author Comment

by:_KrYPt0_
ID: 34108052
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
 
LVL 16

Expert Comment

by:jmatix
ID: 34115730
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
 
LVL 16

Accepted Solution

by:
jmatix earned 250 total points
ID: 34115762
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now