sqlite_reset() on an INSERT with RETURNING statement rollback/cancel it-CodePudding

I want to INSERT data in a SQLite table and do this :

sqlite3_stmt *pStmt;
sqlite3_prepare(db,"INSERT INTO table(col2,col3) VALUES (?,?) RETURNING col1;",-1,&pStmt,NULL);
for (int i = 0; i < dataset_length; i  ) {
    sqlite3_bind_int(pStmt,1,dataset[i].value1);
    sqlite3_bind_int(pStmt,2,dataset[i].value2);
    switch (sqlite3_step(pStmt)) {
        case SQLITE_ROW: {
            // Nice! A row has been inserted.
            dataset[i].id = sqlite3_column_int(pStmt,0);
        } break;
        case SQLITE_DONE: {
            // No results. What? Return an error.
        } return false;
        default: {
            // Return an error
        } return false;
    }
    // ↓ Problem below ↓
    sqlite3_reset(pStmt);
}
//sqlite3_cleanup(pStmt); <- Don't worry about cleanups
return true;

sqlite3_step() always returns SQLITE_ROW and the RETURNING expression works.

If I do a SELECT before the sqlite3_reset(), it returns the freshly inserted row. If I prepare and run the same query after the sqlite3_reset(), my table is empty, the row is vanished.

I tried without the sqlite3_reset() and that works, but I don't understand why and think it's due to the auto-reset feature I OMIT in the Windows build.

Where I am wrong in my SQLite usage?

CodePudding user response：

I finally find out where I was wrong. SQLite mandate to call sqlite_reset() only after receiving an error or SQLITE_DONE.

In my code I only generate a SQLITE_ROW, but I sqlite_reset() before getting a SQLITE_DONE and it cause SQLite to somewhat "reset" the statement and rolling back changes from my point of view.

The correct way is to after a SQLITE_ROW, to call sqlite_step() again that generate a SQLITE_DONE and then sqlite_reset(). That means :

// The way to SQLite with a RETURNING INSERT
for (...) {
    // sqlite3_bind...()
    sqlite3_step();  // Returns SQLITE_ROW
    // sqlite3_column...()
    sqlite3_step();  // Returns SQLITE_DONE
    sqlite3_reset(); // Returns SQLITE_OK
}

Here is below my fixed code from my question :

sqlite3_stmt *pStmt;
sqlite3_prepare(db,"INSERT INTO table(col2,col3) VALUES (?,?) RETURNING col1;",-1,&pStmt,NULL);
for (int i = 0; i < dataset_length; i  ) {
    sqlite3_bind_int(pStmt,1,dataset[i].value1);
    sqlite3_bind_int(pStmt,2,dataset[i].value2);
    switch (sqlite3_step(pStmt)) {
        case SQLITE_ROW: {
            // Nice! A row has been inserted.
            dataset[i].id = sqlite3_column_int(pStmt,0);
            // Generate a SQLITE_DONE
            if (sqlite3_step(pStmt) != SQLITE_DONE)
                // Something went wrong, return an error
                return false;
        } break;
        case SQLITE_DONE: {
            // No results. What? Return an error.
        } return false;
        default: {
            // Return an error
        } return false;
    }
    sqlite3_reset(pStmt);
}
//sqlite3_cleanup(pStmt); <- Don't worry about cleanups
return true;

Of course my code imply that there is only 1 row returned by SQLite, adapt your code if SQLite returns more. The rule is that a sqlite_step() must returns a SQLITE_DONE before doing a sqlite_reset().