At the moment in DBD::SQLite I have to use the API to get an entire select results into memory and then loop through that for fetch() calls. This is a bit annoying because it uses lots of memory for large result sets.
Internally SQLite implements a callback mechanism (sort of like SAX) but this doesn't work with the DBI's model of working, so we have to use the above.
Thankfully the SQLite team has added a non callback streaming API into CVS, so at some point I'll convert DBD::SQLite to use this.
Even more interesting is that the model looks almost exactly like how the DBI is written, so the code will get even simpler in the next release. Woot.