When writing DBI code I use bind parameters religiously, however, an occasion came up recently where I wasn't sure about the best method for doing so. I have a "WHERE foo IN ( 1, 2, 3 )" type clause where the size of the "IN" list is variable. I came up with two ways of doing this, both of which work:

First way:

# @ins is actually calculated at runtime. 
# Defined here for demonstration.
my @ins = qw(1 2 3 4 5 6);
my $in = join ', ', @ins;
my $sth = $dbh->prepare("SELECT foo FROM Bar WHERE baz IN ( ? )");
$sth->execute( $in );

[download]

Second way: 

my @ins = qw(1 2 3 4 5 6);
my $in;  $in .= '?, ' for @ins;

# discard hanging comma
$in =~ s/, $//;
my $sth = $dbh->prepare("SELECT foo FROM Bar WHERE baz in ( $in )");
$sth->execute( @ins );

[download]

From an elegance standpoint, it's nice to have each "IN" item be its own bind parameter, but I don't know if that's best for efficiency. With a single bind parameter, prepare_cached could be used since the rest of the statement doesn't change. On the other hand, I don't know if prepare_cached offers any advantage whatsoever when dealing with this type of query. If it matters, I'm using MySQL with InnoDB tables.

In reply to DBI and variable numbers of bind parameters by friedo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.