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

donnpie

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

donnpie

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.

SGaist

Hi,

This thread might give you some clues.

donnpie

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.