Qt 5.14.2 QSqlQuery does not read all rows into QTableView [SOLVED]

ad5xj

I know I have seen this somewhere before but I simply cannot find it now. So I will ask a newbie question.

I have created a SQL Query and made it a QSQLMode for a QTableView. It has a large result set that is displayed in the QTableView correctly.

However, I have discovered it only displays about 300 rows or so until scrolling down to the last record displayed. Then about that many more are added but do not follow the formatting of the previous rows as far as formatting like row height. More scrolling produces more rows, etc until all the query rows are retrieved.

My question is how to get all rows in the query and have them all shown in the QTableView without having to scroll to the last displayed row to load more.

The use of the QSqlModel speeds the load and view of large data queries far faster than other methods and except for this problem have a superior performance over any other method I have used this far.

Does anyone remember how this is done? Or can someone advise what to do?

Sorry for such a newbie question.

SGaist

Hi,

You are likely looking for canFetchMore and its friends.

JonB

@ad5xj
As @SGaist has just said. The "about 300 rows" is because internal Qt code implementation fetches 256 rows at a time from SQL database. You want to call fetchMore() while canFetchMore() returns true to fetch them all in one go. Initial delay in return for smoother performance.

ad5xj

It seems from the documentation that this method works only if the database reports the correct query size (rows). In the case of SQLite 3 it does not.

JonB

@ad5xj
Have you actually tried it nonetheless? The example at https://doc.qt.io/qt-5/qsqlquerymodel.html#fetchMore implies to me that it should work. For example, https://stackoverflow.com/questions/26369420/when-or-how-to-use-fetchmore-on-qsqltablemodel-with-a-sqlite-database-for-rowc is using that on SQLite. So far as I know, that is what QTableView will be using, and it corresponds to the new rows you see being added.

Bonnie

@ad5xj said in Qt 5.14.2 QSqlQuery does not read all rows into QTableView:

It seems from the documentation that this method works only if the database reports the correct query size (rows). In the case of SQLite 3 it does not.

No, the doc says that it only affects databases that don't report back the size of a query

ad5xj

@Bonnie

No, the doc says that it only affects databases that don't report back the size of a query
``
Not understanding what you are saying here. 

I said in the quote you included that SQLIte 3 DOES NOT report query size accurately. You can move to the last record and the size will update. But it only applies the record count to the segment that has been loaded (e.g. 256 records), not the entire query result. 

I am attempting to get all the records in the query result not just the first few. The query my retrieve several thousand records.

Bonnie

@ad5xj
I mean the documentation of the method "canFetchMore" and "fetchMore" says that the two methods only have affect in databases that don't has the feature of QSqlDriver::QuerySize. So they work with SQLite 3.

JonB

@ad5xj
I asked before:

Have you actually tried it [the canFetchMore()-fetchMore() loop] nonetheless?

?

ad5xj

Thanks JonB for the link to the solution.

I changed my code to be like the link and that did it. Problem solved.