SQLite query results limitation

Question

I am trying to get data from SQLite database table, but i cannot get more than 50 rows. Is there a limitation of 50 rows? My code looks like that:

unit Unit1;

interface

uses
FireDAC.Stan.Def, FireDAC.DApt, FireDAC.Phys.SQLite, FireDAC.VCLUI.Wait, FireDAC.Comp.Client, FireDAC.Stan.Async;

type
  TRaportas = record
    Pradzia: TDateTime;
    Pabaiga: TDateTime;
    Trukme: Integer;
    idPriezastis: Integer;
    Priezastis: string;
    idVieta: Integer;
    Vieta: string;
    Komentaras: string;
  end;

procedure TForm1.btnRaportasClick(Sender: TObject);
var
sqlConn: TFDConnection;
query: TFDQuery;
prastovuRec: array of TRaportas;
i: Integer;
begin
  dbVieta := edt2.Text;
  sqlConn := TFDConnection.Create(nil);
  //sqlConn.Connected := False;
  sqlConn.DriverName := 'SQLITE';
  sqlConn.Params.Values['DataBase'] := dbVieta;  
  query := TFDQuery.Create(nil);
  query.Connection := sqlConn;
  query.SQL.Text := 'SELECT * FROM Prastovos WHERE ID >= :_ID';
  query.ParamByName('_ID').Value := StrToIntDef(edt3.Text, 656);
  sqlConn.Open();
  query.Open();
  SetLength(prastovuRec, query.RowsAffected);
  edt4.Text := IntToStr(query.RowsAffected);
  for i := 0 to query.RowsAffected - 1 do
    begin
      with mRaportas do
        begin
          Pradzia := query.FieldByName('Pradzia').AsDateTime;
          Pabaiga := query.FieldByName('Pabaiga').AsDateTime;
          Trukme := query.FieldByName('Trukme').AsInteger;
          idPriezastis := query.FieldByName('IDpriezastis').AsInteger;
          Priezastis := query.FieldByName('Priezastis').AsString;
          idVieta := query.FieldByName('IDvieta').AsInteger;
          Vieta := query.FieldByName('Vieta').AsString;
          Komentaras := query.FieldByName('Komentaras').AsString;
        end;
      prastovuRec[i] := mRaportas;
      query.Next;
    end;
  query.Close;
  query.DisposeOf;
  sqlConn.Close;
  sqlConn.Free;
end;

Branko · Accepted Answer · 2020-11-28T16:34:32.460

There is a lot of mistakes and misunderstandings inyour code. To simplify, I'm just going to fix your code to make it work. Study the differences yourself.

unit Unit1;

interface

uses
  FireDAC.Stan.Def, FireDAC.DApt, FireDAC.Phys.SQLite, FireDAC.VCLUI.Wait, FireDAC.Comp.Client, 
  FireDAC.Stan.Async, FireDAC.Stan.Option;

type
  TRaportas = record
    Pradzia: TDateTime;
    Pabaiga: TDateTime;
    Trukme: Integer;
    idPriezastis: Integer;
    Priezastis: string;
    idVieta: Integer;
    Vieta: string;
    Komentaras: string;
  end;

var
  prastovuRec: array of TRaportas;

procedure TForm1.Button7Click(Sender: TObject);
var
  sqlConn: TFDConnection;
  query: TFDQuery;
  mRaportas: TRaportas;
  i: Integer;
begin
  sqlConn := TFDConnection.Create(nil);
  query := TFDQuery.Create(nil);
  try
    sqlConn.DriverName := 'SQLITE';
    sqlConn.Params.Values['DataBase'] := edt2.Text;

    query.Connection := sqlConn;
    query.FetchOptions.Mode := fmAll;   // essential if you want to use RecordCount
    query.SQL.Text := 'SELECT * FROM Prastovos WHERE ID >= :_ID';
    query.ParamByName('_ID').Value := StrToIntDef(edt3.Text, 656);
    query.Open();

    edt4.Text := IntToStr(query.RecordCount);
    SetLength(prastovuRec, query.RecordCount);
    i := 0;

    while not query.Eof do
    begin
      mRaportas := Default(TRaportas);  // not necessary if you assign all record fields
      mRaportas.Pradzia := query.FieldByName('Pradzia').AsDateTime;
      mRaportas.Pabaiga := query.FieldByName('Pabaiga').AsDateTime;
      mRaportas.Trukme := query.FieldByName('Trukme').AsInteger;
      mRaportas.idPriezastis := query.FieldByName('IDpriezastis').AsInteger;
      mRaportas.Priezastis := query.FieldByName('Priezastis').AsString;
      mRaportas.idVieta := query.FieldByName('IDvieta').AsInteger;
      mRaportas.Vieta := query.FieldByName('Vieta').AsString;
      mRaportas.Komentaras := query.FieldByName('Komentaras').AsString;
      prastovuRec[i] := mRaportas;
      Inc(i);

      query.Next;
    end;
    query.Close;
  finally
    query.Free;
    sqlConn.Free;
  end;
end;

Side note: Putting `query.FieldByName('....')` once outside the loop would help the performance. — Arnaud Bouchez, Nov 28 '20 at 21:18
@ArnaudBouchez Can you say why? and outside the loop you mean before the loop starts. — Nasreddine Galfout, Nov 29 '20 at 11:39
@NasreddineGalfout Calling `FieldByName()` makes a lookup which has a performance cost. Just make it once before the loop, assign to a local `TField` variable, then use them to access the field values inside the loop. For more details, check https://www.delphitools.info/2010/11/30/fieldbyname-or-why-a-profiler-is-your-friend The Delphi RTL uses a TDictionary<> since that time, but it has still a cost, and it calls `lowercase()` each time, which makes a string allocation and some conversion, so has also a cost. — Arnaud Bouchez, Nov 29 '20 at 11:50
@Arnaud Bouchez i am wondering is `query.first` is same as call `query.FieldByName('..')` first time before the loop? — DeFacto, Dec 09 '20 at 12:13

score 1 · Answer 2 · answered Nov 28 '20 at 15:47

1

No, there is no special limit.

But you have a WHERE clause in your query. It is likely that which limits the result set. Check the value you use.

answered Nov 28 '20 at 15:47

fpiette

11,983
1
24
46

strange, but `query.RowsAffected` returns maximum 50. If i do change it to `while not query.Eof do` then i do get all the records. – DeFacto Nov 28 '20 at 16:02

SQLite query results limitation

2 Answers2