Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: DBD::SQLite dilemma

by dbwiz (Curate)
on Aug 11, 2003 at 12:31 UTC ( #282832=note: print w/replies, xml ) Need Help??

in reply to DBD::SQLite dilemma

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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://282832]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (4)
As of 2020-08-15 14:51 GMT
Find Nodes?
    Voting Booth?
    Which rocket would you take to Mars?

    Results (79 votes). Check out past polls.