Using the DBI to return the first ix/i rows

[Anonymous Monk]

I need to return the first x rows of a 'select' statement.

$returnRef = $db->selectall_arrayref($sql);

returns all the rows, but the table has >50,000 rows and I only want to see the first 10, for example.

How can this be done?

Thanks.

May you Perl in peace

[salvadors]

I need to return the first x rows of a 'select' statement

The most obvious way is to put a 'LIMIT x' in your SELECT.

But if for some reason you can't do that, then use something like:

my $sth = $dbh->prepare($sql);
$sth->execute;
$sth->bind_columns(\my ($col1, $col2 ... $coln));
my $count = 0;
while ($sth->fetch) {
  [$col1 .. $coln will be populated with the values in that row]
  last if ++$count == 10;
}

Tony

[eg]

In case you're using Oracle: you don't have a LIMIT SQL command (go figure), so you need to use ROWNUM instead:

SELECT * FROM table WHERE ROWNUM < 10;

[davorg]

Sybase doesn't have either of those mechanisms. You need to do it in two steps.

set rowcount 10
select * from table

--
<http://www.dave.org.uk>

"Perl makes the fun jobs fun
and the boring jobs bearable" - me

[kudra]

You could try looking at this discussion of much the same question. For future reference, you may want to try using the search or the Super Search to see if your question has been asked before.

If you never want to see more than just 10 rows, you might consider just adding a limit to your query.

[dsb]

Instead of doing a 'selectall' try sending an actual SQL query to the database. When you do, use the 'LIMIT' operator to keep the number of rows returned down. Then you could use any number of ways to assign the data to variables. When dealing with more than one column of data I prefer to use 'fetchrow_array' or 'fetchrow_arrayref'. Example:

#!/usr/bin/perl
use DBI;
$dbh = DBI->connect( #your database info here# );
$sth = $dbh->prepare( "SELECT * FROM table_name LIMIT x" );   # prepar
+es a query to send to DB where 'x' is the number of rows you want ret
+urned
$sth->execute();   # execute the query against the db

while ( ($col1, $col2) = $sth->fetchrow_array() ) {
     # code
     # code
     # code....etc.
}
$sth->finish();
$dbh->disconnect();

Hope this helps. - kel -