How to use relative path on SQLite .db file on iOS

Kiovtorov

I recently built my app on my local iPhone. A problem accrued when I tried to log in. I got
Database query failed: No query. Unable to fetch row On iPhone Simulator it worked flowlessly. The drivers are built and I don't know why I can't log in. How can I at least debug it

Kiovtorov

FINALLY!
I don't know what is the difference between a db file and sqllite file but with .sqlite file the copying from .qrc file works
Here is my code for everyone with the same problem


void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Use QStandardPaths to get a writable location
    QString dbName = "identifier.sqlite";
    QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
    QString fullDbPath = dbLocation + "/" + dbName;

    // Set the SQLite database file location
    m_db.setDatabaseName(fullDbPath);

    qDebug() << "Database path:" << fullDbPath;

    // Check if the database file exists in the writable location
    if (!QFile::exists(fullDbPath)) {
        qDebug() << "Database does not exist in Documents, copying from resources.";

        // Path to the resource database file
        QString resourceDbPath = ":/resources/identifier.sqlite"; // Adjust the path to your resource file

        // Attempt to copy the database from resources to the writable location
        if (QFile::copy(resourceDbPath, fullDbPath)) {
            qDebug() << "Database copied successfully to Documents.";
            // Set file permissions to writable (necessary for some platforms)
            QFile::setPermissions(fullDbPath, QFileDevice::ReadOwner | QFileDevice::WriteOwner);
        } else {
            qDebug() << "Failed to copy database from resources.";
        }
    }

    // Open the database connection and handle success or failure
    if (m_db.open()) {
        qDebug() << "Database opened successfully.";

        // Sample query to verify the database contents
        QSqlQuery query(m_db);
        query.prepare("SELECT * FROM users WHERE username = :username");
        query.bindValue(":username", "test");

        if (!query.exec()) {
            qDebug() << "Error executing query:" << query.lastError().text();
        } else {
            while (query.next()) {
                qDebug() << "Username:" << query.value("username").toString();
            }
        }
    } else {
        // Handle connection error
        qDebug() << "Database failed to open.";
        qDebug() << m_db.lastError().text();
    }
}

jsulm

@Kiovtorov said in SQLite failed on local iOS app:

A problem accrued when I tried to log in

What do you mean with "log in"?
You will need to debug to see what is happening. From what you posted impossible to say.
Posting your code can also help.

Kiovtorov

My suggestion is that the problem occurred because of the absolute path in my databasemanager class

#include "databasemanager.hpp"

DatabaseManager::DatabaseManager() {

}

QSqlDatabase DatabaseManager::GetDatabase() {
    return m_db;
}

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Set the SQLite database file location (ensure the path is correct)
    m_db.setDatabaseName("/Users/boyankiovtorov/Desktop/Finbank/resources/Finbank.db");

    // Open the database connection and handle success or failure
    if (m_db.open() && m_db.isOpen()) {
        qDebug() << "Database opened successfully";
    } else {
        // Handle connection error
        qDebug() << "Database failed to open";
        qDebug() << m_db.lastError().text();
    }
}

void DatabaseManager::CloseConnection() {
    // Close the database connection
    m_db.close();
}

I tried to use a resource file which didn't work out. What can I do to make the database available for my local iOS device

SGaist

Hi,

That path does not and cannot exist on your phone.

Use QStandardPaths to request a suitable location to store your database.

If you have a prebuilt database, you can copy it to that location and work on that copy.

Kiovtorov

@SGaist
Hi I tried using QStandardPaths but did not manage to make it work

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Determine the writable location for the database
    QString writablePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QString databasePath = writablePath + "/Finbank.db";

    // Ensure the writable directory exists
    QDir dir(writablePath);
    if (!dir.exists()) {
        dir.mkpath(writablePath);
    }

    if (!QFile::exists(databasePath)) {
        QFile resourceFile("qrc:/resources/Finbank.db");
        if (resourceFile.exists()) {
            if (!resourceFile.copy(databasePath)) {
                qDebug() << "Failed to copy database to writable location:" << resourceFile.errorString();
                return;
            }
        } else {
            qDebug() << "Database resource file not found.";
            return;
        }
    }

    // Set the SQLite database file location
    m_db.setDatabaseName(databasePath);

    // Open the database connection and handle success or failure
    if (m_db.open() && m_db.isOpen()) {
        qDebug() << "Database opened successfully at" << databasePath;
    } else {
        // Handle connection error
        qDebug() << "Database failed to open";
        qDebug() << m_db.lastError().text();
    }
}

