Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

SQLite DB at Runtime



  • 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())
    

  • Lifetime Qt Champion

    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



  • @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();
    

  • Lifetime Qt Champion

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



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


  • Lifetime Qt Champion

    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]



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


  • Lifetime Qt Champion

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



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


  • Lifetime Qt Champion

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



  • @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.


  • Lifetime Qt Champion

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



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


  • Lifetime Qt Champion

    @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:"...



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

    db.setDatabaseName(dbPath);
    

  • Lifetime Qt Champion

    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.



  • @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.



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


Log in to reply