A Lua iterator is designed to be called repeatedly inside a "for" loop. The function
nrows returns such an iterator, after doing an SQLite3 "prepare" (to set up for doing a SELECT or similar). After doing a "prepare" you are supposed to "finalize" to clean up the prepared statement, unlock the database, and free resources.
What happens in this iterator is that, when it reaches the end of the requested set of data (that is, the attempt to get more data returns SQLITE_DONE) it does the cleanup, and then returns nil, which exits the for loop.
If you break out of the loop (ie. using
break or
return) then that code is not executed and the database remains locked.
Your options are:
- Don't break out of the loop, but let it finish, ignoring the other rows you don't want.
- Make the query such that it exits itself when your desired condition is reached. For example, if you want to stop after a certain item is found, make that part of the WHERE clause.
- Do the prepare / execute / finalize yourself (see below).
http://www.gammon.com.au/scripts/doc.php?general=lua_sqlite3
You can:
Example:
require "tprint"
-- make in-memory database
db = sqlite3.open_memory()
-- make a table for testing
db:exec [[
CREATE TABLE mobs (name, class, hp);
INSERT INTO mobs VALUES("Naga", "mage", 666);
INSERT INTO mobs VALUES("Wolf", "beast", 42);
INSERT INTO mobs VALUES("Guard", "warrior", 100);
]]
-- prepare a SELECT statement
local stmt = db:prepare ("SELECT * FROM mobs")
-- loop until we get everything
while true do
local result = stmt:step ()
-- exit loop if done
if result == sqlite3.DONE then
break
end -- if done
-- should have ROW result
assert (result == sqlite3.ROW, "Row not found")
-- get all values into a table
local row = stmt:get_named_values()
-- display them
print (string.rep ("-", 20))
tprint (row)
end -- while
-- done with this statement
stmt:finalize ()
-- finished with database
db:close ()
Now in this example, since we are handling the finalize ourselves, we can break out of that loop if we want to, because we will then execute the
stmt:finalize () anyway. If you choose to do a
return then you need to do the finalize before returning.