using model to fill tableView with QMYSQL

[Christian Ehrlicher]

@Thank-You said in using model to fill tableView with QMYSQL:

QSqlQuery *query = new QSqlQuery(db);

And don't create it on the heap - otherwise the query is leaking.

[JonB]

@Thank-You said in using model to fill tableView with QMYSQL:

    db.open();
    if(db.open()){

And don't do that, it makes no sense, if you understand what you are doing.

[Thank You]

@JonB Ok , I can remove it
But may be I was checking it only 🤣🤣🤣😂

[Thank You]

@Christian-Ehrlicher

QSqlQueryModel *model = new QSqlQueryModel();
QSqlQuery query(db);
query.prepare(command);
 if(query.exec()){
           model->setQuery(query);
           ui->report->setModel(model);
}

This one doesn't work either

How can I solve it?

[Christian Ehrlicher]

@Thank-You said in using model to fill tableView with QMYSQL:

How can I solve it?

Did you read @VRonin 's message?

[Thank You]

But why the same thing is working with
SQLITE
and not with
MYSQL

[JonB]

@Thank-You
Who knows, they are different databases with different driver code & behaviour.

Why do you remove the database? What behaviour do you expect after the remove? Why do you care if the behaviour is different between SQLite versus MySQL if the code is wrong and can be corrected?

[Thank You]

@Christian-Ehrlicher
Yes it works

QSqlDatabasePrivate::addDatabase: duplicate connection name 'report', old connection removed.

But this is error is very very disgusting. Like we should remove the connection,
Can you say another answer or different way??

@JonB Because I read somewhere that removing database is always great
https://doc.qt.io/qt-5/qsqldatabase.html#removeDatabase

[JonB]

@Thank-You
But you have an ongoing ui->information->setModel(model) which is still using the database, and you wonder why it goes wrong after you have done a removeDatabase()? Where does it say that situation is a good idea?

[jsulm]

@Thank-You said in using model to fill tableView with QMYSQL:

QSqlDatabasePrivate::addDatabase: duplicate connection name 'report', old connection removed.

Simply add the database once...

[Thank You]

@jsulm
https://doc.qt.io/qt-5/qsqldatabase.html#removeDatabase
Warning: There should be no open queries on the database connection when this function is called, otherwise a resource leak will occur.

{
    QSqlDatabase db = QSqlDatabase::database("sales");
    QSqlQuery query("SELECT NAME, DOB FROM EMPLOYEES", db);
}
// Both "db" and "query" are destroyed because they are out of scope
QSqlDatabase::removeDatabase("sales"); // correct

From the official documentation I found that queries should not be open.
I don't know wether I am understanding what you are saying or not. If you mean different thing, please describe in simpler words

[jsulm]

@Thank-You I was talking about ADDING database.
"QSqlDatabasePrivate::addDatabase: duplicate connection name 'report', old connection removed." - this means you're trying to add a connection which already was added...

What you posted now shows again that you still are removing the database though you was told several times already to not to do so. Why?!

[JonB]

@Thank-You said in using model to fill tableView with QMYSQL:

From the official documentation I found that queries should not be open.

Again: in your case the query is not out of scope/destroyed because you have:

        QSqlQueryModel *model = new QSqlQueryModel();
        QSqlQuery *query = new QSqlQuery(db);
        model->setQuery(*query);
        ui->information->setModel(model)

[Thank You]

@jsulm
If I am right ,
We get this error when we didn't close the database properly.

Please see my code at very top ,
There I created database and after executing it
I just removed the database

Yes as said by @Christian-Ehrlicher and @VRonin If I remove this line it actually works but this error is obtained.

Again I will tell you ,
It was working fine when I used the "SQLITE" database, The same code. Even there is no fault in query too.
What is difference between using these database, Is it due to ASYNCHRONUS thing?
Am I right ??

[JonB]

@Thank-You
We have tried to tell you. And one more time: just because bad code might work with SQLite but behave differently with MySQL does not mean we can tell you why you might get away with it in some circumstances.

Why don't you just think about what you are doing? If you add database, open a query onto it and then set a permanent model to that which is referenced by an outside-world QTableView, and then you remove the database while that is still the case, what do you actually expect to happen? Can you not see this might be a problem?

• Don't remove the database while you have a view/model using it.
• Don't re-add the same database while you have previously added it and not yet removed it.

Frankly I don't understand your whole architecture of opening a database while you are displaying a message box, and leaving it to persist into the outside world. The usual approach is to open a database initially/first time wanted and persist it till you are ready to close it and never want it again. I wouldn't be adding/removing the same database repeatedly.

[Thank You]

Don't remove the database while you have a view/model using it

How can I know that it is using the database or not??

I just tried inserting QMessageBox when

if(query->exec()){
// same as the upper code here 
QMessageBox::information(this,"NOw the database is shown", " This is also shown");
}

After clicking OK,

The data vanishes again
@jsulm @JonB can you see this?

[JonB]

@Thank-You
After showing the message box, does your code execute QSqlDatabase::removeDatabase()? Just a "yes" or "no" answer.

[Thank You]

@JonB
No it doesn't
It executes
removeDatabase
at the very end of the program
like

{
db initialize
execute query and set model
show the message box
}
remove the database

or you can see the code same but there is
messagebox inside if(query->exec){
// all execution like model etc
messagebox
}

[JonB]

@Thank-You
If you really want anyone to look at this, show your actual relevant, minimal code which reproduces the issue, not a description of it.

[Thank You]

@JonB I have posted the code right at the top
Exactly that code brings this error

void tableFill::fill_table(){
QString command = "select * from countryDetails;";
   {
QString databaseName = "root";
QString databasePassword= "";
QString databaseUsername= "root";
QString databaseHost = "localhost";
int databaseName = 3306;


       QSqlDatabase db =  QSqlDatabase::addDatabase("QMYSQL");
       db.setDatabaseName(databaseName);
db.setPort(databasePort);
db.setHostName(databaseHostname);
db.setUserName(databaseUsername);
db.setPassword(databasePassword);
    
       if(db.open()){
           QSqlQueryModel *model = new QSqlQueryModel();
           QSqlQuery *query = new QSqlQuery(db);
           query->prepare(command);
           if(query->exec()){
               model->setQuery(*query);
               ui->information->setModel(model);
//setting the model 
           }else{
               QMessageBox::warning(this,"ERrors in query exec" ,"Error while using query execution"+query->lastError().text());
           }

       }else{
           QMessageBox::warning(this,"Error in database ", "Database is not connected "+db.lastError().text());
       }

   }
   QSqlDatabase::removeDatabase("qt_sql_default_connection"); 
}