Error opening/reading SQLite Database .db file - I/O Disk Error - UE 5.1

Development Programming & Scripting
, , , ,
1

Hello :smiley:

I’m pretty new to UE and C++ development, sorry if I’m not precise enough or correct…

Here is the story :

I’m working on a VR game that will run on a Meta Quest 2 under Android 10 on standalone mode and 99% of the time in offline.
The game will be driven by data that I want to store in a SQLite database file named AtmoData.db so that I can retrieve data from this file and update the data (contrary to dataTables).

For now, my .db file is stored in the /Content/Database folder in my project and i’ve set up project settings (Project > Packaging > Additional Non-Asset Directories to Package) so that the files in the Database folder are packed with the project when I build it even if those files are not uassets.

I’ve created a C++ Class named DatabaseManager that will handle connection to the DB and that I want to call from my GameMode Blueprint.

Here is my files :

DatabaseManager.h :

#pragma once

#include "SQLiteDatabase.h"
#include "CoreMinimal.h"
#include "DatabaseManager.generated.h"

UCLASS(Blueprintable, DisplayName="SQLite DB Manager", ClassGroup="SQLite DB Manager")
class LIFEVAIR_API UDatabaseManager : public UBlueprintFunctionLibrary
{
    GENERATED_BODY()
    
public:
    UFUNCTION(BlueprintCallable, meta=(CompactNodeTitle=""), Category="SQLite DB Manager ")
    TArray<FString>& getData(const FString Path);
};

DatabaseManager.c :

#include "DatabaseManager.h"

TArray<FString>& UDatabaseManager::getData(const FString Path)
{
    
    FSQLiteDatabase* SQLiteDB = new FSQLiteDatabase();
    SQLiteDB->Open(*Path, ESQLiteDatabaseOpenMode::ReadWrite);
    static TArray<FString> ResultColumns;
    UE_LOG(LogTemp, Warning, TEXT("Error while trying to access the DB"))
    
    if(SQLiteDB->IsValid())
    {
        GEngine->AddOnScreenDebugMessage(-1, 50.0f, FColor::Green, TEXT("COnnected to DB"));
        const FString Query = TEXT("select * from Domains");
        FSQLitePreparedStatement LoadStatement;
        LoadStatement.Reset();
        GEngine->AddOnScreenDebugMessage(-1, 50.0f, FColor::Blue, TEXT("Querying"));
        
        LoadStatement.Create(*SQLiteDB,*Query, ESQLitePreparedStatementFlags::Persistent);

        //IsSuccessful = LoadStatement.IsValid();
        
        ResultColumns = LoadStatement.GetColumnNames();

        LoadStatement.ClearBindings();
        LoadStatement.Destroy();
        //TODO. Do some checks to avoid crash
        SQLiteDB->Close();
        delete SQLiteDB;
        
        return ResultColumns;
    }
    UE_LOG(LogTemp, Warning, TEXT("Error while trying to access the DB"))

    return ResultColumns;
}

Here is my BP where I instantiate my class & call my function getData

image
image1795×802 179 KB

Everything is working except the opening of the AtmoData.db SQLite file…

Here is the error :
Failed to open database '../../../../../../Users/alexandre/Documents/Unreal Projects/LifeVair/Source/Database/AtmoData.db': disk I/O error

It seems like it might be a problem of write/read authorization but I’ve tried to access the DB from Jetbrain Rider and using Node JS with the SQLite3 module and that works well.

I would appreciate any suggestions !

2

Hey,

The solution was quite simple in fact, the code was not searching in the right place…
In the blueprint code, I used the Game Source Dir node that return the source folder, however, the DB file was in the Content/Database directory…

I replaced it with the Project Content Dir node to fix it :
image

3

Does the app work as Android apk?

4

Try this:

5

are you able to share the project file SQLite full code? . i will learn from your code

6

Hi, I can’t open the database file. SQLite and SQLiteSupport plugins are active. “SQLiteCore”, ‘SQLiteSupport’ elements are added in the build.cs file. But still, the problem must be on Unreal side, because I can open the file externally and it has full permissions, it’s not in a read-only directory. I can open the database in SQLite command shell and add to the table.

Code block trying to open the database: 
DatabaseManager::DatabaseManager(FString Path, ESQLiteDatabaseOpenMode OpenMode)
{
    Database = new FSQLiteDatabase();

    if (Database->Open(*Path, OpenMode))
    {
        if (Database->IsValid())
        {
            const TCHAR* SaveQuery = TEXT("replace into players (id, x, y, z) values ($id, $x, $y, $z)");
            SaveStatement.Create(*Database, SaveQuery, ESQLitePreparedStatementFlags::Persistent);

            const TCHAR* LoadQuery = TEXT("select * from players where id = $id limit 1");
            LoadStatement.Create(*Database, LoadQuery, ESQLitePreparedStatementFlags::Persistent);
        }
        else
            UE_LOG(LogTemp, Warning, TEXT("Database is not valid %s"), *Database->GetLastError());
    }
    else
        UE_LOG(LogTemp, Warning, TEXT("Failed to open database: %s"), *Database->GetLastError()); // error  raise in this line
}
The arguments I have given: 
FPaths::ProjectContentDir() + "Databases/game.sqlite", ESQLiteDatabaseOpenMode::ReadWrite

The error message: '../../../../../../Users/rosil/Documents/Unreal Projects/LearnMultiplayer/Content/Databases/game.sqlite': disk I/O error

Please help me get rid of this weird error, it took me a long time :neutral_face: