RE: How do I form an acceptable string for an IN clause? (slick)

In perl, there are (naturally) many ways to do things... I recall doing
this other somewhat more brute force ways, but I particularly, like 
this fragment for IN clauses...

slick.

Perhaps we should add this example could be added to the DBI pod?


-----Original Message-----
From: Ronald J Kimball [mailto:rjk-dbi@focalex.com]
Sent: Wednesday, February 07, 2001 10:20 AM
To: Jim Lynch
Cc: dbi-users
Subject: Re: How do I form an acceptable string for an IN clause?


On Wed, Feb 07, 2001 at 09:58:17AM -0500, Jim Lynch wrote:
> I've tried everything except standing on my head to get it to work. 
> This example should show what I'm attempting to do.  As long as the
> clause has only one element, it works.
> 
> my $in_clause1="('abcdef','xzyrst')";
> my $in_clause2="('abcdef')";
> 
> sth=$db->prepare("select * from table_x where txt in ?");
> $sth->execute($in_clause1);
> # that works
> $sth->execute($in_clause);
> # this doesn't
> I've tried also tried
> 
> 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?

This is a rather frequently asked question.  You cannot use a placeholder
in the place of multiple values.  Each placeholder substitutes for exactly
one value.

When I need to use placeholders for an arbitrary number of values, I
generally do something like this:

my @sth;

my @values = ('abcdef', 'ghijkl');

my $placeholders = join ', ', ('?') x @values;

$sth[@values] ||= $dbh->prepare(<<"EndOfSQL");
  SELECT *
  FROM my_table
  WHERE my_column IN ($placeholders)
EndOfSQL

$sth[@values]->execute(@values);


That gives me one prepared statement handle for each count of values that
occurs.

Ronald

• RE: How do I form an acceptable string for an IN clause? (slick) by Baxter, Lincoln
• Summary of "How do I form an acceptable string for an IN clause?" by Thomas A . Lowery