SQLite Forum

In C API, do prepared statements of simple inserts execute repeatedly even if not reset?
Login

In C API, do prepared statements of simple inserts execute repeatedly even if not reset?

(1) By Gary (1codedebugger) on 2021-04-03 06:17:21 [link] [source]

I have a stupid question that I'm curious about because I made a stupid mistake but half of the process appears to have worked despite the error.

I left out some sqlite3_reset() statements for persistent prepared statements that were used to insert data. The same prepared statement was executed thousands of times without being reset and the data looks fine.

However, when I altered the code to test the success of each write to the database by extracting a piece of the just written data and sent it back to the sending application, it failed to extract the data; and that is how I noticed my mistake.

Do prepared statements that are simple inserts continue to work properly without running sqlite_reset()? The inserts are pretty simple because identically structured JSON passed to the C code is parsed using JSON_tree() and then written to the database based on path or fullkey.

Of course, I don't intend to code that way but wondered why the code didn't error/fail upon attempting to execute the prepared statement a second time for insert when it does for select.

Thank you.

(2) By Sir Humphrey Appleby (SirHumphreyAppleby) on 2021-04-03 07:49:04 in reply to 1 [link] [source]

I came across a similar question a few days ago. The answer as to why this works is in the source...

/* We used to require that sqlite3_reset() be called before retrying
** sqlite3_step() after any error or after SQLITE_DONE.  But beginning
** with version 3.7.0, we changed this so that sqlite3_reset() would
** be called automatically instead of throwing the SQLITE_MISUSE error.
** This "automatic-reset" change is not technically an incompatibility, 
** since any application that receives an SQLITE_MISUSE is broken by
** definition.

(3) By Keith Medcalf (kmedcalf) on 2021-04-03 14:19:30 in reply to 1 [link] [source]

As pointed out, a reset is automatic since version 3.7.0 when a statement concludes execution.

Technically speaking, a prepared statement is a "generator subprobram" and may have three states: "Never Executed", "Executed and produced Result", or "Done Execution".

The prepare process generates the subprogram and it is initially in the "Never Executed" state. When you call sqlite3_step it enters the "executing" state and proceeds either to the "Execute and produced Result" state (you get back SQLITE_ROW) and the subprogram is "paused" while you deal with the interim results. This repeats (you call sqlite3_step again) until the subprogram is complete or "Done Execution" at which point it returns SQLITE_DONE.

The above sequence may be modified by detection of an ERROR condition in which case the statement / generator subprogram returns an error indication and goes to the complete or "Done Execution" state and returns SQLITE_ERROR (or some other status code).

At this point the automatic reset kicks in and returns the statement automatically to the "Never Executed" state in preparation for the next go-round.

sqlite3_reset basically takes any subprogram (statement) and, no matter what its current state at the time of the call, resets any intermediate state and returns is to the "Never Executed" state ready to be executed as if for the first time (so that if you are, for example, in the "paused during execution state" to deal with a generated result row, you can "reset" the execution context in preparation for executing the statement anew).

(4) By Keith Medcalf (kmedcalf) on 2021-04-03 14:25:55 in reply to 3 [link] [source]

Note that you can compile the sqlite3 library to not do an automatic reset and instead require that you must issue a manual reset in order to transition from the "Not Executing, Done" state to the "Not Executing, Never Executed" state, but there is really no reason for the non-reset state to exist (all it really does it provide a capability to return a different error code if you forget where you are in the execution of a statement).

(5) By Keith Medcalf (kmedcalf) on 2021-04-03 14:35:49 in reply to 3 [link] [source]

Note that neither an explicit nor automatic reset clears any bindings that you have made against the statement as these are not part of the "execution state" but are a separate construct associated with the statement itself (though there is a call to reset the bindings (sqlite3_clear_bindings) which will clear the bindings data associated with a statement context).

It is not necessary to re-bind unchanged bindings to a statement context as this data is not modified by the execution state of the statement itself.

sqlite3_finalize operates on a statement context and does a reset, clears the bindings, then free's the various memory structures associated with a statement and destroys the statement context.

(6) By Gary (1codedebugger) on 2021-04-03 19:12:55 in reply to 3 [source]

Thank you for the explanation.  I think I now understand why sqlite3_step() didn't fail for the insert statements without a manual reset.  However, now my question is why does the select statement fail without a manual reset?

In the code below, the sqlite3_step(db_base->insertLexi) works repeatedly without a manual reset.

The first time the sqlite3_step(db_base->getLexi) it is executed, both sqlite3_column_text(db_base->getLexi, 0) and sqlite3_column_text(db_base->getLexi,8) point to data. The second time it is executed, without a manual reset, sqlite3_step(db_base->getLexi) returns SQLITE_DONE and both the sqlite3_column_text(...) return NULL. But with a manual reset, both the sqlite3_column_text(...) again point to data.

It appears that the statement is not being reset automatically for the sqlite3_step(db_base->getLexi).  I'm not arguing that it is not being reset but only saying that it appears to behave that way in that without the manual reset, I can't get the statement to return data. All the query requests is a select of the row with the greatest rowid. 

Thank you.

void write_json_lexi( const char *c, const char *tab )
  { 
    int rc = 0;     
    if ( ( rc = sqlite3_step( db_base->insertLexi ) ) != SQLITE_DONE )
      {
        sprintf( response, "{\"tab\":\"%s\",\"c\":\"%c\",\"s\":1,\"msg\":\"Failed to db_base->insertLexi.\" }", tab, *c );
        send_response( response );
        sqlite3_reset( db_base->insertLexi );
        sqlite3_reset( db_base->getLexi );
        return;
      }

    sprintf( response, "{\"tab\":\"%s\",\"c\":\"%c\",\"s\":0, \"msg\":\"Successfully wrote to json_raw.\"}", tab, *c );
    send_response( response );

    char *data,
         *strongs_no;

    if ( ( ( rc = sqlite3_step( db_base->getLexi ) ) != SQLITE_ROW && rc != SQLITE_DONE ) || 
         ( strongs_no = sqlite3_column_text( db_base->getLexi, 0 ) ) == NULL ||
         ( data = sqlite3_column_text( db_base->getLexi, 8 ) ) == NULL )
      {
        sprintf( response, "{\"tab\":\"%s\",\"c\":\"%c\",\"s\":1,\"rc\":%d,\"msg\":\"Failed to db_base->getLexi.\" }", tab, *c, rc );
        send_response( response );
        sqlite3_reset( db_base->insertLexi );
        sqlite3_reset( db_base->getLexi );
        return;
      }

    int szBytes = sqlite3_column_bytes( db_base->getLexi, 8 );
    sprintf( response, "{\"tab\":\"%s\",\"c\":\"%c\",\"s\":0,\"strongs_no\":\"%s\",\"bytes\":%ld,\"data\":\"%s\" }", tab, *c, strongs_no, szBytes, data );
    send_response( response );
    
    sqlite3_reset( db_base->insertLexi );
    sqlite3_reset( db_base->getLexi );

    return;

  } // close write_json_lexi

(7) By Igor Tandetnik (itandetnik) on 2021-04-03 21:06:46 in reply to 6 [link] [source]

The automatic reset happens when you call sqlite3_step after the previous sqlite3_step returned SQLITE_DONE.

The insert statement returns SQLITE_DONE right away (since obviously it doesn't have any rows to report), and so every time you call sqlite3_step, it's reset and executed anew.

The select statement returns SQLITE_ROW on the first call, and positions the cursor to read from the row. On second call, it returns SQLITE_DONE indicating that no more rows are available; the cursor is not positioned on any row, so no column data can be extracted (I assume from your description that the resultset consists of a single row). If you were to make the third call, the statement would reset and restart from the beginning, returning SQLITE_ROW again.

(8) By Gary (1codedebugger) on 2021-04-04 00:55:25 in reply to 7 [link] [source]

Oh, I see, now. Thank you very much for the explanation. I'm sure I read this before and applied it when the queries returned more than one row. But I use SQLite in small intervals with with big gaps in between and got stupid. I don't think I'll forget this again now. Thanks a lot.