I got an error saying
Parameter count mismatch I don't think it even uses the same database file I want it to use

SGaist

When using SQLite, the open always succeed unless the file creation failed.

You should check the size of the db file before opening it.
Also, since you are testing right now, you should nuke the file at startup to ensure it's not some leftover that is causing trouble.

Finally, unrelated, as per the documentation: do not keep a QSqlDatabase member variable.

Kiovtorov

@SGaist
I got that the size of my database is 0 bytes which means that the copying of the file doesn't work but I can't figure it out

jsulm

@Kiovtorov said in How to use relative path on SQLite .db file on iOS:

which means that the copying of the file doesn't work

So, you see "Failed to copy database to writable location:" in the log?

Kiovtorov

I tried in a test app that I can not copy a file from a resource file (.qrc). I am now searching for another way

JonB

@Kiovtorov
First try again on QFile::copy() but remove the qrc, keep the :, so QFile resourceFile(":/resources/Finbank.db");. https://stackoverflow.com/questions/10724899/how-to-copy-qrc-resource-file-into-filesystem
https://forum.qt.io/topic/13131/how-to-extract-a-resource-from-qrc-solved
Hopefully that does work? I think it's supposed to.

If it really does not work, you would have to do the copy yourself via QFile::open(), read content, write to extracted file location.

SGaist

@JonB is correct, I missed that in your code. qrc:/ is used in QML because it uses URI however, in the C++ world, it's :/ that is used to signify that you want to access a file in the resource system.

Kiovtorov

Hi @JonB

Here is the corrected path but it doesn't work

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Determine the writable location for the database
    QString writablePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QString databasePath = writablePath + "/Finbank.db";

    // Ensure the writable directory exists
    QDir dir(writablePath);
    if (!dir.exists()) {
        dir.mkpath(writablePath);
    }

    if (!QFile::exists(databasePath)) {
        QFile resourceFile(":/resources/Finbank.db");
        if (resourceFile.exists()) {
            if (resourceFile.open(QIODevice::ReadOnly)) {
                QFile destinationFile(databasePath);
                if (destinationFile.open(QIODevice::WriteOnly)) {
                    destinationFile.write(resourceFile.readAll());
                    resourceFile.close();
                    destinationFile.close();
                    qDebug() << "Database successfully copied to writable location.";
                } else {
                    qDebug() << "Failed to open destination file:" << destinationFile.errorString();
                }
            } else {
                qDebug() << "Failed to open resource file:" << resourceFile.errorString();
            }
        } else {
            qDebug() << "Database resource file not found.";
        }
    }

    // Set the SQLite database file location
    m_db.setDatabaseName(databasePath);

    // Open the database connection and handle success or failure
    if (m_db.open() && m_db.isOpen()) {
        qDebug() << "Database opened successfully at" << databasePath;
    } else {
        // Handle connection error
        qDebug() << "Database failed to open";
        qDebug() << m_db.lastError().text();
    }
}

I get an output of

Database opened successfully at "/Users/boyankiovtorov/Library/Application Support/appFinbank/Finbank.db"

I was unable to access that directory to check the file but nevertheless I think it is generated .db file which is 0 bytes and not the db file from the resource file.
I am currently looking at a stackoverflow case - https://stackoverflow.com/questions/50828041/how-to-open-database-sqlite-file-on-iphone-real-device

But i am getting no success so far because I do not have Application Support folder like him

Kiovtorov

@JonB AI thought of copying the database from the CMakeFIle.txt which worked

cmake_minimum_required(VERSION 3.16)

project(sqlite VERSION 0.1 LANGUAGES CXX)

set(CMAKE_CXX_STANDARD_REQUIRED ON)

find_package(Qt6 6.5 REQUIRED COMPONENTS Quick Sql)

qt_standard_project_setup(REQUIRES 6.5)

