CODEWITHSUNDEEP
c++sqliteprepared-statement
TVA van Hesteren
TVA van Hesteren

Reputation: 1221

C++ SQLite3 not deleting with prepared statement

I have the following code in C++ to DELETE all ID's which are not in the vector IDs. The ID's that should be deleted are printed, however the DELETE seems to fail because of the ID-binding. (When I remove the ID from the statement and only bind the reference, it works well).

This is how the DB is created:

CREATE TABLE IF NOT EXISTS Files (
    ID LONGTEXT DEFAULT NULL,
    Reference LONGTEXT NOT NULL,
    FilePath LONGTEXT PRIMARY KEY NOT NULL,
    ProcessedOn LONGTEXT NOT NULL)

The code to delete the right ID's:

rc = sqlite3_prepare_v2(db, "SELECT ID FROM Files WHERE Reference=? AND ID IS NOT NULL", -1, &stmt, 0);
sqlite3_bind_text(stmt, 1, Settings["Reference"].c_str(), Settings["Reference"].length(), 0);
CheckDBError(rc);
rc = sqlite3_step(stmt);
sqlite3_stmt* stmt2;
int rc2 = sqlite3_prepare_v2(db, "DELETE FROM Files WHERE ID=? AND Reference=?", -1, &stmt2, 0);
CheckDBError(rc2);
while(rc == SQLITE_ROW) {
    string IDToCheck = (const char*)sqlite3_column_text(stmt, 0);
    cout << "Checking: " << IDToCheck << endl;
    if (find(IDs.begin(), IDs.end(), IDToCheck) == IDs.end()) {
        cout << "Delete " << IDToCheck << endl;

        //SHOWS ME THE CORRECT ID's BUT THE DELETE IS NOT WORKING. THE 
        //STATEMENT IS EXECUTED PROPERLY WHEN I ONLY USE IT WITH BOUND
        //REFERENCE, SO BINDING the IDToCheck GOES WRONG?

        sqlite3_bind_text(stmt2, 1, IDToCheck.c_str(), IDToCheck.length(), 0);
        sqlite3_bind_text(stmt2, 1, Settings["Reference"].c_str(), Settings["Reference"].length(), 0);
        rc2 = sqlite3_step(stmt2);
        }
    rc = sqlite3_step(stmt);
    }
sqlite3_finalize(stmt);
sqlite3_finalize(stmt2);

Upvotes: 0

Views: 1023

Answers (1)

CL.
CL.

Reputation: 180060

The sqlite3_bind_text() documentation says:

The fifth argument to the BLOB and string binding interfaces is a destructor used to dispose of the BLOB or string after SQLite has finished with it. The destructor is called to dispose of the BLOB or string even if the call to bind API fails. If the fifth argument is the special value SQLITE_STATIC, then SQLite assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns.

You do not have a dynamic buffer that is freed with an explicit destructor, so you cannot use a function pointer. The buffer returned by c_str() is not unmanaged, so you cannot use SQLITE_STATIC. So you have to use SQLITE_TRANSIENT.

Also, you have to check for errors (check rc2, and call sqlite3_errmsg().

Also, you need to reset the statement before you can execute it a second time.

Upvotes: 1

Related Questions