in reply to DBD::SQLite, how to pass array in query via placeholder?

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.

Replies are listed 'Best First'.
Re^2: DBD::SQLite, how to pass array in query via placeholder?
by dwalin (Monk) on Apr 09, 2011 at 19:18 UTC
    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.

[reply]
[d/l]

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

[reply]
        Corion,

        Thanks, this I have found out already. ;) But if not arrayref, is there any other method of passing a set of values suitable for IN operator? That's what I'd like to know. Maybe there's some kind of magic trick... Or a way to use DBI I don't know about yet, more like it. DBI's not what I'd call simple and laconic API...

        Regards,
        Alex.

[reply]

In Section Seekers of Perl Wisdom