How to safely interact with an SQL database minimally

Posted by Kahenraz on Wed 06 Jun 2018 12:16 PM — 4 posts, 20,085 views.

Kahenraz Wed 06 Jun 2018 12:16 PM #0

I've run into issues in the past due to a failure to completely iterate over the result from a query. The Mush documentation clearly states:

Quote:
Note: You should let the iterator finish (that is, get to the end of the selected rows) otherwise the database remains locked. So, do not "break" out of the for loop.

On that note, is it safe to break early if I also call db:interrupt()?

Also, do I need to iterate with a for loop for db:rows() if I'm only expecting a single row?

Do I need to do this:

local a;

for _ in db:rows(Statement) do
   a = _
end -- for

Or can I just do this safely:

local a;

a = db:rows(Statement)

I can figure all of this out for myself but I can't tell how to check if the database is locked after setting up a piece of code.

What would be a good way to go about testing this?

Fiendish USA Global Moderator Thu 14 Jun 2018 10:54 AM #1

Yeah, that warning is there because I ran into the problem accidentally a while back.

This old post might be helpful. Maybe, maybe not.
https://www.mushclient.com/forum/?id=13415&reply=5#reply5

Quote:
I can figure all of this out for myself but I can't tell how to check if the database is locked after setting up a piece of code.

What would be a good way to go about testing this?

As far as I know, the only way to check is to do another sqlite operation that will generate an error and then look at the error code.

Nick Gammon Australia Forum Administrator Tue 19 Jun 2018 06:16 AM #2

Have you read this?

http://www.gammon.com.au/db

You can "prepare" an SQL statement, "step" once, and then finalize it.

Nick Gammon Australia Forum Administrator Tue 19 Jun 2018 06:22 AM #3

Quote:

Also, do I need to iterate with a for loop for db:rows() if I'm only expecting a single row?

Personally I often do that, because the alternative is more coding. After all, the overhead is just one extra query for more data that immediately fails.