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

using model to fill tableView with QMYSQL



  • I ran into huge problem at the end of the project.
    I was using sqlite as the database at the starting of the project. Configured mysql database and liked it so I decided to use it.
    Everything is working absolutely fine except one
    Filling The datas in tableView using model
    a82f8a8d-2823-46d2-ab6e-5f07416ac525-image.png
    I am getting the data but it is empty.
    And fetching in text Edit works so fine.
    Using the same queries everything is working but filling doesnot work , See in the picture even the query is working and detecting that there are two datas but they aren't filled
    WHY

    What can I do to solve this?
    Plz I would be very thankfull



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

    Filling The datas in tableView using model

    Can you show your code?



  • Just to cover the bases, did you create a QSqlTableModel with the SQL database, then pass it into your TableView? Did you set a table?

    Here's how I have mine setup:

    _database = loadDatabase("postgres");  //There is more code in this function, but I assume yours is correct.
    _tableModel = new QSqlTableModel(this,_database);
    _tableModel->setTable(tableName);
    _tableModel->setEditStrategy(QSqlTableModel::OnFieldChange);
    _tableModel->select();
    ui->tableView->setModel(_tableModel);
    

    I hope this helps.



  • @VRonin

     QString command = "select * from countryDetails;";
        {
            QSqlDatabase db =  QSqlDatabase::addDatabase("QMYSQL");
            db.setDatabaseName(databaseName);
    db.setPort(databasePort);
    db.setHostName(databaseHostname);
    db.setUserName(databaseUsername);
    db.setPassword(databasePassword);
            db.open();
            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,data.ERRORS.DATABASE_SELECT_NOT_WORKING_TITLE("CF"),data.ERRORS.DATABASE_SELECT_NOT_WORKING_BODY("CF")+query->lastError().text());
                }
    
            }else{
                QMessageBox::warning(this,data.ERRORS.DATABASE_NOT_CONNECTED_TITLE("CF"),data.ERRORS.DATABASE_NOT_CONNECTED_BODY("CF")+db.lastError().text());
            }
    
        }
        QSqlDatabase::removeDatabase("qt_sql_default_connection"); // removing database
    

    If I put the message box inside the query, then while the messagebox is open then the table is filled , as soon as I close the messagebox then data vanish but the rows with empty data remains there WHY?

     QString command = "select * from countryDetails;";
        {
            QSqlDatabase db =  QSqlDatabase::addDatabase("QMYSQL");
            db.setDatabaseName(databaseName);
    db.setPort(databasePort);
    db.setHostName(databaseHostname);
    db.setUserName(databaseUsername);
    db.setPassword(databasePassword);
            db.open();
            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);
    QMessageBox::information(this,"Now it is seen", "seen now");
    //setting the model 
                }else{
                    QMessageBox::warning(this,data.ERRORS.DATABASE_SELECT_NOT_WORKING_TITLE("CF"),data.ERRORS.DATABASE_SELECT_NOT_WORKING_BODY("CF")+query->lastError().text());
                }
    
            }else{
                QMessageBox::warning(this,data.ERRORS.DATABASE_NOT_CONNECTED_TITLE("CF"),data.ERRORS.DATABASE_NOT_CONNECTED_BODY("CF")+db.lastError().text());
            }
    
        }
        QSqlDatabase::removeDatabase("qt_sql_default_connection"); // removing database
    


  • Do not remove the database. That’s the problem


  • Lifetime Qt Champion

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



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



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



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


  • Lifetime Qt Champion

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

    How can I solve it?

    Did you read @VRonin 's message?



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



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



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



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


  • Lifetime Qt Champion

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



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


  • Lifetime Qt Champion

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



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


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



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



  • 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");
    }
    

    bed72770-61b8-47f8-b13a-4e0a7dd44ca2-image.png

    After clicking OK,
    1898caf6-1200-40ad-948d-07096e55b0fe-image.png
    The data vanishes again
    @jsulm @JonB can you see this?



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



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



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



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

  • Lifetime Qt Champion

    @Thank-You And we told you at least two times that you must not remove the database connection while there is an open query for your QSqlTableModel!



  • @Thank-You
    This is exactly the code as it was. It has removeDatabase() immediately after the message box show. Which we have told you multiple times is wrong. It does not have the

    QMessageBox::information(this,"NOw the database is shown", " This is also shown");
    

    you later claimed to have put in, nor the output you showed in your last screenshot. Which doesn't correspond to that either.

    What else is there to say? You don't listen, you don't adjust your code, you show screenshots which don't pertain to the code.

    I think we are all done. We don't know what answer you are prepared to accept. I suggest you keep your code exactly as you have it, since that is the only thing you are prepared to accept. Bye.



  • @Christian-Ehrlicher And when to remove it
    Or leave it without removing ?
    I said that it will give me errors,
    QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
    I don't want this application to have this error?

    OR
    How can I get the signal when query does not require the database.

    @JonB Please refer to my last code. It is exactly the same, Nothing different. May be I gave you the example of another table fill.
    Just see the last one and say exactly what is the problem just for the last time.


  • Lifetime Qt Champion

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

    I said that it will give me errors,

    And I said you to not to add the database again and again, do it just once. I really don't know why it is necessary to repeat same things again and again...
    Remove the database when it is not needed anymore (for example in the destructor of your class).



  • @jsulm OOOOw
    Maybe it can help ,
    I will try and say the result.



  • Maybe an analogy would help with the advice that everybody has given. Pretend like running a query on a database is like teaching a dog a new trick. You want the dog to be alive when you teach the trick and still be alive to enjoy watching the trick.

    What people are saying is that in your fill table function, you are creating a dog, teaching a trick, killing the dog, and wondering how come it doesn't perform the trick.

    In conclusion, you need your dog to live as long as the lifetime that you want the user to see your table. Did this help?


Log in to reply