DBI select IN Array

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

Replies are listed 'Best First'.
Re: DBI select IN Array by hippo (Archbishop) 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);` [download] Update: Fix typo on last line `s/taglist/tagList/;` I never use camelCase in variable names so it was easy to miss :-)	[reply] [d/l] [select]
Re^2: DBI select IN Array by Tux (Canon) on Feb 20, 2019 at 15:07 UTC
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	[reply]
Re^2: DBI select IN Array by Anonymous Monk on Feb 20, 2019 at 09:42 UTC
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);` [download]	[reply] [d/l]
Re^3: DBI select IN Array by hippo (Archbishop) on Feb 20, 2019 at 10:02 UTC
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.	[reply]
Re^4: DBI select IN Array by Anonymous Monk on Feb 20, 2019 at 11:55 UTC
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__` [download] Output: `$ perl 1230223.pl SELECT table FROM tags WHERE ( tag IN ( ?, ? ) ) red black` [download] Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l] [select]
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";` [download] yields `SELECT table FROM tags WHERE tag IN (?, ?)` [download]	[reply] [d/l] [select]
Re^2: DBI select IN Array by Tux (Canon) on Feb 20, 2019 at 15:41 UTC
Or use `$"`: `local $" = ","; my $sql = "select table from tags where tag in (@{[('?')x@taglist]})";` [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
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));` [download]	[reply] [d/l] [select]