qt_add_executable(appsqlite
    main.cpp
)

qt_add_qml_module(appsqlite
    URI sqlite
    VERSION 1.0
    QML_FILES
        Main.qml
        SOURCES maketransactions.hpp maketransactions.cpp
        RESOURCES resources.qrc
)

# Qt for iOS sets MACOSX_BUNDLE_GUI_IDENTIFIER automatically since Qt 6.1.
# If you are developing for iOS or macOS you should consider setting an
# explicit, fixed bundle identifier manually though.
set_target_properties(appsqlite PROPERTIES
#    MACOSX_BUNDLE_GUI_IDENTIFIER com.example.appsqlite
    MACOSX_BUNDLE_BUNDLE_VERSION ${PROJECT_VERSION}
    MACOSX_BUNDLE_SHORT_VERSION_STRING ${PROJECT_VERSION_MAJOR}.${PROJECT_VERSION_MINOR}
    MACOSX_BUNDLE TRUE
    WIN32_EXECUTABLE TRUE
)

target_link_libraries(appsqlite
    PRIVATE Qt6::Quick Qt6::Sql
)

include(GNUInstallDirs)
install(TARGETS appsqlite
    BUNDLE DESTINATION .
    LIBRARY DESTINATION ${CMAKE_INSTALL_LIBDIR}
    RUNTIME DESTINATION ${CMAKE_INSTALL_BINDIR}
)


# Add the database file to the bundle
set(APP_RESOURCES Finbank.db)

# Copy resources to the macOS/iOS bundle or build directory
foreach(resource ${APP_RESOURCES})
    configure_file(${resource} ${resource} COPYONLY)
endforeach()

# Ensure it is included in the app bundle
if(APPLE)
    set_source_files_properties(${APP_RESOURCES} PROPERTIES MACOSX_PACKAGE_LOCATION "Resources")
endif()

# Include it in the target
target_sources(appsqlite PRIVATE ${APP_RESOURCES})

The problem there is that for different OSs the path to this db file is different. It generates it at

/Users/boyankiovtorov/Documents/sqlite/build/Qt_6_8_0_for_macOS-Debug/Finbank.db

Do you think it is a good idea?

jsulm

@Kiovtorov But do you see any of the errors you're printing (like "Failed to open resource file:"")?

Kiovtorov

@jsulm
No, I am getting

Database opened successfully at "/Users/boyankiovtorov/Library/Application Support/appFinbank/Finbank.db"

but it is just because it automatically generates it.

jsulm

@Kiovtorov said in How to use relative path on SQLite .db file on iOS:

but it is just because it automatically generates it.

If it automatically generates it the copying failed.
Run through debugger step by step to see what happens.

Kiovtorov

I just tested a new method which works (kind of)

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Use QStandardPaths to get a writable location
    QString dbName = "identifier.sqlite";
    QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
    QString fullDbPath = dbLocation + "/" + dbName;

    // Set the SQLite database file location
    m_db.setDatabaseName(fullDbPath);

    qDebug() << "Database path:" << fullDbPath;

    // Check if the database file exists
    if (!QFile::exists(fullDbPath)) {
        qDebug() << "Database does not exist, creating a new one.";
    }

    // Open the database connection and handle success or failure
    if (m_db.open()) {
        qDebug() << "Database opened successfully";

        if (!QFile::exists(fullDbPath)) {
            // If the database is newly created, initialize tables
            QSqlQuery query(m_db);
            query.prepare("SELECT * FROM users WHERE username = :username");
            query.bindValue(":username", "test");

            if (!query.exec()) {
                qDebug() << "Error creating settings table:" << query.lastError().text();
            } else {
                qDebug() << "Settings table created successfully.";
                qDebug() << "lite: " << query.value("username");
            }
        }
    } else {
        // Handle connection error
        qDebug() << "Database failed to open";
        qDebug() << m_db.lastError().text();
    }
}

it creates a database in my Documents folder or the ios devide documents folder. I tested if it works with creating a new table

