First, your SQL statement is not what you might think it is.

Second, placeholders do not work for arrays. One question mark needs to correspond to one scalar value.

In the case of needing an in clause in SQL, I either use:

my $placeholders = join ",", (('?') x @values);
my $sql .= " and foo in ($placeholders)";
[download]

or just interpolate the values directly.

Corion,

Oops, I shoulda use single quotes for that SQL statement in Perl part, thanks for noticing. I'll correct the question.

Regarding placeholders, they do work i.e. in Postgres. This code is perfectly valid with DBD::Pg:

$sth = 
$dbh->prepare('SELECT * FROM foo WHERE foo = $1 AND bar = ANY ($2)');
$sth->execute(1, [1,2,3]);
[download]

In fact, I was porting this code from Pg to SQLite and stumbled on this discrepancy. I don't want to modify the query, it's passed on me (mine is a module) along with parameters from calling application. Kind of templating at work here. Parsing SQL select statements is not a trivial task, I don't want to reinvent the wheel (again).

Regards,
Alex.

At least for SQLite, I'm certain that IN does not work with arrays+placeholders the way you'd like.

As Corion already pointed out, but more explicitly stated:

my @in_vals = (1,2,3);
my $in_placeholders = join ',', ('?') x @in_vals;
my $sth = $dbh->prepare(qq{select * from foo where bar=? and baz in ($
+in_placeholders)});
$sth->execute(1, @in_vals) or die $dbh->errstr;
[download]

$SQL = new SQL::Abstract;
($sql, @bind) = $SQL->select ('foo', '*', {
  foo => 1,
  bar => { in => [ 1, 2, 3 ] },
};

$sth = $dbh->prepare ($sql);
$sth->execute (@bind);
[download]

Thanks but this module is not abstract enough for my purposes. Or perhaps I didn't grok it in full yet. Can you define the following query:

SELECT SPLIT, syn(SPLIT),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 40),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 30),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 50),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 220),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 20),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 80)
FROM csplit WHERE ACD = $1 AND SPLIT = $2
[download]

...in terms of SQL::Abstract? I don't see a way, at least no straightforward way.

Regards,
Alex.

I wonder ... wouldn't something like this perform better and be easier to tweak for SQL::Abstract?

SELECT SPLIT, syn(SPLIT),
    SUM(CASE WHEN WORKMODE = 40 THEN 1 ELSE 0 END),
    SUM(CASE WHEN WORKMODE = 30 THEN 1 ELSE 0 END),
    SUM(CASE WHEN WORKMODE = 50 THEN 1 ELSE 0 END),
    SUM(CASE WHEN WORKMODE = 220) THEN 1 ELSE 0 END,
    COUNT(*),
    SUM(CASE WHEN WORKMODE = 20 THEN 1 ELSE 0 END),
    SUM(CASE WHEN WORKMODE = 80 THEN 1 ELSE 0 END)
  FROM csplit
  JOIN cagent ON cagent.ACD = csplit.ACD AND cagent.SPLIT = csplit.SPL
+IT
 WHERE csplit.ACD = $1 and csplit.SPLIT = $2
   and cagent.LOC_ID = ANY ($3) and cagent.LOGID IS NOT NULL
 GROUP BY SPLIT
[download]

Jenda
Enoch was right!
Enjoy the last years of Rome.

When you execute a single SQL statement (regardless of how, or with what language, you attempt to do it...), a single placeholder necessarily corresponds to a single value that will be placed into a single column of a single row.

That is “how the SQL model-of-reality works.”

In short ... you are attempting to place a list of scalar values into a “slot” (a single column of a single database row) that can only possibly contain one scalar value.   And in this regard, it truly makes not one whit of difference which flavor of SQL engine you are attempting to use!   Alas, you have “several square pegs,” and “exactly one (square...) hole.”

“Perl or not ...”   ... “SQLite or not...”   ... you can’t do it.

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
);
[download]

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;
[download]

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;
[download]

As expected, this outputs:

ger: Hallo,Welt
eng: Hello,world
ger: Hallo,Welt
[download]

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.

Don't use '#ff0000':
use Acme::AutoColor; my $redcolor = RED();
All colors subject to change without notice.

Well, except for when you can

Good Day,
    Dean

Well, yes, “and then there’s Postgres.”   :-)   But Postgres goes beyond the traditional SQL model – and does so very well – in many ways beyond just this.   Good point.