CODEWITHSUNDEEP
c++sqliteunreal-engine4
Biggles
Biggles

Reputation: 51

How does one debug sqlite3_prepare_v2 and sqlite3_step not giving expected values when no error codes are generated?

I am having problems using the sqlite3 C/C++ amalgamation within the context of Unreal Engine 4...

I can open a connection to the database write data to it, verifying this using DB Browser, but running SELECT statements using sqlite3_prepare_v2 and sqlite3_step is giving unexpected behaviours. I'm checking the return values and not seeing any error codes, but I am seeing a SQLITE_DONE when I'd expect to see SQLITE_ROW returned by sqlite3_step. If I log out the result of sqlite3_expanded_sql, I get the expected statement with all parameters properly inserted and if I copy & paste this into DB Browser, I get the expected results.

The data being pulled is specific to each run of the program so I know that I'm not seeing old or irrelevant data in DB Browser, and I know that my "write" functions are also operating as expected.

What's particularly strange is that after commenting out all references to SQLPreparedStatementString (which I was using in an attempt to debug the issue) the code performs differently: I do now get one row back (instead of the expected 4).

This almost makes me suspect that there's a memory issue going on, perhaps sqlite3 and Unreal are both writing to the same sections of memory without communicating that to each other? I tend to rely on UE4's memory management and don't have a lot of C/C++ experience outside of UE4, so my knowledge of things like malloc is limited pretty much just to the general feeling that "here be dragons" etc. I also understand that sqlite3_prepare_v2 and sqlite3_step are functions that tend to do a lot of memory allocation, but if that's what's causing the problem, I'm surprised that it's only occuring so rarely, since I'm successfully calling them several thousand times (to add data) on each game run.

I understand that it's possible to set compile-time options for sqlite that pre-allocate it a memory block so that it doesn't use malloc at all during runtime, but I've not quite worked out how to do that in the context of including the amalgamation in UE4 just yet.

Relevant code below:

TArray<FName> UOzyBrainLogger::GetLoggedCivsForTurn(const int32 TurnNumber)
{
    TArray<FName> rVal;
    
        sqlite3_stmt *SQLStatement;
        FString SQLCommand = "SELECT DISTINCT CivName FROM Decision WHERE (GameID = ? AND TurnNumber = ?) ;";
        int rc;
        
        rc = sqlite3_prepare_v2(DB, TCHAR_TO_UTF8(*SQLCommand), -1, &SQLStatement, NULL);
        if (rc == SQLITE_OK)
        {
            //FString SQLPreparedStatementString;
            UE_LOG(OzyBrainLogger, Log, TEXT("Getting Civs logged for turn %d in game %s"), TurnNumber, *GameID);
            if(sqlite3_bind_text(  SQLStatement, 1, TCHAR_TO_UTF8(*GameID), -1, SQLITE_STATIC) == SQLITE_OK &&
               sqlite3_bind_int(   SQLStatement, 2, TurnNumber)                                == SQLITE_OK)
            {
                //SQLPreparedStatementString = UTF8_TO_TCHAR(sqlite3_expanded_sql(SQLStatement));
                //UE_LOG(OzyBrainLogger, Log, TEXT("Successfully created statement: %s"), *SQLPreparedStatementString);
                rc = sqlite3_step(SQLStatement);
                if (rc == SQLITE_DONE)
                {
                    UE_LOG(OzyBrainLogger, Log, TEXT("No results found?"));
                }
                else if (rc != SQLITE_ROW)
                {
                    UE_LOG(OzyBrainLogger, Log, TEXT("Return code was: %d"), rc);
                }
                while (rc == SQLITE_ROW)
                {
                    FName CivName = FName(*FString(UTF8_TO_TCHAR((const char*) sqlite3_column_text(SQLStatement, 0))));
                    //UE_LOG(OzyBrainLogger, Log, TEXT("Returning %s for turn %d via statement: %s"), *CivName.ToString(), TurnNumber, *SQLPreparedStatementString);
                    UE_LOG(OzyBrainLogger, Log, TEXT("Returning %s for turn %d"), *CivName.ToString(), TurnNumber);
                    
                    rVal.Add(CivName);
                    rc = sqlite3_step(SQLStatement);
                }
            }
            else
            {
                UE_LOG(OzyBrainLogger, Warning, TEXT("%s"), UTF8_TO_TCHAR(sqlite3_errmsg(DB)));
            }
        }
        else
        {
            UE_LOG(OzyBrainLogger, Warning, TEXT("%s"), UTF8_TO_TCHAR(sqlite3_errmsg(DB)));
        }
        sqlite3_finalize(SQLStatement);
    
    return rVal;
}

I might be missing something really obvious, but I'm not sure how to investigate this further to make progress so any advice on avenues to explore would be very helpful.

Am feeling a bit lost!

Upvotes: 1

Views: 781

Answers (1)

Biggles
Biggles

Reputation: 51

I think I managed to fix it! The problem seems to have been a combination of:

  1. Calling sqlite3_open in my object's constructor expecting singleton-like behaviour when UE4 calls constructors silently from time to time (e.g. on compile).
  2. Using SQLITE_STATIC in place of SQLITE_TRANSIENT
  3. Adding return code (rc) checking absolutely everywhere

Fixing both of these issues seems to have resolved the main problem.

The final issue (getting only 2 rows back instead of 4) was a more pedestrian rookie error (I'd rearranged the call to sqlite3_step into a do ... while loop and accidentally ended up calling sqlite3_step twice per iteration!

The things that didn't fix the main issue included calling sqlite3_config with SQLITE_CONFIG_MEMSTATUS, SQLITE_CONFIG_LOOKASIDE and SQLITE_CONFIG_HEAP, however: when I started checking the return codes for these calls properly and finding errors, that was a major clue to realising that something had already started the database connection without me realising it (i.e. UE4's silent constructor-calling on compile)

Upvotes: 2

Related Questions