QSqlQuery query(db);
            query.prepare("CREATE TABLE settings ("
                          "`id` INTEGER PRIMARY KEY AUTOINCREMENT, "
                          "`username` TEXT, "
                          "`password` TEXT, "
                          "`remember_login` TEXT);");
            if (!query.exec())
            {
                qDebug() << "ERROR: FAILED TO CREATE TABLE -" << query.lastError().text();
            }

            query.prepare("INSERT INTO settings (username, password, remember_login) VALUES ('aaa', 'bbb', '1')");
            query.exec();

            query.prepare("SELECT * FROM settings");
            query.exec();

and it works on macos and ios. Before I couldn't even find the file so I can now at least debug more. I will now try to copy the data from my original sqlite file to that one in the documents

Kiovtorov

FINALLY!
I don't know what is the difference between a db file and sqllite file but with .sqlite file the copying from .qrc file works
Here is my code for everyone with the same problem


void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Use QStandardPaths to get a writable location
    QString dbName = "identifier.sqlite";
    QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
    QString fullDbPath = dbLocation + "/" + dbName;

    // Set the SQLite database file location
    m_db.setDatabaseName(fullDbPath);

    qDebug() << "Database path:" << fullDbPath;

    // Check if the database file exists in the writable location
    if (!QFile::exists(fullDbPath)) {
        qDebug() << "Database does not exist in Documents, copying from resources.";

        // Path to the resource database file
        QString resourceDbPath = ":/resources/identifier.sqlite"; // Adjust the path to your resource file

        // Attempt to copy the database from resources to the writable location
        if (QFile::copy(resourceDbPath, fullDbPath)) {
            qDebug() << "Database copied successfully to Documents.";
            // Set file permissions to writable (necessary for some platforms)
            QFile::setPermissions(fullDbPath, QFileDevice::ReadOwner | QFileDevice::WriteOwner);
        } else {
            qDebug() << "Failed to copy database from resources.";
        }
    }

    // Open the database connection and handle success or failure
    if (m_db.open()) {
        qDebug() << "Database opened successfully.";

        // Sample query to verify the database contents
        QSqlQuery query(m_db);
        query.prepare("SELECT * FROM users WHERE username = :username");
        query.bindValue(":username", "test");

        if (!query.exec()) {
            qDebug() << "Error executing query:" << query.lastError().text();
        } else {
            while (query.next()) {
                qDebug() << "Username:" << query.value("username").toString();
            }
        }
    } else {
        // Handle connection error
        qDebug() << "Database failed to open.";
        qDebug() << m_db.lastError().text();
    }
}

Kiovtorov

Here is the same code using appDataLocation

void DatabaseManager::OpenConnection() {
    // Configure the database connection parameters for SQLite
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    // Use QStandardPaths to get the application data location
    QString dbName = "identifier.sqlite";
    QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QString fullDbPath = dbLocation + "/" + dbName;

    QDir dbDir(dbLocation);
    if (!dbDir.exists()) {
        if (dbDir.mkpath(dbLocation)) {
            qDebug() << "Created application data directory:" << dbLocation;
        } else {
            qDebug() << "Failed to create application data directory:" << dbLocation;
            return; // Exit if the directory cannot be created
        }
    }

    // Set the SQLite database file location
    m_db.setDatabaseName(fullDbPath);

    // Check if the database file exists in the application data location
    if (!QFile::exists(fullDbPath)) {
        qDebug() << "Database does not exist in AppDataLocation, copying from resources.";

        // Path to the resource database file
        QString resourceDbPath = ":/resources/identifier.sqlite"; // Adjust the path to your resource file

        // Attempt to copy the database from resources to the writable location
        if (QFile::copy(resourceDbPath, fullDbPath)) {
            qDebug() << "Database copied successfully to AppDataLocation.";
            // Set file permissions to writable (necessary for some platforms)
            QFile::setPermissions(fullDbPath, QFileDevice::ReadOwner | QFileDevice::WriteOwner);
        } else {
            qDebug() << "Failed to copy database from resources.";
            return; // Exit if the database cannot be copied
        }
    }

    // Open the database connection and handle success or failure
    if (m_db.open()) {
        qDebug() << "Database opened successfully.";

        // Sample query to verify the database contents
    } else {
        // Handle connection error
        qDebug() << "Database failed to open.";
        qDebug() << m_db.lastError().text();
    }
}