我正在尝试从SQLite
数据库表中获取数据,但是我不能获得超过50行。是否限制为50行?我的代码如下所示:
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;
你的代码中存在很多错误和误解。为简化起见,我将修复你的代码以使其工作。自己研究差异。
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;
query.FetchOptions.Mode := fmAll;
解决了我的问题。旁注:将
query.FieldByName('....')
一次放置在循环外将有助于提高性能。@ArnaudBouchez你能说为什么吗?而在循环之外,则表示在循环开始之前。
@NasreddineGalfout调用
FieldByName()
会进行查找,这会降低性能。只需在循环之前进行一次,分配给局部TField
变量,然后使用它们访问循环内的字段值。有关更多详细信息,请访问delphitools.info/2010/11/30/…自那时以来,Delphi RTL使用TDictionary <>,但是它仍然很昂贵,并且lowercase()
每次调用都会进行一次字符串分配和某种转换,因此也有成本。我想知道@Arnaud Bouchez与循环之前第一次
query.first
调用是否相同query.FieldByName('..')
?