Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

How to use Perl DBI to select just on column of db

by lightoverhead (Monk)
on Mar 18, 2011 at 04:30 UTC ( #893903=perlquestion: print w/ replies, xml ) Need Help??
lightoverhead has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I tried to retrieve only one column from db every time and combine each time result into an array.

I know I can use "selectcol_arrayref" to do this job, and it works.

However, I kind of doubt its performance. I tried to use a statement handle with a place holder to do it. so I can prepare a query first, and each loop of execute will return all the rows of one single column.

Just didn't find such info anywhere.

could someone give me some hints? thank you.
foreach $id (@id){ $statement = qq{select val1 from tb11 where x=$id}; $col_ref = $dbh->selectcol_arrayref($statement); push @ary,$col_ref; }

above is what I did, which actually just return the values of one column.

now, I want to put the query statement out of the loop,

and use something like $sth->prepare(qq{select val1 from tbl1 where x= ?}),

then in the loop try to do $sth->execute($id) to get the value.

But I want to get the whole column values at once, not just reference to array of reference.

I am wondering if there is a function for statement handle to return all the values for a single column by just one call.

Comment on How to use Perl DBI to select just on column of db
Download Code
Re: How to use Perl DBI to select just on column of db
by wind (Priest) on Mar 18, 2011 at 04:33 UTC

    What are you trying to do again? Where is the code that you've tried thus far, and what problem do you have with it?

    You've already asked a few questions on perlmonks, but maybe try reading How do I post a question effectively?.

    - Miller

    Update: Thank you for the updated posting

    The below code uses a placeholder like you described.

    my $sth = $dbh->prepare(qq{SELECT val1 FROM tb11 WHERE x=?}) foreach my $id (@id){ $sth->execute($id) or die $dbh->errstr; $sth->bind_columns(\my $val1); while ($sth->fetch) { push @ary, $val1; } }

    Or in a single statement

    if (@id) { my $ids = join ',', map {$dbh->quote($_)} @id; $col_ref = $dbh->selectcol_arrayref(qq{SELECT val1 FROM tb11 WHERE + x IN ($ids)}) }

    Update2Mistype, meant prepare not selectcol_arrayref

      Hi Miller,

      Thank you for your answer. However, I don't understand how you get statement handle $sth

      the return value of $dbh->selectcol_arrayref is the reference to the column which containing all the values of rows. But you treated it as a statement handle.

      As you can see from my previous code, I put the reference to an array containing the values of the column from each row into @ary. But you seem put every value of row into this @ary which is what I tried to avoid.

      Could you explain more to me? Thanks.
Re: How to use Perl DBI to select just on column of db
by repellent (Priest) on Mar 18, 2011 at 06:03 UTC
    The IN keyword was there since SQL-86 (I believe):
    SELECT val1 FROM tbl1 WHERE x IN (11, 22, 33, ...);

    Here's an elaborate example:

    use DBIx::Interp;
    my $dbx = DBIx::Interp->new($dbh); my @ary = @{ $dbx->selectcol_arrayref_i( "SELECT val1 FROM tbl1 WHERE", { x => \@id }, ) };
Re: How to use Perl DBI to select just on column of db
by runrig (Abbot) on Mar 18, 2011 at 14:52 UTC
    selectcol_arrayref is a database handle method, but it can take a statement handle instead of a SQL statement as an argument:
    my $sth = $dbh->prepare("select val1 from tb11 where x=?"); ... $dbh->selectcol_arrayref($sth, undef, $x);
    The second argument is the "attributes" hashref, which can cause the method to fetch more than one column, and/or select which column(s) are fetched. See the docs.
      Hi runrig, That's the one I wanted. I almost forget that "undef" trick.

      Thank you very much.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2014-07-28 08:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (193 votes), past polls