Recent

Author Topic: [SOLVED] TSQLQuery SQLite EOF issues  (Read 301 times)

yoctobit

  • Newbie
  • Posts: 3
[SOLVED] TSQLQuery SQLite EOF issues
« on: August 31, 2020, 11:07:13 am »
Hello all, eventually had the need to sign up for the forum to ask questions so as a short introduction: i've been programming Object Pascal since Delphi1 and the last decade or so using FPC/Lazarus. I'm not the greatest programmer, it's also a hobby and at work it's part of what i do, we make electronic measurement equipment.

I've ran into an issue with TSQLQuery connected to a SQLite database. I use the typical
Code: Pascal  [Select][+][-]
  1. while not (Q.EOF) do begin ... ; Q.Next; end;
construction.

It appeared on my dataset this only works correcly if i add a LIMIT to the query, f.e.:
Code: Pascal  [Select][+][-]
  1. 'SELECT stamp,%s FROM `%s` WHERE TRUE %s ORDER BY stamp ASC LIMIT 15000'

However, if i remove the limit, or make the number bigger - it's likely the same issue - the query partly fails. It will return the first thousand or so records, then suddenly report EOF. If i limit to 15000 records, i get the complete data set.

I do a First and a Last already, as suggested in https://forum.lazarus.freepascal.org/index.php/topic,48738.0.html , since i need the timestamps to scale stuff into a graph. So going by the docs and other topics the complete dataset should already be buffered. It obviously is not or better said: it lost data. After doing .last the last record actually contains valid data, but i cannot loop the whole data set for some reason.

What am i overlooking? Or did i bump into so bug, like memory limitations? Records are not insanely large, just numeric data. Any workaround?
« Last Edit: August 31, 2020, 02:48:11 pm by yoctobit »

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: TSQLQuery SQLite EOF issues
« Reply #1 on: August 31, 2020, 11:15:05 am »
It appeared on my dataset this only works correcly if i add a LIMIT to the query, f.e.:
Code: Pascal  [Select][+][-]
  1. 'SELECT stamp,%s FROM `%s` WHERE TRUE %s ORDER BY stamp ASC LIMIT 15000'
TRUE %s ???

What do you provide as the three %s'es?

Maybe you can show the exact loop-code. (exact code, not just snippets)

yoctobit

  • Newbie
  • Posts: 3
Re: TSQLQuery SQLite EOF issues
« Reply #2 on: August 31, 2020, 11:22:45 am »
I'm sorry, the statement gets formatted like
Code: Pascal  [Select][+][-]
  1. Format ('SELECT stamp,%s FROM `%s` WHERE TRUE %s ORDER BY stamp ASC', [..])

The actual query looks like
Code: Pascal  [Select][+][-]
  1. SELECT stamp,* FROM `1C12` WHERE TRUE AND stamp>0 ORDER BY stamp ASC

And the loop+query init code:
Code: Pascal  [Select][+][-]
  1.   Q.SQL.Text:=filteredQuery;
  2.  
  3.   Q.Open;
  4.   Q.First;
  5.   pstart := Q.FieldByName('unixstamp').AsLargeInt;
  6.   Q.Last;
  7.   pend := Q.FieldByName('unixstamp').AsLargeInt;
  8.   Q.First;
  9.  
  10.   //set the desired range
  11.   SetPlotRange (pstart, pend);
  12.  
  13.   //clear our existing graph
  14.   ClearPlot;
  15.  
  16.   while not Q.EOF do begin
  17.     for i:=1 to Q.FieldCount-1 do begin
  18.       if Q.Fields[i].IsNull then continue;
  19.       Plot (Q.FieldDefs.Items[i].Name, Q.Fields[i].AsFloat, Q.FieldByName('unixstamp').AsLargeInt);
  20.     end;
  21.     Q.Next;
  22.   end;    
  23.  
Where Q is type TSQLQuery.
« Last Edit: September 01, 2020, 09:30:23 am by yoctobit »

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: TSQLQuery SQLite EOF issues
« Reply #3 on: August 31, 2020, 12:04:05 pm »
And the loop+query init code:
From your code I see no reason why a "LIMIT 15000" would give you the complete set and without the limit you don't get everything.

yoctobit

  • Newbie
  • Posts: 3
Re: TSQLQuery SQLite EOF issues
« Reply #4 on: August 31, 2020, 02:34:35 pm »
I'm sorry to have opened the topic  - it was bothering me since last week and i couldn't pinpoint the issue. The problem is not with fetching the data. Listed code is working properly. The error was outside the code (and i did not find it yet) but had mislead me. After double-checking i can tell that all records are fetched just fine...

Can i delete the topic as it's no longer relevant to others?

--in case anyone wonders: at the end of the dataset was a record with a datestamp that had suddenly jumped a few year because someone who's name shall not be mentioned adjusted the HW clock during equipment testing. While visualizing, by jumping several years without data it appeared as the dataset was only 10% filled. You can all laugh at me now :)

« Last Edit: August 31, 2020, 02:47:48 pm by yoctobit »

rvk

  • Hero Member
  • *****
  • Posts: 4327
Re: TSQLQuery SQLite EOF issues
« Reply #5 on: August 31, 2020, 02:45:13 pm »
The error was outside the code (and i did not find it yet) but had mislead me.
Isn't it always  :D

Can i delete the topic as it's no longer relevant to others?
Not sure if you can delete topics. You could edit the starting post and put [SOLVED] in front of the subject.

 

TinyPortal © 2005-2018