Recent

Author Topic: [SOLVED] Grid To display SQL query result  (Read 1504 times)

jshah

  • Full Member
  • ***
  • Posts: 128
[SOLVED] Grid To display SQL query result
« on: June 10, 2018, 03:10:37 pm »
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.

« Last Edit: June 12, 2018, 04:12:33 pm by jshah »

jshah

  • Full Member
  • ***
  • Posts: 128
Re: Grid To display SQL query result
« Reply #1 on: June 10, 2018, 03:39:53 pm »
Code: Pascal  [Select]
  1. This is current code to display result in stringgrid
  2.  
  3. procedure Tform1.show_data_in_string_grid(rs: TSQLQuery; grid1: TStringGrid);
  4. var
  5.   i, j: integer;
  6.   fc: integer;
  7. begin
  8.   self.show_message('started...');
  9.   grid1.FixedCols := 0;
  10.   grid1.RowCount := rs.RecordCount + 1;
  11.                 fc := rs.FieldCount;
  12.  
  13.   for j := 0 to fc-1 do
  14.   begin
  15.     grid1.Columns.Add;
  16.     grid1.Columns[j].Title.Caption := rs.Fields[j].DisplayName;
  17.   end;
  18.  
  19.   i := 1;
  20.   rs.First;
  21.   while rs.EOF = False do
  22.   begin
  23.     grid1.Row := i;
  24.     for j := 0 to fc-1 do
  25.     begin
  26.       grid1.Cells[j, i] := rs.Fields[j].AsString;
  27.     end;
  28.     rs.Next;
  29.     i := i + 1;
  30.  
  31.   end;
  32.   grid1.AutoSizeColumns;
  33.   grid1.Row := 1;
  34.   self.show_message('Record Found : ' + IntToStr(grid1.RowCount));
  35.  
  36. end;
  37.  

fred

  • Full Member
  • ***
  • Posts: 118
Re: Grid To display SQL query result
« Reply #2 on: June 10, 2018, 07:06:16 pm »
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]
  1. rn: integer; // row number
  2.  
  3.     rn := 0;
  4.     grid.BeginUpdate;
  5.  
  6.     while (not RecordSet.Eof) do
  7.     begin
  8.       inc(rn);
  9.       if rn >= grid.RowCount
  10.       then grid.RowCount := rn+1500;
  11.  
  12.       // fill the Cell at rn like:
  13.       grid.Cells[1,rn] := 'data';
  14.       RecordSet.MoveNext;
  15.     end;
  16.  
  17.     grid.RowCount := succ(rn);
  18.     grid.EndUpdate;
  19.  
« Last Edit: June 10, 2018, 07:21:26 pm by fred »
Started with OmegaSoft Pascal on OS-9/68k , now Lazarus 1.8.4 / FPC 3.0.4 on Windows 7

rvk

  • Hero Member
  • *****
  • Posts: 3504
Re: Grid To display SQL query result
« Reply #3 on: June 10, 2018, 07:38:54 pm »
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.
You might want to use a TDBGrid.

jshah

  • Full Member
  • ***
  • Posts: 128
Re: Grid To display SQL query result
« Reply #4 on: June 10, 2018, 08:30:51 pm »
@fred

BeginUpdate and EndUpdate work on 10000+ records very well

But 300,000 record taking long time

jshah

  • Full Member
  • ***
  • Posts: 128
Re: Grid To display SQL query result
« Reply #5 on: June 10, 2018, 08:39:27 pm »
@rvk

Not showing any records in DBGRID

Code: Pascal  [Select]
  1. procedure TForm1.show_data_in_dbgrid(rs: TSQLQuery; grid1: TDBGrid);
  2. var
  3.   i, j: integer;
  4.   fc: integer;
  5.   ds: TDataSource;
  6. begin
  7.   self.show_message('started...');
  8.   fc := rs.FieldCount;
  9.  
  10.   for j := 0 to fc-1 do
  11.   begin
  12.     grid1.Columns.Add;
  13.     grid1.Columns[j].Title.Caption := rs.Fields[j].DisplayName;
  14.   end;
  15.  
  16.   ds := TDataSource.Create(self);
  17.   ds.DataSet := rs;
  18.         grid1.DataSource := ds;
  19.  
  20.   self.show_message('Record Found : ' + IntToStr(rs.RecordCount));
  21. end;
  22.  
  23.  


rvk

  • Hero Member
  • *****
  • Posts: 3504
Re: Grid To display SQL query result
« Reply #6 on: June 10, 2018, 08:43:13 pm »
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.
« Last Edit: June 10, 2018, 08:45:26 pm by rvk »

