How to handle more records from sqlite database using c++ ?

Qt embedded developer

I am using sqlite3 in Embedded device. this Device is used for Bus ticketing. In this device i have to show report that shows all ticket detail. i am fetching all ticket detail from sqlite database. But It takes too much run time memory which help to execute process while fetching the records. And so that device getting crash.

So i want to know is there better way to fetch many records for my Inspection report with out getting crash ?

i am using below queries :

query 1] in table 1

select INTTXNTYPE,substr(STRPASSREFERENCENO,7),DTEUSEDON,(select STRPASSTYPENAME from TBLPASSFREQUENCYCONFIGURATIONMASTER PFC where PFC.INTPASSTYPEID = PQV.INTPASSTYPE AND PFC.BACTIVE=1 AND PFC.BDELETED=0 limit 1 ) as PassDuration,(select strStageCode from TBLROUTESTATIONMASTER RSM where RSM.INTROUTEID = PQV.INTROUTEID and RSM.INTSTATIONID = PQV.INTSCANSTATIONID AND RSM.bActive=1 AND RSM.bDeleted=0 ),(select strStageCode from TBLROUTESTATIONMASTER RSM where RSM.INTROUTEID = PQV.INTROUTEID and RSM.INTSTATIONID = PQV.INTTOSTATIONID AND RSM.bActive=1 AND RSM.bDeleted=0 ) from TBLPASSQRCODEVALIDATE PQV where STRWAYBILLNO ='W0311214002' AND INTTRIPID='3' AND BVALID = 1 group by STRPASSREFERENCENO order by DTEUSEDON asc;

query 2] in table 2 select intTicketTypeID,substr(strTicketNo,7),dteTicketDateTime,TR.intFromStationID,TR.intToStationID,(select strStageCode from TBLROUTESTATIONMASTER RSM where RSM.intRouteID = TR.intRouteID and RSM.intStationID = TR.intFromStationID AND RSM.bActive=1 AND RSM.bDeleted=0 ) as FromStaionStageCode,(select strStageCode from TBLROUTESTATIONMASTER RSM where RSM.intRouteID = TR.intRouteID and RSM.intStationID = TR.intToStationID AND RSM.bActive=1 AND RSM.bDeleted=0 ) as ToStaionStageCode,sum(intFullTickets) as AdultQty ,sum(intHalfTickets) as HalfQty ,sum(intTotalTicketAmount/100) as TotalTicketAmount from tblTicketTransactions TR where strWayBillNo ='W0311214002' AND BSUCCESSTXNFORCARD = 0 AND intTripID='3' group by strTicketNo order by dteTicketDateTime asc;

jsulm

@Qt-embedded-developer You can limit number of records returned by the DB, see https://stackoverflow.com/questions/8439645/how-can-i-get-a-specific-chunk-of-results
So, only show what currently is visible. Qt MVC implementation should actually already fo that. How exactly do you fetch and show the data?

Qt embedded developer

@jsulm i am using sqlite3_exec to execute query, i am showing this data on Physical paper.

jsulm

@Qt-embedded-developer said in How to handle more records from sqlite database using c++ ?:

Physical paper

Don't know what this is.

You should rather use https://doc.qt.io/qt-5/model-view-programming.html instead of hacking around with sqlite3_exec...

Qt embedded developer

@jsulm i am printing this data on paper. i need help for optimize this query so that my device not show out of memory error.

artwaw

@Qt-embedded-developer then you should ask on SQLite forum.
What @jsulm is suggesting though is, I think, that you should try to use model/view approach and then see. You can get the data from the model without much of an effort.

Kent-Dorfman

yeah. this is more of a question about SQL...and the hint is something called a CURSOR.

KroMignon

@Qt-embedded-developer said in How to handle more records from sqlite database using c++ ?:

So i want to know is there better way to fetch many records for my Inspection report with out getting crash ?

You are using SQLite, which a "lite" DB manager, but your SQL statement is very complex. It uses many subqueries and GROUP BY, which means that the SQL engine have to store locally the intermediary results before it can give a result.

I guess there is your problem. You have to rethink the way you are using your database or use another engine.
By the way, your SQL request looks very strange to me (why 2 subqueries on TBLROUTESTATIONMASTER tables??), but I am not an SQL expert:

select INTTXNTYPE,substr(STRPASSREFERENCENO,7),DTEUSEDON,
(select STRPASSTYPENAME from TBLPASSFREQUENCYCONFIGURATIONMASTER PFC where PFC.INTPASSTYPEID = PQV.INTPASSTYPE AND PFC.BACTIVE=1 AND PFC.BDELETED=0 limit 1 ) as PassDuration,
(select strStageCode from TBLROUTESTATIONMASTER RSM where RSM.INTROUTEID = PQV.INTROUTEID and RSM.INTSTATIONID = PQV.INTSCANSTATIONID AND RSM.bActive=1 AND RSM.bDeleted=0 ),
(select strStageCode from TBLROUTESTATIONMASTER RSM where RSM.INTROUTEID = PQV.INTROUTEID and RSM.INTSTATIONID = PQV.INTTOSTATIONID AND RSM.bActive=1 AND RSM.bDeleted=0 )

from TBLPASSQRCODEVALIDATE PQV where STRWAYBILLNO ='W0311214002' AND INTTRIPID='3' AND BVALID = 1 group by STRPASSREFERENCENO order by DTEUSEDON asc;

Qt embedded developer

@KroMignon to get the result i have used 2 subqueries for selecting to station id and from station id .

KroMignon

@Qt-embedded-developer said in How to handle more records from sqlite database using c++ ?:

to get the result i have used 2 subqueries for selecting to station id and from station id .

My concerns with this kind of SQL request is that it may requires a big amount of memory on SQLite side, depending on tables size.
As I wrote before, I am not an SQL expert, but this don't looks for me as the best way to achieve what you want, I would you a "LEFT JOIN" and not this 2 subqueries on same table with almost same where statement.