Re^2: DBD::SQLite, how to pass array in query via placeholder?

[cavac]

That is partially correct. For example, PostgreSQL supports arrays. In that case, you use an array reference to put it into a single placeholder.

Ok, lets make a test table:

CREATE TABLE arraytesttable
(
   array_id text NOT NULL, 
   array_values text[] NOT NULL, 
   CONSTRAINT arraytesttable_pk PRIMARY KEY (array_id)
) 
WITH (
  OIDS = FALSE
);

Now, we use Perl to insert some rows:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=MY_DB;host=localhost", 
                       'MY_Server',
                       'VERYSECRET', 
                       {AutoCommit => 0}) 
                   or die("Can't connect");

my $sth = $dbh->prepare_cached("INSERT INTO arraytesttable
                                (array_id, array_values)
                                VALUES (?, ?)")
                   or die($dbh->errstr);

my @english = qw(Hello world);
my @german = qw(Hallo Welt);

$sth->execute('ger', \@german) or die($dbh->errstr);
$sth->execute('eng', \@english) or die($dbh->errstr);
$dbh->commit;

That worked very beautifull, now let's read the values back. First all rows, then by finding the languages when we know the full translation (e.g. selecting by array):

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=MY_DB;host=localhost", 
                       'MY_Server',
                       'VERYSECRET', 
                       {AutoCommit => 0}) 
                   or die("Can't connect");

# SELECT ALL ROWS
my $fullsth = $dbh->prepare_cached("SELECT array_id, array_values
                                    FROM arraytesttable")
                   or die($dbh->errstr);

$fullsth->execute or die($dbh->errstr);
while((my $line = $fullsth->fetchrow_hashref)) {
    print $line->{array_id} . ': ' . join(',', @{$line->{array_values}
+}) . "\n";
}
$fullsth->finish;

# SELECT BY ARRAY
my $arrsth = $dbh->prepare_cached("SELECT array_id, array_values
                                    FROM arraytesttable
                                    WHERE array_values = ?")
                   or die($dbh->errstr);

my @german = qw(Hallo Welt);
$arrsth->execute(\@german) or die($dbh->errstr);
while((my $line = $arrsth->fetchrow_hashref)) {
    print $line->{array_id} . ': ' . join(',', @{$line->{array_values}
+}) . "\n";
}
$arrsth->finish;

$dbh->rollback;

As expected, this outputs:

ger: Hallo,Welt
eng: Hello,world
ger: Hallo,Welt

So, we established that the single value in single column statement doesn't hold true. PostgreSQL allows a single column holds an array model, which DBD::Pg allows us to use through array references.