Here is a summary of the "in clause" discussion. When I've time, I'll
put it into a POD and send it to Tim Bunce. Also, I'll post it on my
DBI FAQ site.
Tom
From: "Baxter, Lincoln" <LBaxter....FLEETCC.COM>
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?
From: Ronald J Kimball [mailto:rjk-dbi....focalex.com]
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.
Ronal
From: Jim Lynch <jwl....sgi.com>
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?
From: Jim Lynch <jwl....sgi.com>
Thanks to all who responded. This has been a valuable and educational
interchange for me. Since I don't have much control over the number of
items in the set, I've decided to load them into a table for simplicity.
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?
>
> Thanks,
> Jim
From: Tony Foiani <anthony_foiani....non.hp.com>
Another way to construct a reasonable "IN" clause is to use the
DBI::quote method:
| my @vals = ('foo', "bar", "baz's");
| my $set = join ', ', map $dbh->quote($_), @vals;
| my $sql = "SELECT whatever FROM wherever WHERE somecolumn IN ($set)";
Note that this will properly handle the single quote in the third
value.
Using multiple placeholders will also work, although I'd be surprised
if the total number of placeholders in any given statement can be all
that high.
Finally, consider other ways of solving the problem. Are you using
this set often? If so, consider putting it into a table in the
database, and use a subquery to generate the IN clause, or join
against the primary table.
From: "Michael A. Chase" <mchase....ix.netcom.com>
See below.
> Another way to construct a reasonable "IN" clause is to use the
> DBI::quote method:
>
> | my @vals = ('foo', "bar", "baz's");
> | my $set = join ', ', map $dbh->quote($_), @vals;
> | my $sql = "SELECT whatever FROM wherever WHERE somecolumn IN ($set)";
>
> Note that this will properly handle the single quote in the third
> value.
>
> Using multiple placeholders will also work, although I'd be surprised
> if the total number of placeholders in any given statement can be all
> that high.
I've used over 50 placeholders in some Oracle SQL statements. If I'd tried
to put that many literals in the statement I'd have hit the upper bound to
the number of characters allowed in a SQL statement; you can fit a lot more
?s in there than you can fit full strings for the same number of arguments.
This is especially noticeable when some of the strings are very long. Also,
$dbh->quote() sometimes has problems with odd characters.
From: "Peter J . Holzer" <hjp....wsr.ac.at>
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.
From: Graham Barr <gbarr....pobox.com>
While everyone is posting thier favorite way to create a placeholder
list I thought I would send my favorite.
chop(my $qus = "?," x @values);
$sth = $dbh->prepare(qq{ ... ($qus) ... });
From: "William R. Mussatto" <mussatto....csz.com>
On Wed, 7 Feb 2001, Graham Barr wrote:
> While everyone is posting thier favorite way to create a placeholder
> list I thought I would send my favorite.
>
> chop(my $qus = "?," x @values);
> $sth = $dbh->prepare(qq{ ... ($qus) ... });
Actually I think you need single quotes (i.e., appostrophy ') so it
should read:
chop (my $qus = '?,' x @values)
$sth = $dbh->prepare("... ($qus) ...});
From: Ronald J Kimball <rjk-dbi....focalex.com>
On Wed, Feb 07, 2001 at 09:48:49AM -0800, William R. Mussatto wrote:
> On Wed, 7 Feb 2001, Graham Barr wrote:
> > While everyone is posting thier favorite way to create a placeholder
> > list I thought I would send my favorite.
> >
> > chop(my $qus = "?," x @values);
> > $sth = $dbh->prepare(qq{ ... ($qus) ... });
>
> Actually I think you need single quotes (i.e., appostrophy ') so it
> should read:
> chop (my $qus = '?,' x @values)
> $sth = $dbh->prepare("... ($qus) ...});
Since this is a Perl string (with no interpolated variables or special
characters), either single or double quotes will work just fine.
From: Ronald J Kimball <rjk-dbi....focalex.com>
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.
--
Thomas A. Lowery [EMAIL PROTECTED]
http://tlowery.hypermart.net
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
