SQLITE Exception Handling: Why must I reset the statement twice after foreign key violation?

I'm using SQLITE 3.8.7 and am trying to create a custom exception handling class whenever a statement fails a constraint such as a foreign key constraint violation or if a sqlite3_step statement returns SQLITE_MISUSE.

In my destructor, I perform 1) a rollback; 2) cleanup; 3) log the exception.

~SQLException()
{
    try
    {
        _db->Execute("ROLLBACK");
        try
        {
           _db->ResetStatement();
        }
        catch (sql_exception anotherException)
        {
           _db->ResetStatement();
        }

        LOG_ERROR("sql_exception: %d %s\n", _e.code, _e.message.c_str());
    }
    catch (sql_execption ex)
    {
        LOG_ERROR("sql_exception: %d %s\n", ex.code, ex.message.c_str());
    }

Execute() is just a wrapper around sqlite3_exec while ResetStatement() is a wrapper around sqlite3_reset().

void DatabaseConnection::Execute(const char* text)
{
   ASSERT(_handle);

   auto const result = sqlite3_exec(_handle.get(), text, nullptr, nullptr, nullptr);

   if (SQLITE_OK != result)
   {
      throw sql_exception(result, sqlite3_errmsg(_handle.get()));
   }
}

void DatabaseConnection::ResetStatement()
{
   auto const result = sqlite3_reset(_stmt.get());

   if (SQLITE_OK != result)
   {
      throw sql_exception(result, sqlite3_errmsg(sqlite3_db_handle(_stmt.get())));
   }
}

I wrote a unit test that triggered a foreign key constraint violation (which triggers this cleanup code), and then try and perform additional inserts like nothing happened. The rollback works fine but the first reset throws the same foreign key constrain violation exception. It is only when I call ResetStatement() a second time do things get cleaned up and I'm able to continue with additional inserts, etc.

Why does the reset statement need to be called twice?

Thanks.

UPDATE: Here's a simple example that causes both ResetStatements() to be hit...

Table 1: Teams
TeamID INTEGER NOT NULL,
Name TEXT NOT NULL,
PRIMARY KEY(TeamID)

Table 2: Players
PlayerID INTEGER NOT NULL,
Name TEXT NOT NULL,
TeamID INTEGER NOT NULL,
PRIMARY KEY(PlayerID),
FOREIGN KEY(TeamID) REFERENCES Teams(TeamID)

try
{
    INSERT INTO Players VALUES(1, "Joe Montana", 1) -->causes expected Foreign Key constraint violation and you cannot proceed with another insert until ResetStatement() is called twice.
}
catch (...)
{
    SQLException();
}

So far this seems to only occur with Foreign Key Constraint violations. Other exceptions I've thrown at it seem to be properly reset after only one call.

This is raw database code. The commented line retains the Foreign Key Constraint violation on the first pass (rc == 19), but is on the second pass (rc ==0).

SQLITE_API int sqlite3_reset(sqlite3_stmt *pStmt){
  int rc;
  if( pStmt==0 ){
    rc = SQLITE_OK;
  }else{
    Vdbe *v = (Vdbe*)pStmt;
    sqlite3_mutex_enter(v->db->mutex);
    rc = sqlite3VdbeReset(v); //first pass rc = 19, second pass rc = 0
    sqlite3VdbeRewind(v);
    assert( (rc & (v->db->errMask))==rc );
    rc = sqlite3ApiExit(v->db, rc);
    sqlite3_mutex_leave(v->db->mutex);
  }
  return rc;
}

The same behavior exists in the latest version of SQLite, 3.8.11.1.

Solution

The described behavior of sqlite3_reset is nowadays documented:

If the most recent call to sqlite3_step(S) for the prepared statement S returned SQLITE_ROW or SQLITE_DONE, or if sqlite3_step(S) has never before been called on S, then sqlite3_reset(S) returns SQLITE_OK.

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.

This is probably a legacy of the initial goofy interface of the sqlite3_step, which returned only generic SQLITE_ERROR code, and the one needed to call sqlite3_reset or sqlite3_finalize in order to find the specific error code, which better described the error. The interface has been updated since then to allow sqlite3_step to return the specific error codes directly, but sqlite3_reset still behaves the old way.