[iOS] SQLite does not save every times

[Vi67]

Hello,

I'm making a mobile app on iOS and Android.
Data are kept in memory with a SQLite DB.

Data are saved nicely in Android, but not in iOS. Some times insert are not working.
Do you have an idea where it comes from ?

I open/close data base depend on application states :

class ApplicationListener : public QObject
{
    Q_OBJECT

public:
    ApplicationListener(QObject *parent = 0) : QObject(parent)
    {
        DataBase::open();
        /*
        *   s_db = QSqlDatabase::addDatabase("QSQLITE");
        *   QString dbFile = Config::getWritableLocation() + "local.sqlite";
        *   s_db.setDatabaseName(dbFile);
        *   s_db.open();
        */

        QObject::connect(qApp, SIGNAL(applicationStateChanged(Qt::ApplicationState)), this, SLOT(onApplicationStateChanged(Qt::ApplicationState)));
        QObject::connect(qApp, SIGNAL(aboutToQuit()), this, SLOT(onApplicationQuit()));
    }

public slots:
    void onApplicationStateChanged(Qt::ApplicationState state)
    {
        if (state == Qt::ApplicationActive) {
            DataBase::open();
        }
        else {
            DataBase::close();
            /*
            *   s_db.close();
            */
        }
    }

    void onApplicationQuit()
    {
        DataBase::close();
    }
};

[the_]

@Vi67
Where in your code do you write into the database?
In the code sniplet only the open/close calls are shown. Is the app in the correct state when you try to exec an insert?
What exactly is DataBase ?

[Vi67]

Inserts are made in a class extends from Table class, there is a part :

QSqlQuery Table::insert(const QStringList& keys, const QVariantList& values)
{
    QString keysAsParam = getKeysString(true, false, false, keys);
    QString keysNoParam = getKeysString(false, false, false, keys);

    QSqlQuery query = QSqlQuery(DataBase::getDB());
    query.prepare("INSERT INTO " + m_name + " (" + keysNoParam + ") VALUES (" + keysAsParam + ");");

    for (int i = 0; i < values.size(); ++i) {
        query.bindValue(":"+keys.at(i), values.at(i));
    }

    return query;
}

QString Table::getKeysString(bool asParams, bool withTypes, bool withValues, QStringList keys)
{
    if (keys == QStringList())
        keys = m_keys;

    QString keysString = QString();

    for (int i=0 ; i<keys.size() ; ++i) {
        if (asParams)
            keysString += ":";

        keysString += keys[i];

        if (withValues)
            keysString += " = :" + keys[i];

        if (withTypes)
            keysString += " " + m_types[i];

        if (i < keys.size()-1)
            keysString += ",";
    }

    return keysString;
}

There is DataBase class :

void DataBase::open()
{
    s_db = QSqlDatabase::addDatabase("QSQLITE");

    QString dbFile = Config::getWritableLocation() + "local.sqlite";

    qDebug() << "dbFile : " << dbFile;
    
    s_db.setDatabaseName(dbFile);

    if(!s_db.open()) {
        qDebug() << "Failed to connect local data base, details : " << s_db.lastError().driverText();
        return;
    }
}

void DataBase::close()
{
    s_db.close();
}

[shav]

@Vi67 Are you saves your database file as a application resource or your app created it?

[Vi67]

@shav I put the file in DocumentsLocation

QString Config::getWritableLocation() {
#ifdef Q_OS_IOS
    return QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation) + "/";
#else
    return "";
#endif
}

[shav]

So your application created database in Documents folder of application, right? If so I think you need to check SQL query. Try to print full SQL query to debug console. Maybe some of data is incorrect.

[the_]

@Vi67

• Whats the code of your query execution? The previos sniplet only showed that you prepare the query and return it but no exec.
• Can you check if you get any error at query.exec() ?

[Vi67]

@shav @the_ Thanks for your both answers.
You're right, now I need to know the errors/log when it happens. But it happens so rarely, never when the device is connected to computer for primary tests. I need to implement a way to send logs to the server. It looks like it happens when the application is kept in background from a long time.

@the_
There is the main query execution. This part do a lot for synchronization system between local data and server data.

bool Table::setOne(QVariantMap object, QVariant previousId)
{
    if (!object.contains("_id")) {
        if (Config::debug)
            qDebug() << "!Warning! setOne - object do not have an _id";
        return false;
    }

    object.detach();

    QMapIterator<QString, QVariant> it(object);
    while (it.hasNext()) {
        it.next();
        if (!m_keys.contains(it.key()))
           object.remove(it.key());
    }

    // if object doesn't have a temporary local id
    if (previousId == QVariant()) {

        QSqlQuery selectQ = select("*", "_id = :_id", {object.value("_id")});
        selectQ.exec();

        // if exists -> update
        if (selectQ.next()) {
            QVariant idValue = object.value("_id");
            object.remove("_id");

            QSqlQuery updateQ = update(object.keys(), object.values(), "_id = :_id", {idValue});
            bool executed = updateQ.exec();
            if (Config::debug) {
                qDebug() << "setOne-!Id-Update : " << executed << updateQ.lastQuery();
                qDebug() << "setOne-!Id-Update : " << updateQ.lastError();
            }
            return executed;
        }
        // if !exists -> insert
        else {
            QSqlQuery insertQ = insert(object.keys(), object.values());
            bool executed = insertQ.exec();
            if (Config::debug) {
                qDebug() << "setOne-!Id-Insert : " << executed << insertQ.lastQuery();
                qDebug() << "setOne-!Id-Insert : " << insertQ.lastError();
            }
            return executed;
        }

    }
    // if object has a temporary local id
    else {

        // remove previous object
        QSqlQuery removeQ = remove("_id = :_id", {previousId});
        bool removeExecuted = removeQ.exec();
        if (Config::debug) {
            qDebug() << "setOne-Id-Remove : " << removeExecuted << removeQ.lastQuery();
            qDebug() << "setOne-Id-Remove : " << removeQ.lastError();
        }

        // replace it by the one from server
        QSqlQuery insertQ = insert(object.keys(), object.values());
        bool insertExecuted = insertQ.exec();
        if (Config::debug) {
            qDebug() << "setOne-Id-Insert : " << insertExecuted << insertQ.lastQuery();
            qDebug() << "setOne-Id-Insert : " << insertQ.lastError();
        }

        return removeExecuted && insertExecuted;

    }
}