Confused about sqlite3_step / sqlite_reset / sqlite3_bind_xxx APIs
(1.1) By Etienne Doms (smod42) on 2021-05-21 14:22:56 edited from 1.0 [link] [source]
I'm running into SQLITE_MISUSE issues and I guess I'm really missing something.
So, the first thing I do in my application is preparing all required statements, I'm using the sqlite3_prepare_v2() API, and I read the "Goofy Interface Alert" in the documentation.
For SELECT queries, I first sqlite3_bind_xxx() all parameters and then loop using sqlite3_step(). When sqlite3_step() returns SQLITE_ROW, I know I have a row from which I can extract column values, and when sqlite3_step() returns SQLITE_DONE I know I processed each row.
For INSERT/UPDATE/DELETE queries, I do the same, but call sqlite3_step() only once, which will either returns SQLITE_DONE or an error (like a constraint violation).
I read in the documentation that since 18.104.22.168, which I'm well above, I do not need to manually call sqlite3_reset() if the sqlite3_step() returned anything other than SQLITE_ROW. And I understood this as "you only need to use sqlite3_reset() when you found the row you needed and won't go further in the cursor".
However, I'm discovering that when trying to reuse the statement with new bindings, sqlite3_bind_xxx() fails with SQLITE_MISUSE. This is indeed documented, but so the "automatic sqlite3_reset()" only works when reexecuting the exact same query, with the exact same bindings, is this correct?
I'm also confused about the value returned by sqlite3_reset() when the previous sqlite3_step() returned something else than SQLITE_DONE or SQLITE_ROW (any error in other words). Will sqlite3_reset() just return the same error as the sqlite3_step()? Why isn't it simply returning SQLITE_OK in this scenario?
Thanks in advance for your enlightenment.
(2) By Simon Slavin (slavin) on 2021-05-21 17:24:21 in reply to 1.1 [link] [source]
Given that you are using SQLite in different ways for different parts of your program, I'm not going to discuss the different behaviours you'll see at different points. Instead, I'll give you some guidelines.
sqlite3_reset() too much rather than too little. It won't do any harm. Also, it doesn't clear existing bindings. Use it if you're going to reuse a statement after
SQLITE_DONE. You should expect to use either
sqlite3_finalize() on every statement before your program closes a connection.
When checking the value returned by
sqlite3_reset(), you should get
SQLITE_OK. Other values probably indicate errors.
(3) By Etienne Doms (smod42) on 2021-05-21 18:35:01 in reply to 2 [link] [source]
Thank you for your reply.
Well, I'm actually trying to isolate the SQLite logic into a dedicated module, I just see a difference between "execute query" (DQL) which returns a cursor to iterate on (SQLITE_ROW/SQLITE_DONE logic), and "execute update" (DML) which directly returns SQLITE_DONE or an error. In both case I also intend to reuse the prepared statements and redo the binding at each query.
I indeed understand that just calling sqlite3_reset() after each query won't harm and seems to be "the way". However, I'm confused about the special scenario where sqlite3_step() returned an error (like SQLITE_CONSTRAINT), then the next sqlite3_reset() is also returning an error (insead of SQLITE_OK as you wrote). Will sqlite3_reset() always return the same error as the previous sqlite3_step()? It's not so clear in the documentation...
(4) By Larry Brasfield (larrybr) on 2021-05-21 19:15:53 in reply to 3 [link] [source]
... However, I'm confused about the special scenario where sqlite3_step() returned an error (like SQLITE_CONSTRAINT), then the next sqlite3_reset() is also returning an error (insead of SQLITE_OK as you wrote). Will sqlite3_reset() always return the same error as the previous sqlite3_step()? It's not so clear in the documentation...
The the sqlite3_reset() doc says, "If the most recent call to sqlite3_step(S) for the prepared statement S indicated an error, then sqlite3_reset(S) returns an appropriate error code." Is that unclear? Do you expect the doc to state exactly what error code will be returned? To me, the doc seems clear enough, even though "appropriate error code" leaves some ambiguity. If you disagree, perhaps you can make a case for more exactitude. My attempts at that seem strained.
(5) By Etienne Doms (smod42) on 2021-05-22 05:06:56 in reply to 4 [link] [source]
Do you expect the doc to state exactly what error code will be returned?
I do need to know exactly what 3rdpary library calls might return or not to specify and test the correct nominal/error/exception logic in my software.
However, I don't need the documentation to be so pedantic, I'm aware I can just read the code, or ask questions here.
To me, the doc seems clear enough, even though "appropriate error code" leaves some ambiguity.
This makes perfect sense for the legacy (pre-v2) interface (sqlite3_step() just says generic error and sqlite3_reset() / sqlite3_finalize() says exactly what).
For the updated interface, what I understand is that nothing actually changed for the sqlite3_reset() function, which is then now returning an error that has already been returned before. This is what I observe and what you seem to confirm, it's fine.
Sorry these questions are boring, but better be safe than sorry.
Thank you, I have no additional questions.
(6) By Simon Slavin (slavin) on 2021-05-22 12:09:29 in reply to 5 [source]
Unfortunately, we don't know what calls from the SQLite C API that third party is making. We don't know what "execute query" and "execute update" do. Those aren't SQLite terms. Those calls in your third party library could make any combination of SQLite C API calls in any order. So we can't tell you what they'll return.