FAQ
Is there a way in DBI to get a $sth->rows count, when selecting to a
temporary table?

In this code I get a proper count from the SELECT, but the SELECT INTO
TEMPORARY TABLE returns -1.

$dbh = DBI->connect(...);
$sth = $dbh->prepare("SELECT data,name FROM saved_searches order by
random()");
$sth->execute();
print "Rows=".$sth->rows."\n";

$sth = $dbh->prepare("SELECT data,name INTO TEMPORARY TABLE foo_1 FROM
saved_searches order by random()");
$sth->execute();
print "Rows=".$sth->rows."\n";

Search Discussions

  • Scoles at Oct 31, 2008 at 10:24 am
    This sort of row count after select is highly depndant on the SQL database
    you are running against.

    What is you DB?
    Is there a way in DBI to get a $sth->rows count, when selecting to a
    temporary table?

    In this code I get a proper count from the SELECT, but the SELECT INTO
    TEMPORARY TABLE returns -1.

    $dbh = DBI->connect(...);
    $sth = $dbh->prepare("SELECT data,name FROM saved_searches order by
    random()");
    $sth->execute();
    print "Rows=".$sth->rows."\n";

    $sth = $dbh->prepare("SELECT data,name INTO TEMPORARY TABLE foo_1 FROM
    saved_searches order by random()");
    $sth->execute();
    print "Rows=".$sth->rows."\n";
  • Stewart Anderson at Oct 31, 2008 at 10:29 am

    -----Original Message-----
    From: scoles@pythian.com
    Sent: 31 October 2008 10:25
    To: Bryce Nesbitt
    Cc: dbi-users@perl.org
    Subject: Re: Getting $sth->rows when selecting to a temporary table

    This sort of row count after select is highly depndant on the SQL database
    you are running against.

    What is you DB?
    Is there a way in DBI to get a $sth->rows count, when selecting to a
    temporary table?

    In this code I get a proper count from the SELECT, but the SELECT
    INTO
    TEMPORARY TABLE returns -1.

    $dbh = DBI->connect(...);
    $sth = $dbh->prepare("SELECT data,name FROM saved_searches order by
    random()");
    $sth->execute();
    print "Rows=".$sth->rows."\n";

    $sth = $dbh->prepare("SELECT data,name INTO TEMPORARY TABLE foo_1
    FROM
    saved_searches order by random()");
    $sth->execute();
    print "Rows=".$sth->rows."\n";
    Don't know what DB you are using but I know I could not reliably use
    this with DBD::Oracle, I just did a count increment with each row
    in a fetch_array loop.

    Stu

    Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and are used under licence. British Sky Broadcasting Limited (Registration No. 2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited (Registration No. 2340150) are direct or indirect subsidiaries of British Sky Broadcasting Group plc (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
  • Bryce Nesbitt at Oct 31, 2008 at 7:12 pm

    scoles@pythian.com wrote:
    This sort of row count after select is highly depndant on the SQL database
    you are running against.
    What is you DB?
    I'm using postgres.
    I can of course do "select count(*) from foo1_" afterward.
  • Greg Sabino Mullane at Oct 31, 2008 at 7:46 pm

    Is there a way in DBI to get a $sth->rows count, when selecting to a
    temporary table?

    In this code I get a proper count from the SELECT, but the SELECT INTO
    TEMPORARY TABLE returns -1.
    Short answer: no

    Long DBD::Pg sepcific answer: SELECT is completely different from
    SELECT INTO TABLE. The latter is really a variant of a CREATE TABLE statement
    and thus is a DDL statement that does not return any rows. And while
    SELECT COUNT(*) is pretty inefficient, so is ORDER BY random(), so for
    small data sets this is fine, but for larger ones you might look at
    rethinking the way you pull the random rows, which may lend itself to
    an easier way to count rows along the way.


    - --
    Greg Sabino Mullane greg@turnstep.com
    PGP Key: 0x14964AC8 200810311542
    http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedOct 30, '08 at 11:31p
activeOct 31, '08 at 7:46p
posts5
users4
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase