Re: How do I form an acceptable string for an IN clause?

[Peter J . Holzer]

On 2001-02-07 09:58:17 -0500, Jim Lynch wrote:
> my $in_clause1="'abcdef','xzyrst'";
> my $in_clause2="'abcdef'";
> 
> sth=$db->prepare("select * from table_x where txt in (?)");
> 
> and gotten the same results.  Can someone please tell me if it's
> possible to generate an IN clause on the fly and how?

You cannot replace a placeholder with a variable number of variables. If
the number of values in the IN clause varies, you have to re-prepare the
statement, too, for example like this:

    @values = qw(abcdef xzyrst);

    [...]

    my @placeholders = map { "?" } @values;

    my $sth = $dbh->prepare("select * from table_x where txt in (" .
                            join("," @placeholders) . ")");
    $sth->execute(@values);

Of course you can cache previously prepared statement handles like this

    my $cmnd = "select * from table_x where txt in (" .
                            join("," @placeholders) . ")";
    if $sth_cache{$cmnd}) {
        $sth = $sth_cache{$cmnd};
    } else {
        $sth = $dbh->prepare($cmnd);
        $sth_cache{$cmnd} = $sth;
    }

    $sth->execute(@values);

but then you probably need to remove unneeded statement handles from the
cache from time to time.

        hp

-- 
   _  | Peter J. Holzer      | Any setuid root program that does an
|_|_) | Sysadmin WSR / LUGA  | exec() somewhere is just a less
| |   | [EMAIL PROTECTED]        | user friendly version of su.
__/   | http://www.hjp.at/   |    -- Olaf Kirch on bugtraq 2000-08-07

PGP signature