QTableView does not show data when populating with QSqlQueryModel / stored procedure



  • I have this frustrating problem that I just can't seem to crack. I use a QSqlQueryModel to populate a QTableView. When I pass an ordinary SELECT query to the model, the tableView displays just fine. But when I pass a stored procedure to the model, only the table headers show up, but no data in the body of the table. However, when I iterate through the records of the model (using a combination of query->next() and model->record()), the results print to qDebug successfully. But as soon as I am done with while loop and try to print the records in the model again, it just prints a bunch of blanks. From what I have read here, it seems to have something to do with isForwardOnly, but I cannot quite figure out what. It looks like when the while loop is finished the model "looses" the data. Btw, the stored procedure executes correctly in SSMS. I thought maybe the model persists longer than the query, and therefore the query is closed before the model is loaded with all the data. But I have created the query and the model on the heap, and made sure the db connection remains open. How do I get the results of the stored procedure to display correctly in the tableView?

    Here's the offending code:

    void MainWindow::on_btnLoadTable_clicked()
    {
        conn = new Connection("LOCALHOST\\SQLEXPRESS", "Plants");
        model1 = new QSqlQueryModel(this);
        conn->init(); //Set driver and connection options
        conn->open();
        qry1 = new QSqlQuery(conn->db);
    
        //qry1->prepare("SELECT * FROM Batches_friendly_view"); //When using this query the tableView populates correctly, but the while loop produces no results
        //if (qry1->exec()) //Use this line when running the select query
    
        if (qry1->exec("spBatchesPerPlant"))
        {
            qDebug() << "Executing query...";
            model1->setQuery(*qry1);
            ui->tableView->setModel(model1);
            int row = 0; //used for counting rows in the query
            qDebug() << "isActive: " << qry1->isActive();
            qDebug() << "isValid: " << qry1->isValid();
            qDebug() << "isSelect: " << qry1->isSelect();
            qDebug() << "isForwardOnly: " << qry1->isForwardOnly();
            while (qry1->next()) //Loop through the results
            {
                //Print out rows from query
                qDebug() << "Batch count: " << qry1->value(0).toString()
                         << "Plant#: " <<qry1->value(1).toString()
                         << "Common name: " <<qry1->value(2).toString();
    
                //Print out records from model
                //This gives the same result as printing from the query
                qDebug() << (model1->record(row)).value(0).toString()
                         << (model1->record(row)).value(1).toString()
                         << (model1->record(row)).value(2).toString();
                qDebug() << endl;
                row++;
            }
            qDebug() <<"\nCheck if model contains any records";
    
            //The following code returns empty results - Why?
            for (int i = 0; i < row; i ++)
            {
                qDebug() << (model1->record(i)).value(0).toString()
                         << (model1->record(i)).value(1).toString()
                         << (model1->record(i)).value(2).toString();
            }
        }
    }
    

    When passing the stored procedure, the qDebug output looks like this:
    Connection constructor called
    Connection opened successfully!
    Executing query...
    isActive: true
    isValid: false
    isSelect: true
    isForwardOnly: true
    Batch count: "4" Plant#: "1" Common name: "Camel thorn"
    "4" "1" "Camel thorn"

    Batch count: "1" Plant#: "2" Common name: "Red apple"
    "1" "2" "Red apple"

    Batch count: "1" Plant#: "3" Common name: "Red pear"
    "1" "3" "Red pear"

    Check if model contains any records
    "" "" ""
    "" "" ""
    "" "" ""


  • Lifetime Qt Champion

    Hi,

    This thread might give you some clues.



  • So I tried the suggestions by @jwernerny. I dumped the QSqlQueryModel and went with a QStandardItemModel. TableView now displays correctly. Code now looks like this:

    void MainWindow::on_btnLoadTable_clicked()
    {
        conn = new Connection("LOCALHOST\\SQLEXPRESS", "Plants");
        stdModel = new QStandardItemModel(this);
        conn->init(); //Set driver and connection options
        conn->open();
        qry1 = new QSqlQuery(conn->db);
        const int COL_COUNT = 3;
        if (qry1->exec("spBatchesPerPlant"))
        {
            qDebug() << "Executing query...";
            int row = 0; //used for counting rows in the query
            while (qry1->next()) //Loop through the results
            {
                for (int col = 0; col < COL_COUNT; col++)
                {
                    stdItem = new QStandardItem(qry1->value(col).toString());
                    stdModel->setItem(row, col, stdItem);
                }
                row++;
            }
            ui->tableView->setModel(stdModel);
        }
    }
    

    Motto of the story: stored procedures generate forward only queries. QSqlQueryModel does not work for forward only queries, so use a standard item (or custom) model.



Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.