jshah

  • Full Member
  • ***
  • Posts: 128
Re: Grid To display SQL query result
« Reply #7 on: June 10, 2018, 09:03:48 pm »
Yes I want to connect everything in the code not in Design time

rvk

  • Hero Member
  • *****
  • Posts: 3504
Re: Grid To display SQL query result
« Reply #8 on: June 10, 2018, 09:06:48 pm »
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).

jshah

  • Full Member
  • ***
  • Posts: 128
Re: Grid To display SQL query result
« Reply #9 on: June 10, 2018, 09:12:11 pm »
Remove the column assignment
But no record is showing in DBGRID

Code: Pascal  [Select]
  1. procedure TForm1.show_data_in_dbgrid(rs: TSQLQuery; grid1: TDBGrid);
  2. var
  3.   i, j: integer;
  4.   fc: integer;
  5.   ds: TDataSource;
  6. begin
  7.   self.show_message('started...');
  8.   fc := rs.FieldCount;
  9.   ds := TDataSource.Create(self);
  10.   ds.DataSet := rs;
  11.         grid1.DataSource := ds;
  12.   rs.Active := True;
  13.   self.show_message('Record Found : ' + IntToStr(rs.RecordCount));
  14. end;
  15.  

rvk

  • Hero Member
  • *****
  • Posts: 3504
Re: Grid To display SQL query result
« Reply #10 on: June 10, 2018, 09:16:34 pm »
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)?

jshah

  • Full Member
  • ***
  • Posts: 128
