1. DBI internals
Let's turn back to a dbGetQuery function call from before.
2. dbGetQuery()
It was incredibly easy to use. Simply pass the connection object and an SQL query, and you get a result.
What it actually does behind the scenes is sending a query, with dbSendQuery, like this. This function returns a result, but this does not actually contain any records you wanted to import. For that, you need to use the function dbFetch. Finally, you have to manually clear the result.
The combination of dbSendQuery, dbFetch and, dbClearResult gives the exact same result as dbGetQuery did before, so why do this? Well, the dbFetch query calls allow you to specify a maximum number of records to retrieve per fetch. This can be useful when you need to load in tons of records, but want to do this chunk by chunk.
Suppose, for example,
3. dbFetch() one by one
you want to get the result of the previous query, record by record. You can use this construction for that, with the same SQL query as before.
This code first sends a query to the database, and then goes into a while loop that checks whether the query result still has data that has not yet been fetched. If there is still data available, the chunks are fetched record by record, and this chunk is printed at each iteration. The output indeed shows the two records, printed separately, one after the other.
In this toy example, this approach is not really useful, but if you're working on a super complicated algorithm that involves millions of database records, you might want to consider a treatment of data in chunks, right?
After all your hard work on the database,
4. Disconnect
don't forget to disconnect from it.
Give these more low-level DBI functions a try in the exercises.
5. Let's practice!
In the next chapter, I'll be back to tell you more about importing data from the web. See you there!