[SOLVED] Grid To display SQL query result
lazarus / 16-Databases

----------------------------------------------------------------------
heejit · 2018-06-10 13:10

I have small application which show result of user entered sql query
in grid.

Currently I am using StringGrid to display result from TSQLQuery if
there is lot of record (10000+) it take very long time to display data
in grid.

Is there any way I can customized StringGrid so I dont have to add all
the data from tsqlquery to grid or is there any other grid component
which can display data fast.

Or any other way to display large data in grid.

----------------------------------------------------------------------
heejit · 2018-06-10 13:39

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i This is
current code to display result in stringgrid procedure
Tform1.show_data_in_string_grid(rs: TSQLQuery; grid1:
TStringGrid);var  i, j: integer;  fc: integer;begin 
self.show_message('started...');  grid1.FixedCols := 0; 
grid1.RowCount := rs.RecordCount + 1;                fc :=
rs.FieldCount;   for j := 0 to fc-1 do  begin    grid1.Columns.Add;  
grid1.Columns[j].Title.Caption := rs.Fields[j].DisplayName;  end;   i
:= 1;  rs.First;  while rs.EOF = False do  begin    grid1.Row := i;  
for j := 0 to fc-1 do    begin      grid1.Cells[j, i] :=
rs.Fields[j].AsString;    end;    rs.Next;    i := i + 1;   end; 
grid1.AutoSizeColumns;  grid1.Row := 1;  self.show_message('Record
Found : ' + IntToStr(grid1.RowCount)); end;

----------------------------------------------------------------------
fred · 2018-06-10 17:06

See: http://forum.lazarus-ide.org/index.php/topic,29536.msg186791.html
Before the while use grid1.BeginUpdate and after reading use
grid1.EndUpdate
Something like this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i rn:
integer; // row number     rn := 0;    grid.BeginUpdate;     while
(not RecordSet.Eof) do    begin      inc(rn);      if rn >=
grid.RowCount      then grid.RowCount := rn+1500;       // fill the
Cell at rn like:      grid.Cells[1,rn] := 'data';     
RecordSet.MoveNext;    end;     grid.RowCount := succ(rn);   
grid.EndUpdate;

----------------------------------------------------------------------
rvk · 2018-06-10 17:38

Quote from: jshah on June 10, 2018, 03:10:37 pmCurrently I am using
StringGrid to display result from TSQLQuery if there is lot of record
(10000+) it take very long time to display data in grid.
You might want to use a TDBGrid.

----------------------------------------------------------------------
heejit · 2018-06-10 18:30

@fred

BeginUpdate and EndUpdate work on 10000+ records very well

But 300,000 record taking long time

----------------------------------------------------------------------
heejit · 2018-06-10 18:39

@rvk

Not showing any records in DBGRID

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.show_data_in_dbgrid(rs: TSQLQuery; grid1: TDBGrid);var  i, j:
integer;  fc: integer;  ds: TDataSource;begin 
self.show_message('started...');  fc := rs.FieldCount;   for j := 0 to
fc-1 do  begin    grid1.Columns.Add;    grid1.Columns[j].Title.Caption
:= rs.Fields[j].DisplayName;  end;   ds := TDataSource.Create(self); 
ds.DataSet := rs;        grid1.DataSource := ds;  
self.show_message('Record Found : ' + IntToStr(rs.RecordCount));end;

----------------------------------------------------------------------
rvk · 2018-06-10 18:43

For now you can remove the columns.
When activating the dataset, the columns of the dbgrid will be created
dynamically.

You can connect all this in designtime unless you have a special
reason to do it in code.

----------------------------------------------------------------------
heejit · 2018-06-10 19:03

Yes I want to connect everything in the code not in Design time

----------------------------------------------------------------------
rvk · 2018-06-10 19:06

Remove the for loop for filling the columns. If no columns are defined
they will be assigned automatically.

Otherwise you need to assign the fieldnames (and you assigned only the
title).

----------------------------------------------------------------------
heejit · 2018-06-10 19:12

Remove the column assignment
But no record is showing in DBGRID

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.show_data_in_dbgrid(rs: TSQLQuery; grid1: TDBGrid);var  i, j:
integer;  fc: integer;  ds: TDataSource;begin 
self.show_message('started...');  fc := rs.FieldCount;  ds :=
TDataSource.Create(self);  ds.DataSet := rs;        grid1.DataSource
:= ds;  rs.Active := True;  self.show_message('Record Found : ' +
IntToStr(rs.RecordCount));end;

----------------------------------------------------------------------
rvk · 2018-06-10 19:16

Did you make sure you don't have any columns defined in designtine?

What does the recordcount give you?

Do you have "empty" rows in the grid or is the grid completely empty
(not even any grid lines)?

----------------------------------------------------------------------
heejit · 2018-06-10 19:27

I did not change any setting in Design Time of DBGRID

Record shows correct number of record here is the full code

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, Forms,
Dialogs,  StdCtrls, Grids, ComCtrls, sqldb, DB,  pqconnection,
LCLType, DBGrids; type  { TForm1 }  TForm1 = class(TForm)            
    DBGrid1: TDBGrid;    ListBox1: TListBox;    Memo1: TMemo;   
StringGrid1: TStringGrid;    procedure FormClose(Sender: TObject; var
CloseAction: TCloseAction);    procedure FormCreate(Sender: TObject); 
  procedure Memo1KeyDown(Sender: TObject; var Key: word; Shift:
TShiftState);    procedure show_data_in_list_view(rs: TSQLQuery; lv:
TListView);    procedure run_sql(sql: string);  private    conn:
TSQLConnection;    transaction: TSQLTransaction;                 
procedure show_data_in_dbgrid(rs: TSQLQuery; grid1: TDBGrid);   
procedure show_data_in_string_grid(rs: TSQLQuery; grid1:
TStringGrid);    procedure show_message(msg: String);  public  
end; var  Form1: TForm1; implementation {$R *.lfm} { TForm1
} procedure TForm1.FormCreate(Sender: TObject);begin  self.conn :=
TPQConnection.Create(self);  self.conn.DatabaseName := 'main_db'; 
self.conn.HostName := '127.0.0.1';  self.conn.UserName := 'user'; 
self.conn.Password := 'password';  self.conn.Connected := True; 
self.transaction := TSQLTransaction.Create(self.conn); 
self.conn.Transaction := self.transaction; 
self.Memo1.Clear; end; procedure TForm1.FormClose(Sender: TObject; var
CloseAction: TCloseAction);begin  conn.Close();end; procedure
TForm1.Memo1KeyDown(Sender: TObject; var Key: word; Shift:
TShiftState);begin  if (ssCtrl in shift) and (key = VK_E) then  begin 
  if (self.Memo1.Lines.Text.Length = 0) then    begin     
ShowMessage('No SQL provided');      Exit;    end;    
self.run_sql(self.Memo1.Lines.Text);   end;end; procedure
TForm1.run_sql(sql: string);var  rs: TSQLQuery;  error: string; 
is_ok: boolean;begin  try    self.show_message('Running sql...');    
rs := TSQLQuery.Create(nil);    rs.DataBase := self.conn;   
rs.SQL.Text := sql;    is_ok := True;    try     
self.transaction.Active := True;      rs.Open;      rs.Last;   
except      on A: EPQDatabaseError do      begin        error :=
A.Message;        is_ok := False;        self.transaction.Active :=
False;      end;    end;    self.show_message('Finished.');     if
is_ok = False then    begin      ShowMessage('Error :' + error);     
exit;    end;    self.show_data_in_string_grid(rs, self.StringGrid1); 
  self.show_data_in_dbgrid(rs, self.DBGrid1);  finally    rs.Free; 
end; end; procedure TForm1.show_message(msg: String);begin 
self.ListBox1.AddItem(msg, nil); 
self.ListBox1.Selected[self.ListBox1.Count-1] := True; 
Application.ProcessMessages;end; procedure
TForm1.show_data_in_list_view(rs: TSQLQuery; lv: TListView);var  i, j:
integer;  vNewItem: TListItem;  fc: integer; begin 
self.show_message('Displaying in Listview...');  lv.Clear; 
lv.Columns.Clear;  fc := rs.FieldCount;  for i := 0 to fc - 1 do 
begin    lv.Columns.Add;    lv.Columns[Lv.ColumnCount - 1].Caption :=
rs.Fields[i].FieldName;    lv.Columns[lv.ColumnCount - 1].AutoSize :=
True;  end;   rs.First;  while rs.EOF = False do  begin    vNewItem :=
lv.Items.Add;    vNewItem.Caption := rs.Fields[0].AsString;    for j
:= 1 to fc - 1 do    begin     
vNewItem.SubItems.Add(rs.Fields[j].AsString);    end;    rs.Next; 
end;  self.show_message('Finished...');  self.show_message('Record
Found : ' + IntToStr(lv.Items.Count)); end; procedure
TForm1.show_data_in_string_grid(rs: TSQLQuery; grid1:
TStringGrid);var  i, j: integer;  fc: integer;begin 
self.show_message('started...');  grid1.FixedCols := 0; 
grid1.RowCount := rs.RecordCount + 1;  fc := rs.FieldCount;   for j :=
0 to fc-1 do  begin    grid1.Columns.Add;   
grid1.Columns[j].Title.Caption := rs.Fields[j].DisplayName;  end;  
grid1.BeginUpdate;  i := 1;  rs.First;  while rs.EOF = False do 
begin    grid1.Row := i;    for j := 0 to fc-1 do    begin     
grid1.Cells[j, i] := rs.Fields[j].AsString;    end;    rs.Next;    i
:= i + 1;  end;  grid1.AutoSizeColumns;  grid1.Row := 1; 
grid1.EndUpdate();  self.show_message('Record Found : ' +
IntToStr(rs.RecordCount));end;  procedure
TForm1.show_data_in_dbgrid(rs: TSQLQuery; grid1: TDBGrid);var  ds:
TDataSource;begin  self.show_message('started...'); 
self.show_message('Record Found : ' + IntToStr(rs.RecordCount));  ds
:= TDataSource.Create(self);  ds.DataSet := rs;  grid1.DataSource :=
ds;  ds.Enabled := True; end;  end.

----------------------------------------------------------------------
rvk · 2018-06-10 19:36

There is your problem. You create and free the dataset in the same
procedure. But you need to keep the dataset open for it to be able to
show it in the tdbgrid. Make the tsqlquery a class variable and create
it in formcreate and free it in formdestroy. Then activate it in your
procedure and keep it active.

----------------------------------------------------------------------
heejit · 2018-06-10 19:55

@rvk

Yes that is the issue I have corrected it is working now.