In a script that processes a lot of rows in a MySQL server, I use dbSendQuery and fetch to throttle the fetching and processing of results.
When my fetch command retrieves exactly the number of rows available (or left) in the resultset, leaving 0 rows to be fetched, dbHasCompleted returns FALSE whereas I expected it to return TRUE.
query <- "select record_id, name
from big_table left join another_table using (record_id)
limit 500"
resultset <- dbSendQuery(con, query) # con: DB connection
while(!dbHasCompleted(resultset)) {
input <- fetch(resultset, n = 500)
print(paste("Rows fetched:", nrow(input)))
# process input ...
}
I expected this loop to run once, but there is an extra run as after processing, print is called again:
Rows fetched: 500
...
Rows fetched: 0
Apparently, dbHasCompleted(resultset) is false when the exact number of available rows is fetched (same behaviour is observed for n = 1000, 2000, 3000). When in this script n = 501, there is no second loop.
Is this to be expected? Am I doing something wrong?
