QSqlTableModel() empty on Ubuntu but works on Windows ?

[R-P-H]

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

[JonB]

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

[R-P-H]

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

[SGaist]

@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 ?

[R-P-H]

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

[VRonin]

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

[JonB]

@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?

[R-P-H]

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

[R-P-H]

@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");

[JonB]

@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?

?

[R-P-H]

@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 ?

[JonB]

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

[R-P-H]

@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 ?

[JonB]

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

[R-P-H]

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

[JonB]

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

[R-P-H]

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

[SGaist]

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.

[R-P-H]

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

[R-P-H]

Is there anything else I can try ?