Recent

Author Topic: Query Sqlite3 Question  (Read 835 times)

nugax

  • Full Member
  • ***
  • Posts: 232
Query Sqlite3 Question
« on: April 19, 2022, 05:41:59 am »
So since Query.Recordcount will only return 10 records as a count at a time, how can I determine the actual amount of records within a sqlite3 database? is there some other var?
-Nugax

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: Query Sqlite3 Question
« Reply #1 on: April 19, 2022, 06:25:36 am »
You can execute an sql similar to you own with COUNT(1) instead of your fields.
Do like SELECT COUNT(*) AS number_of_records FROM orders;

You can also do a SqlQuery1.Last and SqlQuery1.First after opening your SqlQuery1. After that you .RecordCount will contain the actual correct recordcount. This will give you a slight performance hit depening on the number of records.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Query Sqlite3 Question
« Reply #2 on: April 19, 2022, 09:05:10 am »
Property PackedRecord is always 10. Set it to 0 and al records will be fetched.

Offcourse the option from rvk wil work.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: Query Sqlite3 Question
« Reply #3 on: April 19, 2022, 09:10:50 am »
Addition to rvk:
If you set PacketRecords to -1 you load all records (and don't need to move to Last/First).
The penalty on performance is still there.
So i agree with RVK: Fire a SELECT COUNT is probably the best way
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

af0815

  • Hero Member
  • *****
  • Posts: 1284
Re: Query Sqlite3 Question
« Reply #4 on: April 19, 2022, 09:32:47 am »
Why do you need the Query.RecordCount ? This is the basic question.
regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: Query Sqlite3 Question
« Reply #5 on: April 19, 2022, 09:44:30 am »
Why do you need the Query.RecordCount ? This is the basic question.
At a guess:
For the "classic"
Code: Pascal  [Select][+][-]
  1. For i:=0 To RS.RcordCount-1 Do Something;
  2.  
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

af0815

  • Hero Member
  • *****
  • Posts: 1284
Re: Query Sqlite3 Question
« Reply #6 on: April 19, 2022, 10:01:53 am »
@zvoni: It you want to this, you better use a 'classical' collection :-) To transfer the data from the RS to a classical construct to deal with.

Working with a Serverbased DB is working with unknown amount of data. And a count of a DB is only valid for the time of asking. With a local sqlite it will be ok, but in a multiuser enviroment the answer can be false in the next moment, because the data in the table can be changed in the meanwhile.

@nugax: If you are using the DB singleuser/process you can do a firts/last and read the the data as zvoni mententions (IMHO the data will be buffered an if you itertate it is also fetched from internal caches - test it - depending on you data you will see nearly no delay - at sqilte local), but the best way is, to think about the design to avoid this fixed constructs.
« Last Edit: April 19, 2022, 10:03:55 am by af0815 »
regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: Query Sqlite3 Question
« Reply #7 on: April 19, 2022, 10:51:41 am »
*snip* And a count of a DB is only valid for the time of asking. With a local sqlite it will be ok, but in a multiuser enviroment the answer can be false in the next moment, because the data in the table can be changed in the meanwhile.
Correct. Except if you lock the table.
Yes, yes, i can see it.....
The user requests the Data, the table gets locked, and suddenly the user has to run to the restroom to dump a log, because he didn't do it before breakfast....
..... i can see the faces of his co-workers....
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: Query Sqlite3 Question
« Reply #8 on: April 19, 2022, 11:45:14 am »
Working with a Serverbased DB is working with unknown amount of data. And a count of a DB is only valid for the time of asking. With a local sqlite it will be ok, but in a multiuser enviroment the answer can be false in the next moment, because the data in the table can be changed in the meanwhile.
Uh, no, not exactly. The amount of data won't change in a multiuser environment. Proper databases work with transactions and you won't see any changes/additions during your own transaction. So using Last/First and then iterating though the dataset with RecordCount should be fine. But I agree, it's not the way to do it. You should use "while not EOF" for that.

 

TinyPortal © 2005-2018