How do I insert values from parallel arrays into a database using Perl's DBI module?

Question

I need to insert values in database using Perl's DBI module. I have parsed a file to obtain these values and hence these values are present in an arrays, say @array1, @array2, @array3. I know how to insert one value at a time but not from an arrays.

I know insert one value at a time:

$dbh = DBI->connect("dbi:Sybase:server=$Srv;database=$Db", "$user", "$passwd") or die "could not connect to database";
$query= "INSERT INTO table1 (id, name, address) VALUES (DEFAULT, tom, Park_Road)"; 
$sth = $dbh->prepare($query) or die "could not prepare statement\n";
$sth-> execute or die "could not execute statement\n $command\n";

I am not sure if I have array1 containing ids, array2 containing names, and array3 containing address, how would I insert values.

Answer 1

How could you not be sure what your arrays contain? Anyway the approach would be the iterate through one array and assuming the other arrays have corresponding values put those into the insert statement

Answer 2

Use placeholders.

Update: I just realized you have parallel arrays. That is really not a good way of working with data items that go together. With that caveat, you can use List::MoreUtils::each_array:

#!/usr/bin/perl

use strict; use warnings;

use DBI;
use List::MoreUtils qw( each_array );

my $dbh = DBI->connect(
    "dbi:Sybase:server=$Srv;database=$Db", 
    $user, $passwd, 
) or die sprintf 'Could not connect to database: %s', DBI->errstr;

my $sth = $dbh->prepare(
    'INSERT INTO table1 (id, name, address) VALUES (?, ?, ?)'
) or die sprintf 'Could not prepare statement: %s', $dbh->errstr;

my @ids = qw( a b c);
my @names = qw( d e f );
my @addresses = qw( g h i);

my $it = each_array(@ids, @names, @address);
while ( my @data = $it->() ) {
    $sth->execute( @data )
        or die sprintf 'Could not execute statement: %s', $sth->errstr;
}

$dbh->commit
    or die sprintf 'Could not commit updates: %s', $dbh->errstr;

$dbh->disconnect;

Note that the code is not tested.

You might also want to read the FAQ: What's wrong with always quoting "$vars"?.

Further, given that the only way you are handling error is by dying, you might want to consider specifying { RaiseError => 1 } in the connect call.

Answer 3

Another way would be to use a hash as an intermediate storage area. IE:

my $hash = {};
foreach(@array1) {
  $hash->{id} = $array1[$_];
  $hash->{name} = $array2[$_];
  $hash->{address} = $array3[$_];
}
foreach( keys %$hash ) {
  $sql = "insert into table values(?,?,?)";
  $sth = $dbh->prepare($sql) or die;
  $sth->execute($hash->{id}, $hash->{name}, $hash->{address}) or die;
}

Though again this depends on the three arrays being synced up. However you could modify this to do value modifications or checks or greps in the other arrays within the first loop through array1 (ie: if your values in array2 and array3 are maybe stored as "NN-name" and "NN-address" where NN is the id from the first array and you need to find the corresponding values and remove the NN- with a s// regex). Depends on how your data is structured though.

Another note is to check out Class::DBI and see if it might provide a nicer and more object oriented way of getting your data in.

Answer 4

Since you have parallel arrays, you could take advantange of execute_array:

my $sth = $dbh->prepare('INSERT INTO table1 (id, name, address) VALUES (?, ?, ?)');
my $num_tuples_executed = $sth->execute_array(
    { ArrayTupleStatus => \my @tuple_status },
    \@ids,
    \@names,
    \@addresses,
);

Please note that this is a truncated (and slightly modified) example from the documentation. You'll definitely want to check out the rest of it if you decide to use this function.