SQLite DB at Runtime

Silenzio76

hello and sorry to bother you,

I read a lot of posts here and on the web but it seems I cannot find a solution fit to my issue.
So, I try to create a SQlite db at runtime(I have installed a messange handler in my main), following the code.
I had no success to create the file, so it's obviously impossible to connect.
Please, what's wrong?

#include "datamanager.h"
#include <QSqlDatabase>
#include <QSqlDriver>
#include <QSqlError>
#include <QSqlQuery>
#include <QApplication>
#include <QtDebug>
#include <QDir>

DataManager::DataManager()
{
    QString dbPath = QApplication::applicationDirPath().append("/data.db");
    qInfo()<<QSqlDatabase::drivers();
    qInfo()<<dbPath;

    if(QSqlDatabase::isDriverAvailable("QSQLITE")) {
        QSqlDatabase db;
        db.addDatabase("QSQLITE");
        db.setDatabaseName(dbPath);
        if (!db.open()) qWarning()<<db.lastError();
        qry = new QSqlQuery(db);
        if (!qry->exec("create table if not exists LoadingPlans (CREATE TABLE LoadingPlans (plan_id INTEGER PRIMARY KEY,"
                " plan_Name TEXT NOT NULL,"
                " plan_Lenght REAL NOT NULL,"
                " plan_Width REAL NOT NULL,"
                " plan_Height REAL NOT NULL,"
                " plan_Weight REAL NOT NULL,"
                " plan_Layers INTEGER NOT NULL DEAFULT 1,"
                " plan_Pieces INTEGER NOT NULL DEFAULT 1,"
                " plan_Pos INTEGER NOT NULL,"
                " plan_Rest INTEGER NOT NULL)")) qWarning()<<qry->lastError();

     }

}

the log:

[INFO]- [dom ago 25 15:34:59 2019]: (34) ="D:/Tests/Qt/Tutorial/cargoWORK/build-source-Desktop_Qt_5_12_2_MinGW_64_bit-Debug/debug"= (..\source\main.cpp:73, int qMain(int, char**))

[INFO]- [dom ago 25 15:35:08 2019]: (40) =("QSQLITE", "QMYSQL", "QMYSQL3", "QODBC", "QODBC3", "QPSQL", "QPSQL7")= (..\source\datamanager.cpp:13, DataManager::DataManager())
[INFO]- [dom ago 25 15:35:08 2019]: (34) ="D:/Tests/Qt/Tutorial/cargoWORK/build-source-Desktop_Qt_5_12_2_MinGW_64_bit-Debug/debug/data.db"= (..\source\datamanager.cpp:14, DataManager::DataManager())
[WARNING]- [dom ago 25 15:35:08 2019]: (81) =QSqlError("", "Driver not loaded", "Driver not loaded")= (..\source\datamanager.cpp:20, DataManager::DataManager())
[WARNING]- [dom ago 25 15:35:08 2019]: (81) =QSqlQuery::exec: database not open= (kernel\qsqlquery.cpp:391, bool QSqlQuery::exec(const QString&))
[WARNING]- [dom ago 25 15:35:08 2019]: (81) =QSqlError("", "", "")= (..\source\datamanager.cpp:31, DataManager::DataManager())

Christian Ehrlicher

I would gues you simply don't use the result of QSqlDatabase::addDatabase() correctly as explained in the documentation: https://doc.qt.io/qt-5/qsqldatabase.html#details

Silenzio76

@christian-ehrlicher I followed the Books example, with the addition of the path:

 QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");

    if (!db.open())
        return db.lastError();

Christian Ehrlicher

So what's the output of your new code? Does it work now?

Silenzio76

@christian-ehrlicher no, I need a persistent database file.
":memory:" means in-memory cached db.

SGaist

Hi,

That is correct and you had it wrong in your original code.

@silenzio76 said in SQLite DB at Runtime:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

Change ":memory:" to the path you want to use and it should work correctly now.

One additional thing, you should use QStandardPaths to get a suitable folder to store your database. On most OS, installing an application is done in a read-only part of your system, so putting your database in the same folder as your application will fail.

[edit: fixed unclear phrase about path SGaist]

Silenzio76

@sgaist Obvously it works then how can i create the db file at runtime?

SGaist

Meaning allow the user to select the folder where to store the database ?

Silenzio76

@sgaist that will be in a second phase, for now I need create the db file( db.sqlite ?)at runtime

SGaist

If the file doesn't exist, it will be created. That's how SQLite works (it's nothing related to the plugin).

Silenzio76

@sgaist ok, if will be created my question is where is put the file qith the ":memory: condition after the app is terminated?
Because the next time I will need to connect to it and retrieve the saved datas.

SGaist

There's no file created when using the special keyword ":memory:".

Silenzio76

@sgaist the problem is that, I need to create the file at the first start of the app.

Christian Ehrlicher

@silenzio76 said in SQLite DB at Runtime:

the problem is that, I need to create the file at the first start of the app.

So where is your problem - simply pass the filename to setDatabaseName() instead ":memory:"...

Silenzio76

@christian-ehrlicher done but don't work, it's in the first post:

db.setDatabaseName(dbPath);

SGaist

In your first post, your db object is invalid. addDatabase is a static method that returns a QSqlDatabase object that is the one that you should use to setup the database connection. Hence my comment on your new version using :memory:., you are using QSqlDatabase correctly on that one. So as already said, you can now use a path to a database file. If it's still not working, then check the errors and also check that you have the write permission on the folder you want to store your database in.

Oshio

@Silenzio76
I don't have the time to read your code, but take a look at my SQLiteSave library: https://github.com/Oshio09/Oshio_Qt_Static_Libs

Its not documented but you will find the interface very intuitive. Try to maintain a copy of the repository, because I just created after I saw your question. I may delete or rename it in the future.

There is a QT Test project that I wrote, read and use as an example on how to use the library.
I don't know how much you understand about Qt so I wrote some simple steps on how to run the tests at the README file.

I wrote this lib last week so I didn't had time to do an extensive test, try to run and let me know of any errors. As @SGaist pointed out QStandardPaths is good practice, SQLiteSave makes use of it.

Silenzio76

@oshio Thank You, you'ra are very usefull!!!