SQLite3 in Unreal Engine 4

Up­dat­ing the Plug­in

This was a mat­ter of mere­ly up­dat­ing a sin­gle build file. As sqlite hadn’t been up­dat­ed since 2016, I did that as well.

Oth­er Im­prove­ments

There were a cou­ple of mi­nor things that were easy to clean up, e.g. us­ing con­st FString& in­stead of con­st FString or FString for method pa­ram­e­ters, since this avoids copy­ing the string.

Oth­er cleanups I made in­clude up­dat­ing use of now dep­re­cat­ed UE4 API, ex­pose some pri­vate meth­ods that turned out to be use­ful dur­ing de­vel­op­ment, some repos­i­to­ry main­ta­nence (adding a .git­ig­nore) and a per­for­mance op­ti­miza­tion that works by al­low­ing you to leave the sqlite3 data­base open in­stead of open­ing and clos­ing it for ev­ery op­er­a­tion. In sin­gle con­nec­tion use cas­es this works great.

In­stal­la­tion

Ei­ther down­load a zip and ex­tract it in­to a sub­di­rec­to­ry of your Plug­ins dir in your Un­re­al En­gine project (e.g. <MyGame>/Plugins/unreal-sqlite3/) or—if you al­ready use git—add it as a sub­mod­ule:

cd Plugins/
git submodule add https://github.com/Squareys/unreal-sqlite3

Then en­able it in the plug­in set­tings of your Un­re­al En­gine project.

Ba­sic Use

While the plug­in can do some fan­cy stuff like pop­u­late prop­er­ties of an Ac­tor, the main thing you’ll need is to run some SQL.

A sim­ple ex­am­ple for a high­scores ta­ble could look like this:

const FString DB_NAME = "DB";

/* Connect to the database */
bool connect() {
    const FString databaseFile = FPaths::ConvertRelativePathToFull(
            FPaths::Combine(FPaths::ProjectSavedDir(), FString("my.db")));

    if (!USQLiteDatabase::IsValidDatabase(databaseFile, false)) {
        if (!USQLiteDatabase::CreateDatabase(databaseFile, false)) {
            UE_LOG(LogTemp, Error, TEXT("Couldn't create database %s"), *databaseFile);
            return false;
        } else {
            UE_LOG(LogTemp, Log, TEXT("Created database %s"), *databaseFile);
        }

        /* Register database, cannot be registered yet, because file did not exist. */
        bool success = USQLiteDatabase::RegisterDatabase(DB_NAME, databaseFile, false, true);
        if(!success) {
            UE_LOG(LogTemp, Error, TEXT("Couldn't register database %s"), *databaseFile);
            return false;
        } else {
            UE_LOG(LogTemp, Log, TEXT("Registered database %s"), *databaseFile);
        }

        /* Run some SQL without return value! */
        success = USQLiteDatabase::ExecSql(DB_NAME, TEXT(
                    "CREATE TABLE Highscores("
                        "id INTEGER PRIMARY KEY ASC,"
                        "score INTEGER,"
                        "player TEXT"
                    ")"));
        if(!success) {
            UE_LOG(LogTemp, Error, TEXT("Couldn't create table"));
            return false;
        }
    }

    return true;
}

Where the fol­low­ing line is one of the more im­por­tant:

bool success = USQLiteDatabase::RegisterDatabase(DB_NAME, databaseFile, false, true);

Which reg­is­ters (stores the file­name for giv­en data­base name and checks if the file can be opened) the data­base with giv­en file, false in­di­cat­ing that the path is not rel­a­tive to the project and true keep­ing it open for per­for­mance—which you may not need, though, es­pe­cial­ly in the case of high­scores.

If you do keep the data­base open, you should close it some­time:

USQLiteDatabase::UnregisterDatabase(DB_NAME);

Run­ning a Query with Re­sult

Run­ning SQL with­out re­turn val­ues can still be very use­ful if you just want to store sta­tis­tics and lat­er eval­u­ate them “off­line” (as in with a dif­fer­ent ap­pli­ca­tion 1 and not dur­ing game run­time). If we want to show some sta­tis­tics to the us­er—and I bet he’d be in­ter­est­ed in that high­score list—we will need to be able to query some da­ta, though:

const FString playerName = "Squareys";
int64 out = -1;

TUniquePtr<SQLiteQueryResult> result = USQLiteDatabase::RunQueryAndGetResults(DB_NAME,
    FString::Printf(
        TEXT("SELECT score FROM Highscores WHERE player='%s'"), playerName));
if (result->Success && result->Results.Num() > 0 && result->Results[0].Fields.Num() > 0) {
    out = result->Results[0].Fields[0].IntValue;
}
else {
    UE_LOG(LogTemp, Error, TEXT("Could not get integer output: %s"), *(result->ErrorMessage));
}

You will no­tice that I pass pa­ram­e­ters to SQL us­ing FString::Printf.

Un­der­neath a pre­pared state­ment is cre­at­ed. So in case this is not suf­fi­cient for your use case and you need that, you can check the code there and pos­si­bly ex­pose pre­pared state­ments through the plug­ins API. (If you do so, I would ap­pre­ci­ate a pull­re­quest to my fork :) )

1

E.g. us­ing KN­IME

Writ­ten in 40 min­utes, ed­it­ed in 15 min­utes.