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

QSqlTableModel() empty on Ubuntu but works on Windows ?



  • Hi, I am trying to populate a QSqlTableModel() using a table in my Postgresql database and display it in a QTableView. Here is the first part of the code:

    QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
    db.setHostName("localhost");
    db.setPort(5432);
    db.setDatabaseName("test_db");
    db.setUserName("test_user");
    db.setPassword("test_pass");
    bool ok = db.open();
    QSqlTableModel *model = new QSqlTableModel(nullptr, db);
    if (ok){
        model->setTable("my_table_name_here");
        model->select();
    }
    

    On Windows, this works perfectly and QTableView is populated.

    However, when testing on Ubuntu 16.04 LTS the QTableView is empty. I checked the output of model->rowCount(); and it returns '0'.

    A few things to note:

    • Both Operating Systems are accessing the same database using the same credentials.
    • On Ubuntu, using a standard QSqlQuery works perfectly in accessing data from the very same database in the same application.
    • There are no errors in opening the database or anything else related.

    This really has me stumped. Why is it not working on Ubuntu ? Thanks.



  • check the return value of model->select(); and if it returns false use model->lastError() to diagnose what's going wrong



  • @VRonin said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    check the return value of model->select(); and if it returns false use model->lastError() to diagnose what's going wrong

    QSqlError("", "Unable to find table logs", "")
    

    It doesn't make sense because the table name is correct and it does exist ?


  • Lifetime Qt Champion

    Hi,

    Might be a silly question but are you sure that your database is properly populated under Linux ?



  • @SGaist said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    Hi,

    Might be a silly question but are you sure that your database is properly populated under Linux ?

    Hi, yes the database is actually hosted on the same machine. I have double checked it with pgadmin.


  • Lifetime Qt Champion

    So the server is on a different machine ?



  • @SGaist Which server ? The Windows machine I tested on is a different machine.


  • Lifetime Qt Champion

    I thought about the machine running PostgreSQL.

    Check the query made by the QSqlTableModel to see if there's anything suspicious.



  • @SGaist Which query ? How would I check it besides what I've already done.


  • Lifetime Qt Champion

    The one returned by the query method.



  • @SGaist I get the error: QSqlError("", "Driver not loaded", "Driver not loaded").


  • Lifetime Qt Champion

    Looks like you are not connected to the database.

    What version of Qt are you using ?
    How did you install it ?



  • @SGaist
    Qt Creator 4.11.2
    Based on Qt 5.14.2 (GCC 5.3.1 20160406 (Red Hat 5.3.1-6), 64 bit)

    I installed using the online-installer for Ubuntu x64 from Qt website.

    What is strange is that I am able to access the database just fine in the same application using QSqlQuery.


  • Lifetime Qt Champion

    That's the Qt Creator version.

    In any case, start your application with the QT_DEBUG_PLUGINS environment variable set to 1, it will give you more information about why the plugin does not load.



  • @SGaist I don't see any problems in loading sql drivers:

    QFactoryLoader::QFactoryLoader() checking directory path "/home/(removed)/Qt/5.13.0/gcc_64/plugins/sqldrivers" ...
    QFactoryLoader::QFactoryLoader() looking at "/home/(removed)/Qt/5.13.0/gcc_64/plugins/sqldrivers/libqsqlite.so"
    Found metadata in lib /home/(removed)/Qt/5.13.0/gcc_64/plugins/sqldrivers/libqsqlite.so, metadata=
    {
        "IID": "org.qt-project.Qt.QSqlDriverFactoryInter(removed)",
        "MetaData": {
            "Keys": [
                "QSQLITE"
            ]
        },
        "archreq": 0,
        "className": "QSQLiteDriverPlugin",
        "debug": false,
        "version": 331008
    }
    
    Got keys from plugin meta data ("QSQLITE")
    QFactoryLoader::QFactoryLoader() looking at "/home/(removed)/Qt/5.13.0/gcc_64/plugins/sqldrivers/libqsqlodbc.so"
    Found metadata in lib /home/(removed)/Qt/5.13.0/gcc_64/plugins/sqldrivers/libqsqlodbc.so, metadata=
    {
        "IID": "org.qt-project.Qt.QSqlDriverFactoryInter(removed)",
        "MetaData": {
            "Keys": [
                "QODBC3",
                "QODBC"
            ]
        },
        "archreq": 0,
        "className": "QODBCDriverPlugin",
        "debug": false,
        "version": 331008
    }
    
    Got keys from plugin meta data ("QODBC3", "QODBC")
    QFactoryLoader::QFactoryLoader() looking at "/home/(removed)/Qt/5.13.0/gcc_64/plugins/sqldrivers/libqsqlpsql.so"
    Found metadata in lib /home/(removed)/Qt/5.13.0/gcc_64/plugins/sqldrivers/libqsqlpsql.so, metadata=
    {
        "IID": "org.qt-project.Qt.QSqlDriverFactoryInter(removed)",
        "MetaData": {
            "Keys": [
                "QPSQL7",
                "QPSQL"
            ]
        },
        "archreq": 0,
        "className": "QPSQLDriverPlugin",
        "debug": false,
        "version": 331008
    }
    
    Got keys from plugin meta data ("QPSQL7", "QPSQL")
    QFactoryLoader::QFactoryLoader() checking directory path "/home/(removed)/(removed)/build-(removed)_Demo-Desktop_Qt_5_13_0_GCC_64bit-Release/sqldrivers" ...
    loaded library "/home/(removed)/Qt/5.13.0/gcc_64/plugins/sqldrivers/libqsqlpsql.so"
    QFactoryLoader::QFactoryLoader() checking directory path "/home/(removed)/Qt/5.13.0/gcc_64/plugins/accessible" ...
    QFactoryLoader::QFactoryLoader() checking directory path "/home/(removed)/(removed)/build-(removed)_Demo-Desktop_Qt_5_13_0_GCC_64bit-Release/accessible" ...
    QFactoryLoader::QFactoryLoader() checking directory path "/home/(removed)/Qt/5.13.0/gcc_64/plugins/accessiblebridge" ...
    QFactoryLoader::QFactoryLoader() checking directory path "/home/(removed)/(removed)/build-(removed)_Demo-Desktop_Qt_5_13_0_GCC_64bit-Release/accessiblebridge" ...
    QSqlDatabasePrivate::database: unable to open database: "FATAL:  role \"(removed)\" does not exist\nQPSQL: Unable to connect"
    


  • @R-P-H said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    QSqlDatabasePrivate::database: unable to open database: "FATAL: role "(removed)" does not exist\nQPSQL: Unable to connect"

    I'm not an expert, but you can Google for PSQL FATAL role and get a fair number of hits about this "permission" issue, e.g. https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist.



  • @JonB said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    @R-P-H said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    QSqlDatabasePrivate::database: unable to open database: "FATAL: role "(removed)" does not exist\nQPSQL: Unable to connect"

    I'm not an expert, but you can Google for PSQL FATAL role and get a fair number of hits about this "permission" issue, e.g. https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist.

    No, that is caused by the following line of code:

    QSqlDatabase::addDatabase("QPSQL");

    It is because at this point in the code I have not yet set the details for accessing the database. I am just loading the driver on startup. So this "error" is not the issue.


  • Lifetime Qt Champion

    @R-P-H said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    FATAL: role "(removed)" does not exist\nQPSQL: Unable to connect

    Does removed match postgres ? If not, how did you create the database in the first place ? Under which role did you create it ?



  • @SGaist No. The database is already created with a specific role that has access.

    /*Somewhere in beginning of program*/
    QSqlDatabase::addDatabase("QPSQL"); //Only called once
    
    /* Later in program when accessing the database */
    QSqlDatabase db = QSqlDatabase::database();
    db.setHostName("localhost");
    db.setPort(5432);
    db.setDatabaseName("test_db");
    db.setUserName("test_user");
    db.setPassword("test_pass");
    

    The role with access to the db and table is "test_user", created using pgadmin4 or psql.



  • @R-P-H said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    On Ubuntu, using a standard QSqlQuery works perfectly in accessing data from the very same database in the same application.

    This is what baffles me. If the problem was in the db driver, the query should fail too. I'm very confused about what's going on...



  • @VRonin
    @R-P-H has already said:

    QSqlDatabasePrivate::database: unable to open database: "FATAL: role "(removed)" does not exist\nQPSQL: Unable to connect"
    No, that is caused by the following line of code:

    QSqlDatabase::addDatabase("QPSQL");
    On Ubuntu, using a standard QSqlQuery works perfectly in accessing data from the very same database in the same application.

    So we seem to have an error at add database stage, and then QSqlQuery works fine but not QSqlTableModel. But I think the OP says this does not matter (though it would worry me).

    check the return value of model->select(); and if it returns false use model->lastError() to diagnose what's going wrong

    QSqlError("", "Unable to find table logs", "")

    Should the OP try printing out what QString QSqlTableModel::selectStatement() const returns? And/or whatever is in QSqlQuery QSqlQueryModel::query() const?



  • @VRonin said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    @R-P-H said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    On Ubuntu, using a standard QSqlQuery works perfectly in accessing data from the very same database in the same application.

    This is what baffles me. If the problem was in the db driver, the query should fail too. I'm very confused about what's going on...

    Yes, it makes no sense.



  • @JonB said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    @VRonin
    @R-P-H has already said:

    QSqlDatabasePrivate::database: unable to open database: "FATAL: role "(removed)" does not exist\nQPSQL: Unable to connect"
    No, that is caused by the following line of code:

    QSqlDatabase::addDatabase("QPSQL");
    On Ubuntu, using a standard QSqlQuery works perfectly in accessing data from the very same database in the same application.

    So we seem to have an error at add database stage, and then QSqlQuery works fine but not QSqlTableModel. But I think the OP says this does not matter (though it would worry me).

    Even if I change the code to this it still doesn't work (and the error disappears).

    QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
    db.setHostName("localhost");
    db.setPort(5432);
    db.setDatabaseName("test_db");
    db.setUserName("test_user");
    db.setPassword("test_pass");
    


  • @R-P-H
    Point taken.

    I did suggest

    Should the OP try printing out what QString QSqlTableModel::selectStatement() const returns? And/or whatever is in QSqlQuery QSqlQueryModel::query() const?

    ?



  • @JonB said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    @R-P-H
    Point taken.

    I did suggest

    Should the OP try printing out what QString QSqlTableModel::selectStatement() const returns? And/or whatever is in QSqlQuery QSqlQueryModel::query() const?

    ?

    How do I call these functions because they are protected ?



  • @R-P-H
    You sub-class from QSqlDatabaseQSqlTableModel. Personally I would always do that anyway, precisely for this reason --- you never know what you may find you need to add/alter from the supplied QSqlDatabaseQSqlTableModel. (I do this for all QWidgets too, but that's another matter.)



  • @JonB said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    @R-P-H
    You sub-class from QSqlDatabase. Personally I would always do that anyway, precisely for this reason --- you never know what you may find you need to add/alter from the supplied QSqlDatabase. (I do this for all QWidgets too, but that's another matter.)

    Can you please provide an example ?



  • @R-P-H
    Of how to sub-class in C++? It's just basic C++.

    I see @SGaist offered this 7 years ago: https://forum.qt.io/topic/30562/example-of-subclassing-qsqltablemodel/2. You'll want something like:

    // mytablemodel.h
    class MyTableModel : public QSqlTableModel
    {
    public:
    explicit MyTableModel(QObject * parent = 0, QSqlDatabase db = QSqlDatabase() ); // the same arguments as QSqlTableModel
    
    protected:
    virtual QString selectStatement() const;
    };
    
    // mytablemodel.cpp
    MyTableModel::MyTableModel(QObject * parent, QSqlDatabase db) :
    QSqlTableModel(parent, db)
    {
    // the rest of your code
    }
    
    QString MyTableModel::selectStatement() const
    {
        QString ss = QSqlTableModel::selectStatement();
        qDebug() << ss;
        return ss;
    }
    

    I don't think QSqlQuery QSqlQueryModel::query() const is virtual, so you can't override it; nor is it protected, it's public, so just call it if you need to access it.



  • @JonB I'm not quite sure how to implement this correctly. I have a class that deals with all database related stuff. Inside that class I have a function that generates the QSqlTableModel using the code you've already seen.

    Now obviously if I create another class for MyTableModel, include it and create model using MyTableModel *model = new MyTableModel(nullptr, db); instead I still get a protected function error when calling its selectStatement() member.

    What would be the correct way of testing this ? Thanks.



  • @R-P-H

    Inside that class I have a function that generates the QSqlTableModel

    You don't want to directly create a QSqlTableModel anywhere, you want it now to create a MyTableModel. E.g if you currently have a new QSqlTableModel somewhere it now goes new MyTableModel. This is basic C++ sub-classing, I don't know whether you're confused over that.

    include it and create model using MyTableModel *model = new MyTableModel(nullptr, db); instead I still get a protected function error when calling its selectStatement() member.

    Assuming you've got the first bit right, you still cannot call a protected method on an instance from outside of the MyTableModel class code, that's what protected is all about. You will need something like the definition I showed you of QString MyTableModel::selectStatement() const. You can either then write another public method in MyTableModel which calls it and the outside world can go via that public method, or (as I do) override all the methods in MyTableModel which do queries, updates etc. and have them print it out. It takes a few minutes to write these override functions, but once you have them from then on it's a lot easier to see what's going on, do logging, handle any errors etc. from the QSql... classes you use.



  • @JonB The result of selectStatement() is " ".


  • Lifetime Qt Champion

    Would you be able to provide a small step by step guide to reproduce your test database ? Meaning the command you are currently using on your Linux machine to create the role and the database.



  • @SGaist Hi, I just use psql to create the database/table/user.

    CREATE DATABASE my_db
        WITH 
        OWNER = postgres
        ENCODING = 'UTF8'
        TABLESPACE = pg_default
        CONNECTION LIMIT = -1;
    
    CREATE ROLE my_user WITH
      LOGIN
      NOSUPERUSER
      INHERIT
      NOCREATEDB
      NOCREATEROLE
      NOREPLICATION
      ENCRYPTED PASSWORD 'md5hidden';
    
    CREATE TABLE public.my_table
    (
        example_column character varying COLLATE pg_catalog."default",
    )
    
    TABLESPACE pg_default;
    
    ALTER TABLE public.my_table OWNER to postgres;
    
    GRANT INSERT, SELECT, DELETE ON TABLE public.my_table TO my_user;
    

    So for example:

    sudo -u postgres psql -c 'one of the above blocks of code'
    

    So here my_db is the database name, my_user is the role I'm using to access the table and my_table is the table name.



  • Is there anything else I can try ?


  • Lifetime Qt Champion

    Sorry I did not had time to test the database.

    One thing that you could try (I don't think it would change anything but worth a try), reorder the database setup and add your custom role and it setup both at the end.



  • @SGaist said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    Sorry I did not had time to test the database.

    One thing that you could try (I don't think it would change anything but worth a try), reorder the database setup and add your custom role and it setup both at the end.

    I guess I could try that but why would the error be

    QSqlError("", "Unable to find table logs", "")
    

    then ?


  • Lifetime Qt Champion

    Gut feeling: PostgreSQL namespace.



  • @SGaist said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    Gut feeling: PostgreSQL namespace.

    I had a look, but I don't quite understand how it applies to this...


  • Lifetime Qt Champion

    The database role must have access to the namespace in order to access the relation it contains.

    There might be a glitch there.



  • @SGaist said in QSqlTableModel() empty on Ubuntu but works on Windows ?:

    The database role must have access to the namespace in order to access the relation it contains.

    There might be a glitch there.

    Yet the same role (same exact application/code) works fine on Windows ?