Firdbird & SQL
lazarus / 16-Databases

----------------------------------------------------------------------
asdf · 2010-11-18 02:48

cn.DatabaseName:= filenameedit1.Text;
cn.UserName:= 'sysdba';
cn.Password:= 'masterkey';
cn.open;
sq.SQL.Clear;
sq.SQL.Text:= 'select first 100 * from engthagen';
sq.Open;
memo1.append(inttostr(sq.RecordCount)+' record(s) found.');
memo1.append('--------------------------------------');
i:=0;
StringGrid1.ColCount:=sq.FieldCount;
for i:=0 to sq.FieldCount-1 do
begin
StringGrid1.Cells[i, 0] := sq.Fields.FieldName ;
end;

StringGrid1.RowCount :=sq.RecordCount+1 ;
for j:=1 to sq.RecordCount do
begin
for i:=0 to sq.FieldCount-1 do
StringGrid1.Cells[i, j] := sq.Fields.Text ;
end;
stringgrid1.AutoSizeColumns;

sq.Close;
cn.Close;                                         

Why did 'sq' get repeated 10 times of record number 1?But Turbobird
found all perfect.

----------------------------------------------------------------------
jhvhs · 2010-11-18 03:30

Hi, asdf.

It happens, because you have to move the record pointer.
Code: [Select]StringGrid1.RowCount :=sq.RecordCount+1 ;
for j:=1 to sq.RecordCount do
begin
for i:=0 to sq.FieldCount-1 do
StringGrid1.Cells[i, j] := sq.Fields[i].Text ;
sq.Next;
end;
stringgrid1.AutoSizeColumns;Hope this helps

----------------------------------------------------------------------
asdf · 2010-11-18 06:40

QuoteStringGrid1.RowCount :=sq.RecordCount+1 ;
If so, why did it count 10 records instead of 1 record ?
Once we have to move pointer one by one, we maybe can't know record
number at first time ?

----------------------------------------------------------------------
Zaher · 2010-11-18 07:10

First in FirebirdSQL do not call RecordCount at all, it is not support
it perfectly, and in fact that correct because SQL engine can not know
the count of rows before fetch them all.
so do it by use "not EOF" and Next
Code: [Select]j :=0;
while not sq.EOF do
begin
  StringGrid1.RowCount :=j+1 ;
  begin
    for i:=0 to sq.FieldCount-1 do
       StringGrid1.Cells[i, j] := sq.Fields.Text;
  end;
  sq.Next;
end;

----------------------------------------------------------------------
asdf · 2010-11-18 07:14

Quotesq.SQL.Text:= 'select first 100 * from engthagen';
But why did it show (like it had found) 10 records?

See the attached picture please...

----------------------------------------------------------------------
Zaher · 2010-11-18 07:22

I remember in old Firebird it give you wrong number.

----------------------------------------------------------------------
José Mejuto · 2010-11-22 09:18

Quote from: asdf on November 18, 2010, 08:14:29 amQuotesq.SQL.Text:=
'select first 100 * from engthagen';But why did it show (like it had
found) 10 records?
See the attached picture please...

Hello,

SQL result records are fecthed in batch packets, this means that the
control ask for an amount of records and when you move to the last
fetched and it is not the last a new batch is requested. This allows
memory to not be consumed by controls with large amount of records
that only the very first records are used, and also make the controls
much more responsive. Imagine a fetch of 100000 records to a DBGrid
over TCP/IP, it will take several seconds to be completed (data
transfer).

In your firebird query change the packets property to the desired max
amount of records fetched at once.

Or just do:

Query.Open;
Query.Last;
Query.First;

This forces the fetch of all records.

----------------------------------------------------------------------
Zaher · 2010-11-22 10:12

Quote from: JoshyFun on November 22, 2010, 10:18:47 amQuote from: asdf
on November 18, 2010, 08:14:29 amQuotesq.SQL.Text:= 'select first 100
* from engthagen';But why did it show (like it had found) 10 records?
See the attached picture please...

Hello,

SQL result records are fecthed in batch packets, this means that the
control ask for an amount of records and when you move to the last
fetched and it is not the last a new batch is requested. This allows
memory to not be consumed by controls with large amount of records
that only the very first records are used, and also make the controls
much more responsive. Imagine a fetch of 100000 records to a DBGrid
over TCP/IP, it will take several seconds to be completed (data
transfer).

That is true, but in PostgreSQL it fetch all the records to the memory
before using it, not like as FirebirdSQL, so you can get the records
count in PostgreSQL.