Re: Grid To display SQL query result
« Reply #11 on: June 10, 2018, 09:27:13 pm »
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]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, Forms, Dialogs,
  9.   StdCtrls, Grids, ComCtrls, sqldb, DB,
  10.   pqconnection, LCLType, DBGrids;
  11.  
  12. type
  13.   { TForm1 }
  14.   TForm1 = class(TForm)
  15.                   DBGrid1: TDBGrid;
  16.     ListBox1: TListBox;
  17.     Memo1: TMemo;
  18.     StringGrid1: TStringGrid;
  19.     procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
  20.     procedure FormCreate(Sender: TObject);
  21.     procedure Memo1KeyDown(Sender: TObject; var Key: word; Shift: TShiftState);
  22.     procedure show_data_in_list_view(rs: TSQLQuery; lv: TListView);
  23.     procedure run_sql(sql: string);
  24.   private
  25.     conn: TSQLConnection;
  26.     transaction: TSQLTransaction;
  27.                   procedure show_data_in_dbgrid(rs: TSQLQuery; grid1: TDBGrid);
  28.     procedure show_data_in_string_grid(rs: TSQLQuery; grid1: TStringGrid);
  29.     procedure show_message(msg: String);
  30.   public
  31.  
  32.   end;
  33.  
  34. var
  35.   Form1: TForm1;
  36.  
  37. implementation
  38.  
  39. {$R *.lfm}
  40.  
  41. { TForm1 }
  42.  
  43. procedure TForm1.FormCreate(Sender: TObject);
  44. begin
  45.   self.conn := TPQConnection.Create(self);
  46.   self.conn.DatabaseName := 'main_db';
  47.   self.conn.HostName := '127.0.0.1';
  48.   self.conn.UserName := 'user';
  49.   self.conn.Password := 'password';
  50.   self.conn.Connected := True;
  51.   self.transaction := TSQLTransaction.Create(self.conn);
  52.   self.conn.Transaction := self.transaction;
  53.   self.Memo1.Clear;
  54.  
  55. end;
  56.  
  57. procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
  58. begin
  59.   conn.Close();
  60. end;
  61.  
  62. procedure TForm1.Memo1KeyDown(Sender: TObject; var Key: word; Shift: TShiftState);
  63. begin
  64.   if (ssCtrl in shift) and (key = VK_E) then
  65.   begin
  66.     if (self.Memo1.Lines.Text.Length = 0) then
  67.     begin
  68.       ShowMessage('No SQL provided');
  69.       Exit;
  70.     end;
  71.  
  72.     self.run_sql(self.Memo1.Lines.Text);
  73.  
  74.   end;
  75. end;
  76.  
  77. procedure TForm1.run_sql(sql: string);
  78. var
  79.   rs: TSQLQuery;
  80.   error: string;
  81.   is_ok: boolean;
  82. begin
  83.   try
  84.     self.show_message('Running sql...');
  85.  
  86.     rs := TSQLQuery.Create(nil);
  87.     rs.DataBase := self.conn;
  88.     rs.SQL.Text := sql;
  89.     is_ok := True;
  90.     try
  91.       self.transaction.Active := True;
  92.       rs.Open;
  93.       rs.Last;
  94.     except
  95.       on A: EPQDatabaseError do
  96.       begin
  97.         error := A.Message;
  98.         is_ok := False;
  99.         self.transaction.Active := False;
  100.       end;
  101.     end;
  102.     self.show_message('Finished.');
  103.  
  104.     if is_ok = False then
  105.     begin
  106.       ShowMessage('Error :' + error);
  107.       exit;
  108.     end;
  109.     self.show_data_in_string_grid(rs, self.StringGrid1);
  110.     self.show_data_in_dbgrid(rs, self.DBGrid1);
  111.   finally
  112.     rs.Free;
  113.   end;
  114.  
  115. end;
  116.  
  117. procedure TForm1.show_message(msg: String);
  118. begin
  119.   self.ListBox1.AddItem(msg, nil);
  120.   self.ListBox1.Selected[self.ListBox1.Count-1] := True;
  121.   Application.ProcessMessages;
  122. end;
  123.  
  124. procedure TForm1.show_data_in_list_view(rs: TSQLQuery; lv: TListView);
  125. var
  126.   i, j: integer;
  127.   vNewItem: TListItem;
  128.   fc: integer;
  129.  
  130. begin
  131.   self.show_message('Displaying in Listview...');
  132.   lv.Clear;
  133.   lv.Columns.Clear;
  134.   fc := rs.FieldCount;
  135.   for i := 0 to fc - 1 do
  136.   begin
  137.     lv.Columns.Add;
  138.     lv.Columns[Lv.ColumnCount - 1].Caption := rs.Fields[i].FieldName;
  139.     lv.Columns[lv.ColumnCount - 1].AutoSize := True;
  140.   end;
  141.  
  142.   rs.First;
  143.   while rs.EOF = False do
  144.   begin
  145.     vNewItem := lv.Items.Add;
  146.     vNewItem.Caption := rs.Fields[0].AsString;
  147.     for j := 1 to fc - 1 do
  148.     begin
  149.       vNewItem.SubItems.Add(rs.Fields[j].AsString);
  150.     end;
  151.     rs.Next;
  152.   end;
  153.   self.show_message('Finished...');
  154.   self.show_message('Record Found : ' + IntToStr(lv.Items.Count));
  155.  
  156. end;
  157.  
  158. procedure TForm1.show_data_in_string_grid(rs: TSQLQuery; grid1: TStringGrid);
  159. var
  160.   i, j: integer;
  161.   fc: integer;
  162. begin
  163.   self.show_message('started...');
  164.   grid1.FixedCols := 0;
  165.   grid1.RowCount := rs.RecordCount + 1;
  166.   fc := rs.FieldCount;
  167.  
  168.   for j := 0 to fc-1 do
  169.   begin
  170.     grid1.Columns.Add;
  171.     grid1.Columns[j].Title.Caption := rs.Fields[j].DisplayName;
  172.   end;
  173.  
  174.   grid1.BeginUpdate;
  175.   i := 1;
  176.   rs.First;
  177.   while rs.EOF = False do
  178.   begin
  179.     grid1.Row := i;
  180.     for j := 0 to fc-1 do
  181.     begin
  182.       grid1.Cells[j, i] := rs.Fields[j].AsString;
  183.     end;
  184.     rs.Next;
  185.     i := i + 1;
  186.   end;
  187.   grid1.AutoSizeColumns;
  188.   grid1.Row := 1;
  189.   grid1.EndUpdate();
  190.   self.show_message('Record Found : ' + IntToStr(rs.RecordCount));
  191. end;
  192.  
  193.  
  194. procedure TForm1.show_data_in_dbgrid(rs: TSQLQuery; grid1: TDBGrid);
  195. var
  196.   ds: TDataSource;
  197. begin
  198.   self.show_message('started...');
  199.   self.show_message('Record Found : ' + IntToStr(rs.RecordCount));
  200.   ds := TDataSource.Create(self);
  201.   ds.DataSet := rs;
  202.   grid1.DataSource := ds;
  203.   ds.Enabled := True;
  204.  
  205. end;
  206.  
  207.  
  208. end.
  209.  

rvk

  • Hero Member
  • *****
  • Posts: 3504
Re: Grid To display SQL query result
« Reply #12 on: June 10, 2018, 09:36:44 pm »
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.

jshah

  • Full Member
  • ***
  • Posts: 128
Re: Grid To display SQL query result
« Reply #13 on: June 10, 2018, 09:55:13 pm »
@rvk

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