Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

DBI select IN Array

by Anonymous Monk
on Feb 20, 2019 at 09:11 UTC ( [id://1230223]=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear monks

What is wrong in the following statement? (Trying to query a database using IN and a array with my query values)

my @tagList=('red','black'); my $ID = $dbh->selectall_arrayref("SELECT table FROM tags WHERE ta +g IN (join(', ', ('?') x @tagList))");

Replies are listed 'Best First'.
Re: DBI select IN Array
by hippo (Bishop) on Feb 20, 2019 at 09:18 UTC

    Perl does not execute Perl inside a Perl string. eg. print "1 + 1"; will output "1 + 1" and not "2". Set up your SQL string properly and it will be fine.

    my @tagList = ('red', 'black'); my $sql = 'SELECT table FROM tags WHERE tag IN (' . join (', ', map {'?'} @tagList) . ')'; my $ID = $dbh->selectall_arrayref ($sql, undef, @tagList);

    Update: Fix typo on last line s/taglist/tagList/; I never use camelCase in variable names so it was easy to miss :-)

      Note that some/many databases have a limit on the length of that list. Did you try with 1_892_102_321 entries?

      YMMV

      Just saying this doesn't scale well


      Enjoy, Have FUN! H.Merijn

      Thank you Hippo. I can see the Perl-related issue. However, your solution produces an empty result, while if I use the following $sql, I can get the expected results. What do you think?

      my $sql = "SELECT table FROM tags WHERE tag IN ('red','black')"; my $ID = $dbh->selectall_arrayref ($sql);

        It was a typo in the args on the last line which I've fixed now. If you were running under strict your code would have picked that up. Try that and see how you get on. If you still have problems please consider providing an SSCCE.

Re: DBI select IN Array
by 1nickt (Canon) on Feb 20, 2019 at 14:41 UTC

    Hi, you might like SQL::Abstract::More for building SQL statements that you can then execute with DBI.

    Your particular query is simple, and since you obviously know that you do not have much data you are using selectall_arrayref, but things have a way of gaining complexity and/or volume with time. I always code for the future, which for me means eschewing hand-rolled SQL. YMMV.

    use strict; use warnings; use feature 'say'; use SQL::Abstract::More; my @tagList = ('red', 'black'); my $SQLA = SQL::Abstract::More->new; my ($stmt, @bind) = $SQLA->select('tags', ['table'], {tag => {-in => \ +@tagList}}); say $stmt; say "@bind"; # $dbh->selectall_arrayref($stmt, undef, @bind); # ... etc. __END__
    Output:
    $ perl 1230223.pl SELECT table FROM tags WHERE ( tag IN ( ?, ? ) ) red black

    Hope this helps!


    The way forward always starts with a minimal test.
Re: DBI select IN Array
by clueless newbie (Curate) on Feb 20, 2019 at 15:21 UTC
    Use the pram/baby cart, young Jedi!
    my @tagList=('red','black'); my $sql="SELECT table FROM tags WHERE tag IN (@{[join(', ', ('?') x @t +agList)]})"; print $sql,"\n";
    yields
    SELECT table FROM tags WHERE tag IN (?, ?)

      Or use $":

      local $" = ","; my $sql = "select table from tags where tag in (@{[('?')x@taglist]})";

      Enjoy, Have FUN! H.Merijn
Re: DBI select IN Array
by dsheroh (Monsignor) on Feb 21, 2019 at 08:28 UTC
    Your original code (using the x operator instead of map) also works just fine if you move the join outside of the quotes:
    my $ID = $dbh->selectall_arrayref("SELECT table FROM tags WHERE tag IN + " . join(', ', ('?') x @tagList));

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1230223]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2024-03-29 11:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found