Search code examples
c++sqlitedelete-row

Cannot Delete Record From SQLite3 Table

Let me start off with the code I am using to delete the records from my tables:

dData("Player_Data", bErrors);

That calls the function, pass the table name and a bool to tell if there was an error. The function:

void Database::dData(string table, bool* bErrors)
{
    sqlStr2 = "Delete From " + table;

    sqlite3_exec(dBase,"BEGIN TRANSACTION",NULL,NULL,&error);

    if (sqlite3_prepare_v2(dBase, sqlStr2.c_str(), sqlStr2.size(), &statement2, 0) == SQLITE_OK)
    {           
        sqlite3_step(statement2);
        *bErrors = false;

        finalize(statement2, bErrors);
    }

    else
    {
        *bErrors = true;
        createBInfo();      
        d.createBReport("SQL Code 3",sqlite3_errmsg(dBase),bLocale + to_string(__LINE__),bTDate,"./SC_Log.txt");
    }

    sqlite3_exec(dBase,"END TRANSACTION",NULL,NULL,&error);
}

I originally had the delete query as "Delete From" + table. This was failing to delete the record so I changed it to Delete * From based on a suggested answer from a similar question here on SO.

No matter what I try I cannot delete the single record in my Player_Data table. There are no errors generated from the delete query for Player_Data either The code in the tags at the top was causing a syntax error to be logged; returning to the proper syntax did not change the situation.

Does SQLite stop you from deleting the last record in a table from the c/c++ interfaces?

Solution

  • Alright, I have solved this problem; and a dozy of a problem it was. So, to summarize the problem quickly as a recap what was happening is that I was attempting to delete data from a table in order to update that data. However the function to carry out this operation was not producing the desired results; no data was being deleted and there were no obvious errors generated from the sql code in that function.

    After joining the SQLite3 newsgroup and posting the problem there we tracked it down. What was happening was that my begins/commits themselves were erroring and I was not catching those errors.

    Kees Nuyt from the newsgroup suggested catching and printing our error to see if there were any errors. When I did so I found that by being statement was throwing out a database locked error. After a few back and fourths I had a brain wave when I realized this code works when I start a new game but not when I load an old one.

    I did some heavy debug checking (went line-by-line) in lData in my dataSystem class (where my data loads from) and found that from the get go in there that there was a problem with unfinalized statements not allowing the database to be closed.

    Here is where we start to unravel the problem. There were three issues causing this:

    1. My code in lData was causing problems because my openDB function calls were in a place where they would be called even if the database did not need to be closed (they would need to be because I need to open my scDatabase.sqlite file to get some types of data for the loading process).

    2. My finalize statements in the getxResults functions in Database were in a position where they would not be called if something went wrong.

    3. Same as above except for my getRows function there were multiple return vectors for it to happen with.

    To fix the issue I had to fix all of these problems. Once everything was moved around to where it needed to go, no more problems with Delete From and a slue of other bugs that have been plaguing my game were fixed as well.