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

[SOLVED] Problem with QSqlDatabase



  • Hi,

    this is my very first post here and I think that it is fine to start a new thread for this problem because i have been searching for an aswer for 2 days now. I'm working with a little project where I am using sqlite database, which I created and I am not getting it to work. I have been working with databases only since this week's monday so there is much that I don't know yet but I am eager to learn.

    So here is the code and hopefully someone can point me to right direction with it:

    @bool User::getUser() {
    bool ret = false;
    db_ = QSqlDatabase::addDatabase("QSQLITE","userConnection");
    db_.setDatabaseName("C:/Users/Jonne/Desktop/userDatabase.db.sqlite");

     // Some testing for the file
    QFileInfo fi("C:/Users/Jonne/Desktop/userDatabase.db.sqlite");
    if(QFile::exists("C:/Users/Jonne/Desktop/userDatabase.db.sqlite")) {
         qDebug() << fi.filePath();
     }
    
    if(db_.Open()) {
    
        qDebug() << "Database open.";
    
        QSqlQuery query("SELECT * FROM user;",db_);
        qDebug() << query.lastError();
    
        if (query.first()) {
    
            name_ = query.value(1).toString();
            age_ = query.value(2).toInt();
            gender_ = query.value(3).toString();
            height_ = query.value(4).toDouble();
            weight_ = query.value(5).toDouble();
            ret = true;
        }
    
    } 
    
    db_.close();
    return ret;
    

    }@

    With this code I am getting these outputs and errors:

    @"userConnection"
    QSqlError(-1, "Error opening database", "out of memory")
    QSqlDatabasePrivate::removeDatabase: connection 'userConnection' is still in use, all queries will cease to work.
    QSqlDatabasePrivate::addDatabase: duplicate connection name 'userConnection', old connection removed.
    "userConnection"
    QSqlError(-1, "Error opening database", "out of memory")
    QSqlDatabasePrivate::removeDatabase: connection 'userConnection' is still in use, all queries will cease to work.
    QSqlDatabasePrivate::addDatabase: duplicate connection name 'userConnection', old connection removed.
    "userConnection"
    QSqlError(-1, "Error opening database", "out of memory") @

    I don't for example understand why these errors are coming 3 times. I am getting the database from Desktop because I couldn't get it from the project directory.

    Earlier I got a error message which said that there is not table named user in the database and that was when I tried to load the database from the project folder. Also I have a problem with qt-creator itself because as default it puts build and source files in the same directory and I couldn't get it to change the build directory.

    Thanks in advance for any help. :)



  • About 3 times messages, I guess it is because somewhere in your program you call function getUser() at least three times. First time connection is created and every next time it is removed and created again.

    The error "QSqlError(-1, "Error opening database", "out of memory")" can be caused by location of your database. In Windows 7 for folders under "Users" there are limited access rights. It is possible that your program have no right to create and/or modify database on this location. I suggest to change location of database to "C:\Test\userDatabase.db.sqlite" for example or some other folder.



  • That is the weird part because I am pretty sure that I call the getUser() function only 1 time but I don't know why the output looks like that. I changed the location of database as you suggested but the error message is still the same.


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    First thing you should do is open your database connection only once at the start of your program since you are using SQLite. Then, if getUser is a slot, verify that you don't connect to it several times.

    Hope it helps



  • Line 12 should be
    @
    if(db_.open()) { //lowercase "o"
    @



  • So it would be a good way to make separate functions for example openDB() and something like that? Thanks I will give it a try.

    Thanks to panoski also for pointing the mistake, I forgot it because I modified the code a bit.



  • I made separate function for opening the database and I call it at the begin of my program and it solved other problems except the one that matters the most so it doesn't open the database. The error messages in the output are: @QSqlError(-1, "Error opening database", "out of memory")
    Unable to open input file: No such file or directory@

    Is there something wrong with my path or what could cause this?

    I am now using this code to open the database:

    @bool User::openDB() {

    // Find QSLite driver
    db_ = QSqlDatabase::addDatabase("QSQLITE");
    db_.setDatabaseName("C:/Databases/userDatabase.db.sqlite");
    
    // Open databasee
    if(db_.open()) {
        return true;
    } else {
        qDebug() << db_.lastError();
        return false;
    }
    

    }@


  • Lifetime Qt Champion

    How big is your database ?



  • The database is 2kB and contains only 1 row of User's information such as name and age.



  • And how did you modify the User::getUser() function?



  • The function is now like this:

    @bool User::getUser() {

    bool ret = false;
    
    if(db_.isOpen()) {
    
        QSqlQuery query("SELECT * FROM user;",db_);
        qDebug() << query.lastError();
    
        if (query.first()) {
    
            name_ = query.value(1).toString();
            age_ = query.value(2).toInt();
            gender_ = query.value(3).toString();
            height_ = query.value(4).toDouble();
            weight_ = query.value(5).toDouble();
            ret = true;
        }
    }
    
    return ret;
    

    }@



  • And I think I should have specified before but I am making a Sailfish application here, does that affect to your advice a lot? Sorry if this was a vital information :/



  • I remember a post where a user had trouble with this QSqlQuery constructor. Try to change your function like this:
    @
    bool User::getUser() {

    bool ret = false;
    
    if(db_.isOpen()) {
    
        QSqlQuery query(db_);
        if(!query.exec&#40;"SELECT * FROM user"&#41;)
            qDebug() << query.lastError();
    
        if (query.first()) {
    
            name_ = query.value(1).toString();
            age_ = query.value(2).toInt();
            gender_ = query.value(3).toString();
            height_ = query.value(4).toDouble();
            weight_ = query.value(5).toDouble();
            ret = true;
        }
    }
    
    return ret;
    

    }
    @



  • I changed the function like you said but the database isn't open so I cant test this but it looks better, thanks.



  • Of course, the error prints when opening the database.... Anyway, I suppose you have also double-checked that the path and database file name are accurate.


  • Lifetime Qt Champion

    Indeed you should have mention that earlier. Are you running this application on your Windows computer or on the Sailfish emulator/device ? In the second case I don't think it will access your hard drive to get the file hence the error "no such file or directory"



  • First in function OpenDB add this:
    @ if(db_.open()) {
    QStringList tables = db_.tables();
    qDebug() << "Tables: " << tables;
    return true;
    } else {@

    This will show you all tables in your database if connection is successful.

    1. In the beginning of function getUser add this:
      @QSqlDatabase db_ = QSqlDatabase::database();
      if(db_.isOpen()) {
      QSqlQuery query(db_);@


  • Thanks for replies. I am running the application in Sailfish emulator and I think I should now get familiar with the usage of local storage and how to get database available for my Sailfish application. After that I can continue with these comments you have given.



  • I am a bit confused now. Does someone know how I could store my existing database to local storage and how I could refer to it and use it from my c++ class/object? I did found some examples on how to access database from QML but I would like to access my database from the c++ backend.


  • Moderators

    Hi,

    This test code might help you

    @
    MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
    {
    ui->setupUi(this);

    if(ConnectLocalDB())
        GetData();
    

    }

    MainWindow::~MainWindow()
    {
    delete ui;
    }

    bool MainWindow::ConnectLocalDB()
    {
    db = QSqlDatabase::addDatabase("QSQLITE");

    QString dbpath = "/root/testd.db";
    db.setDatabaseName(dbpath);
    
    QSqlQuery query(QSqlDatabase::database());
    query.exec&#40;"CREATE TABLE testtable ("
               "id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
               "testcolumn TEXT&#41;;"
               );
    
    query.exec&#40;"insert into testtable(testcolumn&#41; values('one')");
    query.exec&#40;"insert into testtable(testcolumn&#41; values('two')");
    query.exec&#40;"insert into testtable(testcolumn&#41; values('three')");
    
    qDebug() << dbpath;
    
    return db.open();
    

    }

    bool MainWindow::GetData()
    {
    QSqlQuery query(QSqlDatabase::database());
    QString q = "select * from testtable;";
    query.exec(q);
    while(query.next())
    {
    qDebug() << query.value(1).toString();
    }
    }
    @



  • Thanks. I tried with that test code, but I get these errors when I try to open the test database to /root/test.db:

    @QSqlDatabasePrivate::database: unable to open database: "out of memory Error opening database"
    QSqlQuery::exec: database not open
    QSqlQuery::exec: database not open
    QSqlQuery::exec: database not open
    QSqlQuery::exec: database not open
    "/root/testd.db"@


  • Moderators

    Hi,

    Replace the path of the Database with your path.
    Which OS are you testing on ?



  • I tried to replace it for example with /home/nemo/.local/MyApp which I think is the place where I could store databases with the emulator. I am testing on Sailfish OS emulator on windows 8.

    I am a bit unsure also about these paths in Qt-Creator, how should I define the path if I would like to access a directory that the emulator also sees and can access?


  • Moderators

    For paths you could use "StandardLocation":http://qt-project.org/doc/qt-5.0/qtcore/qstandardpaths.html#StandardLocation-enum of QStandardPaths.
    Good to use "QStandardPaths::DataLocation" for data storage.
    Check manually if the db file was created atleast in that path.



  • In this test code I think database must be opened before you create and execute QSqlQuery.

    I am not familiar with Sailfish emulator at all. But you can test whether the problem is only in location of database. Just use the test code from above but for database name use
    db.setDatabaseName(":memory:");
    This will create temporary database in memory.



  • Thanks for your help, my problem was solved when I learned that I had to access the memory of emulator via SSH connection.



  • Great, you should
    mark the thread as [solved] then ;)


Log in to reply