Warm tip: This article is reproduced from serverfault.com, please click

SQLite query results limitation

发布于 2020-11-28 15:08:19

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;
Questioner
DeFacto
Viewed
0
Branko 2020-11-29 00:34:32

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;