perl DBI and placeholders

Question

I have this query select * from table where ID in (1,2,3,5...)

How is it possible to build this query with the DBI using placeholders ?

for example :

my @list = (1, 2, 3, 4, 5);
my $sql = "select * from table where ID in (?)";

$sth->prepare($sql);
$sth->execute();

What argument should I send to execute? Is it a list or a string separated by , or something else?

Bellaude

Answer 1

This should build your query dynamically according to the number of items in your array

my @list =(1,2,3,4,5);
my $sql ="select * from table where ID in (@{[join',', ('?') x @list]})";

Girt

Answer 2

It's not possible in that way. You need to specify a placeholder for each item in your array:

my @list = (1,2,3,4,5);
my $sql = "select * from table where ID in (?,?,?,?,?)";

$sth->prepare($sql);
$sth->execute(@list);

If your @list is not a fixed size, you need to build the $sql with the proper number of placeholders.

Styrax

Answer 3

Quoting DBI documentation:

Also, placeholders can only represent single scalar values. For example, the following statement won't work as expected for more than one value:

     SELECT name, age FROM people WHERE name IN (?)    # wrong
     SELECT name, age FROM people WHERE name IN (?,?)  # two names

Rewrite to:

my $sql = 'select * from table where ID in ( ?, ?, ?, ?, ? )';
$sth->prepare($sql);
$sth->execute(@list);

Manakin

Answer 4

If you are using DBI to access a PostgreSQL database with the DBD::Pg driver, you can use:

my @list = (1, 2, 3, 4, 5);
my $sql = "select * from table where ID = ANY(?::INT[]);";

$sth->prepare ($sql);
$sth->execute (\@list);

Ectophyte

I don't think you even need the ::INT[]. Just id = ANY(?) works for me. – Tunis

Answer 5

Unless you know the exact number of elements you cannot use placeholders. Try this:

my @list = (1, 2, 3, 4, 5);  # any number of elements
my $in = join(',', map { $dbh->quote($_) } @list);
my $sql = "select * from table where someid IN ($in)";

Builtup

As you can see from other answers, it is possible to use placeholders since it is easy to find out the size of an array, even though it comes from a dynamic source... – Ducharme

Using placeholders for lists makes sense if you prepare the statement once and then execute it multiple times with the exact same number of arguments; in this case it will only be overhead and increase the likeliness to exceed maximum query size. – Builtup

Answer 6

If you switch to DBIx::Simple you can just say:

$db->query('INSERT INTO foo VALUES (??)', $foo, $bar, $baz);

?? Means "as many as needed"

Edit:

Actually, I was a little too optimistic: "If the string (??) is present in the query, it is replaced with a list of as many question marks as @values."

So this does not seem to work:

$db->query( "SELECT * FROM foo WHERE id IN (??) AND stuff=?", @ids, $stuff )

Still useful though..

For the curious, the code in the module is:

# Replace (??) with (?, ?, ?, ...)
sub _replace_omniholder {
  my ($self, $query, $binds) = @_;
  return if $$query !~ /\(\?\?\)/;
  my $omniholders = 0;
  my $q = $self->{dbd} =~ /mysql/ ? $quoted_mysql : $quoted;
  $$query =~ s[($q|\(\?\?\))] {
    $1 eq '(??)'
    ? do {
        Carp::croak('There can be only one omniholder')
            if $omniholders++;
        '(' . join(', ', ('?') x @$binds) . ')'
    }
    : $1
  }eg;
}

Raymund

Answer 7

I found a sure way for this to work summarizing all of the above advice. My Production query (I posted a much simpler version here) uses IN <>, where neither the codes nor their quantity is unknown. It could be a single Code (e.g. FIN), or a series of them (FITLC FITLD FITLU FITSC FITSD FITSU MDYLC MDYLD MDYLU). Some function returns that as a list.

The code that makes this happen is

            @codes =  get_muni_evcode( $category );
            my $in = join( ', ', ('?') x @codes );
            print "\n\nProcessing Category: $category --> Codes: @codes   .. in: $in\n";

            my $sql = "select distinct cusip9 
            from material_event 
            where event_date between (trunc(sysdate) - 1) + 2/3 and trunc(sysdate) + 2/3 
            and event_code in ($in)";
            my $sth2 = $dbh->prepare($sql);
            $sth2->execute( @codes );

            while (my $s2 = $sth2->fetchrow_hashref('NAME_lc'))
            {
                    my $cusip9 = $s2->{cusip9};
                    print "$cusip9\t";
                   .................. further processing ..............

            }

The result sample:

Processing Category: RatingChange --> Codes: FITLC FITLD FITLU FITSC FITSD FITSU MDYLC MDYLD MDYLU MDYSC MDYSD MDYSU SPLD SPLPR SPLU SPSD SPSPR SPSU .. in: ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? 359496HQ2 359496GB6 359496GH3 359496GL4 359496HU3 359496HS8 359496HA7 359496HF6 359496GM2 359496HM1 359496HR0 359496HT6 359496GY6 359496GJ9 359496HL3 359496GU4 359496HK5 359496HN9 359496HP4 359496GW0 359496GZ3 359496HC3 359496GC4 359496GK6 359496GP5 359496GV2 359496GX8 359496GN0

I'm extremely grateful to everybody who posted their ideas here that finally made me find the right way to do this. It must be a pretty common problem I think.

Multiplicate