Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re: Perl - DBI - How to process array rather than single value?

by mje (Curate)
on Apr 10, 2014 at 13:56 UTC ( #1081817=note: print w/replies, xml ) Need Help??


in reply to Perl - DBI - How to process array rather than single value?

Something like (untested):

my @query_ids = (5,4,3,2,1); my $sql = q/select rec_data from foo where id in (/ . join(",", @query +_ids) . q/) order by id/; my $results = $dbh->selectall_arrayref($sql);

You can also use placeholders which most people will say are better (which they are) but it depends on how big @query_ids is. Some database engines have quite low limits on the number of parameters. Anyway, to do it that way:

my @query_ids = (5,4,3,2,1); my $sql = q/select rec_data from foo where id in (/ . join ",", ("?") +x @query_ids .q/) order by id/; print $sql;' select rec_data from foo where id in (?????) order by id my $results = $dbh->selectall_arrayref($sql, undef, @query_ids);

Are you also asking to get the rows back in the order they are in @query_ids?

Replies are listed 'Best First'.
Re^2: Perl - DBI - How to process array rather than single value?
by chacham (Prior) on Apr 10, 2014 at 14:51 UTC

    Dynamic SQL is insecure. Dynamic placeholders can be inefficient as the statement will likely not be reused.

    Instead, split the string in SQL via XMLDB or the like. This is not dynamic (especially when defined in the DB itself) and the plan can be reused by the optimizer.

    --

    Edit: Removed comment on limitation. I misread the code. Added more explanation.

Re^2: Perl - DBI - How to process array rather than single value?
by Anonymous Monk on Apr 10, 2014 at 15:55 UTC
    Whereas the per-record loop approach would be: while ($row = $dbh->selectrow_hashref()) or somesuch . . .

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1081817]
help
Chatterbox?
marioroy concurrent Tie::File among many workers is possible via MCE::Shared.
marioroy Ditto for DB_File, BerkeleyDB, etc. ;-)

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (3)
As of 2017-08-18 07:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Who is your favorite scientist and why?



























    Results (296 votes). Check out past polls.

    Notices?