Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
i.e. I've broken my $rowcount = $dbh->do("SELECT * FROM TABLE");

That was sort of broken from the beginning, I'm afraid. As PodMaster notes, the DBI does not guarantee that a method returns a row count for a SELECT, so using $dbh->do to get a row count doesn't seem to be kosher.

I would go for option number 2, trying to mimic DBD::mysql approach, where you have "store_result" (when you get all the records at once) or "use_result" (when you get the records one-by-one). You may leave the old method as behavior, so existing code would not be broken, and the new method as optional for each statement handler (e.g. $sth= $dbh->prepare($query, {sqlite_use_result => 1})).

Getting the numbers of rows in this scenario would follow the DBI standards. If you use selectall_arrayref or fetchall_arrayref, the number of rows is given by the resulting array size. Using the fetchrow_* methods, you need to count manually, the same way you do it for other drivers.

Concerning the suggestion of integrating the SELECT call with a separate COUNT(*) statement, I would rather not do that, since it is a kind of intrusive coding that is difficult to control. For example, given this query

SELECT field1, field2, SUM(field3) FROM table1, table2, table3 WHERE table1.id1 = table2.id1 AND table2.id2=table3.id2 GROUP BY field1, field2

How would you modify it to get a count? You must get the count of the aggregated columns, but if this is an expensive query, the count would be almost as expensive as the original query. The same is true for WHERE clauses with heavy calculations. In this case, I would advise to leave such tricks to the users imagination.


In reply to Re: DBD::SQLite dilemma by dbwiz
in thread DBD::SQLite dilemma by Matts

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others cooling their heels in the Monastery: (8)
    As of 2020-11-27 20:30 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found

      Notices?