DBGrid error on scrolling, after SQLQuery filter applied

[Mike.Cornflake]

I don't think you'd need to read all the records for the filter - I think Bill was saying they only read far enough ahead to fully populate the grid.

Doesn't matter, somethings not right - DB Grid shouldn't fall over, even if RecordCount is wrong.  Mind you, Delphi's DBGrid scrollbar was awful, and I'm guessing we're dancing around the very issue.  I cring when I see commercial apps that implement it.

Gosh I miss DevExpress :-)

No need to open any new ticket - the one I posted is still open.  Waiting on a Test Case...

[teos]

I don't think you'd need to read all the records for the filter - I think Bill was saying they only read far enough ahead to fully populate the grid.

Doesn't matter, somethings not right - DB Grid shouldn't fall over, even if RecordCount is wrong.  Mind you, Delphi's DBGrid scrollbar was awful, and I'm guessing we're dancing around the very issue.  I cring when I see commercial apps that implement it.

Gosh I miss DevExpress :-)

No need to open any new ticket - the one I posted is still open.  Waiting on a Test Case...

Then I wonder how one can determine which records meet the filter and all are read without reading the dataset or dertermine the records. And if one has to implement a scrollbar like DBGrid, one has to find out somehow how far the scrollbar should be able to scroll.

I think from 15 years of experience with the standard DBGrid that there is nothing wrong with it, as long as the dataset feeding the grid is inherited properly. And my effords with TDataset tell me that the grid works okay as long as the Dataset works OK.

Brings me back to my first remark: how would one determine records that meet the filter, count them and give that number as recordcount when "filtered = true" and expect a scrollbar to behave correct in the situation where that is not known? Tell me..

DevExpress.. 5000 pages of manual..

I then wonder why not use the VirtualTree then.

[Knipfty]

Mike/teos,

From what I understand, DBGrid is really dependent on the datasource it is attached to.  It is up to the datasource (really, the TTable or TQuery object) to pull in the records.  In reading up on a lot of the documentation on these components, it is up to TTable and TQuery to manage what is brought into memory.

These componenents may or may not read all records, but buffer them in order to limit memory usage.  It's one of the reasons why you cannot depend on .RecordCount.  And why you cannot compare RecNo to RecordCount to determine if you are at the last record.

Perhaps a way around this would be to make calls to .Last and .First.  That may force TQuery and TTable to load all the records.  I'm just guessing, you'll have to test it out.

Knipfty

[Mike.Cornflake]

Knipfty said:

These componenents may or may not read all records, but buffer them in order to limit memory usage.  It's one of the reasons why you cannot depend on .RecordCount.  And why you cannot compare RecNo to RecordCount to determine if you are at the last record

Yup, that's my understanding as well, and is why the Delphi DBGrid scrollbar was so awful.  It never assumed it knew anything about the number of records, so never resized itself to give you a clue as to result size.  Dragging the scrollbar down simply loaded the next set of results in.  Mind you, this is a 10 year old memory, things may have changed with later releases...
Mind you, knowing what I do is wrong, doesn't stop me from trying it to see if I can useful fuctionality :-)  I was pleasantly surprised when the Zeoslib components worked just fine, and immediately used that to implement a scrollbar that behaves as I expect scrollbars to work.
I suspect we'll find that the bug is not within the TSQLQuery - that really looks like functionality by design.  It's DBGrid making assumptions that results in a more useful scrollbar.  And in truth I'll take useful scrollbar behaviour over simple filtering any day.  I've now implemented my filtering at the SQL level (as suggested earlier in this thread).  Means less generic code, but I'm no longer coding in a professional environment, so I can live with that.
Which is the reason I miss DevExpress grids.  No filtering was ever done on the dataset, all filtering was done by the grid.  And this feels right to me.  How they achieved their phenomenal speeds on large datasets is beyond my understanding, and is why I never begrudged paying their costs.

Perhaps a way around this would be to make calls to .Last and .First.  That may force TQuery and TTable to load all the records.  I'm just guessing, you'll have to test it out.

Should have reported this earlier.  Tried this early on, with no effect.
You know, I've never, ever, used TTable :-)  All my database work has been with normalised schemas.  I cannot tell you if filtering can be done on a TTable, and if it can, whether this issue reproduces there as well.

[Mike.Cornflake]

Teos said:

Brings me back to my first remark: how would one determine records that meet the filter, count them and give that number as recordcount when "filtered = true" and expect a scrollbar to behave correct in the situation where that is not known? Tell me..

Can't :-)  There you have the nub of the matter.  We've one of two issues.  Either TSQLQuery should report a correct RecordCount (and that calculation can be expensive) OR DBGrid should never assume RecordCount is correct.   
In fact, I vaguely remember some ADO code that never populated RecordCount at all unless a certain call was made. 

DevExpress.. 5000 pages of manual..

Ah, but what riches await the person who reads that manual? :-)   And I never did, there was a nice set of samples that came with it, so I just opened up the appropriate sample and worked out what I needed from there.

I then wonder why not use the VirtualTree then.

No idea, never used it.  Are you recommending it for the display/filtering of datasets?  If so, I'll go play - sounds intriguing.  Is this what you're talking about?  http://wiki.lazarus.freepascal.org/VirtualTreeview

[teos]

Teos said:

Brings me back to my first remark: how would one determine records that meet the filter, count them and give that number as recordcount when "filtered = true" and expect a scrollbar to behave correct in the situation where that is not known? Tell me..

Can't :-)  There you have the nub of the matter.  We've one of two issues.  Either TSQLQuery should report a correct RecordCount (and that calculation can be expensive) OR DBGrid should never assume RecordCount is correct.   
In fact, I vaguely remember some ADO code that never populated RecordCount at all unless a certain call was made. 

Zeoslib works OK because it just does what I say: run a query which gives back all records within the query/filter

DevExpress.. 5000 pages of manual..

Ah, but what riches await the person who reads that manual? :-)   And I never did, there was a nice set of samples that came with it, so I just opened up the appropriate sample and worked out what I needed from there.
[/quote]
Okay when you step in DevExpress on a new project but a PITA if you have to make up large parts of the workings out of someone else's code.

I then wonder why not use the VirtualTree then.

No idea, never used it.  Are you recommending it for the display/filtering of datasets?  If so, I'll go play - sounds intriguing.
[/quote]
VirtualTree could indeed be what you look for in Lazarus. It's not a DevExpress grid but could be very usefull for you.

[Mike.Cornflake]

If it's http://wiki.lazarus.freepascal.org/VirtualTreeview you're talking about then bugger - I'm currently working on a vessel in the north sea, and sourceforge is one of the blocked sites :-(  Have to wait until I'm home again...

Okay when you step in DevExpress on a new project but a PITA if you have to make up large parts of the workings out of someone else's code

True, DexEv was not an intuitive set of controls to use...  I miss all that extra functionality you got over a normal grid though...

[taazz]

If it's http://wiki.lazarus.freepascal.org/VirtualTreeview you're talking about then bugger - I'm currently working on a vessel in the north sea, and sourceforge is one of the blocked sites :-(  Have to wait until I'm home again...

I would download code typhoon instead it come with a number of extra components including tvirtualtreeview and a few virtualdbgrids and trees it will cut your development time considerably. The virtualtreeviews component pack do not have any DB aware components.

http://www.pilotlogic.com/sitejoom/index.php/codetyphon/codetyphon-download

[Mike.Cornflake]

Nice  :-)  I *love* open source projects.  So, someone has taken Lazarus/FreePascal and a whole slew of open components and packaged them for simple release.
Shame about the 450Mb size though.  Although that download isn't blocked, I wont try it here.  One quick way of getting booted off a vessel

[xico]

Hi all!

After some weeks away I came back yesterday to the forum and I saw alot of comments and ideas...

Well, my conclusion is, I won't use the Filter together with the DBGrid as long as the RecordCount issue isn't corrected.

Anyhow, yesterday I was playing around with it and I came with a WORKAROUND.

The idea is:

• Disable the DBGrid Vertical Scrollbar and use a TScrollbar instead
• Use a TStringList (or a TList/TArray) to keep tracking of the Records after applying the Filter

And this is just the pick of the iceberg. I didn't work on things like:

• catching exceptions, ie: if the Filter returns zero resullts it will throw a "Scrollbar Out of Range"
• syncing the DBNavigator with the TScrollbar
• add Mouse ScrollWheel logic to the TScrollbar component
• set the TScrollbar thumb height to visually match the number of Rows on the Grid
• etc.

Some of these are important other are optional.


For my test I ceated a SQLLite test table with two columns, a "ID" (Int, Auto_Increment) and a "DUMMY" (Int).
I inserted about 50 records with values of 3,8 and 12.
The filter I want to apply is "dummy =" and then use 3,8 or 12.
The components are:

• SQLite3Connection1
• SQLTransaction1
• SQLQuery1 (Main Query)
• SQLQuery2 (to fill ComboBox1 with "DISTINCT" values from the "dummy" column)
• Datasource1
• DBGrid1 (where Scrollbars:= ssHorizontal. No vertical Scrollbar!)
• ScrollBar1
• ComboBox1 (filled with the values 'OFF' plus the result of SQLQuery2)
• Edit1 (TEdit component to show the total number of rows)

The main procedure here is the one called "SetupScrollbar" wich fills a TStringList with the RecNos that are then used to feed the Scrollbar:

Code: [Select]

procedure TForm1.SetupScrollbar;
  var i: Integer;
begin
  with SQLQuery1 do
    if Filtered then
    begin  // FILTER is ON
      RecList.Clear;
      i:= -1;
      DisableControls;
      First;
      while not EOF do
      begin
        Inc(i);
        RecList.Add(IntToStr(RecNo));
        Next;
      end;
      ScrollBar1.Min:= 0;
      ScrollBar1.Max:= RecList.Count-1;
      Edit1.Text:= IntToStr(RecList.Count);
      First;
      EnableControls;
    end  // --------------------------------
    else
    begin  // FILTER is OFF
      ScrollBar1.Min:= 1;
      ScrollBar1.Max:= RecordCount;
      Edit1.Text:= IntToStr(RecordCount);
      First;
    end;
end;

CONTINUATION ->

[xico]

-> CONTINUING

the whole thing is:

Code: [Select]

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  sqldb, sqlite3conn, db, Forms, DBGrids, StdCtrls, Classes, SysUtils;

type

  { TForm1 }

  TForm1 = class(TForm)
    ComboBox1: TComboBox;
    DBGrid1: TDBGrid;
    Datasource1: TDatasource;
    Edit1: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    ScrollBar1: TScrollBar;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLQuery2: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure ComboBox1Select(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure ScrollBar1Scroll(Sender: TObject; ScrollCode: TScrollCode;
      var ScrollPos: Integer);
    procedure SQLQuery1AfterScroll(DataSet: TDataSet);
  private
    { private declarations }
    RecList: TStringList;
    procedure SetupQuery1;
    procedure SetupQuery2;
    procedure SetupScrollbar;
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }


procedure TForm1.SetupQuery1;
begin
  with SQLQuery1 do
  begin
    Close;
    Filtered := False;
    SQL.Clear;
    SQL.Text := 'SELECT * FROM filter';
    Open;
    First;
  end;
end;

procedure TForm1.SetupQuery2;
begin
  with SQLQuery2 do
  begin
    Close;
    SQL.Clear;
    SQL.Text := 'SELECT DISTINCT dummy FROM filter ORDER BY dummy ASC';
    Open;
    First;
    with ComboBox1 do
    begin
      Clear;
      Items.Add('OFF');
      while not EOF do
      begin
        Items.Add(FieldValues['dummy']);
        Next;
      end;
      ItemIndex:= 0;
    end;
    Close;
  end;
end;

procedure TForm1.SetupScrollbar;
  var i: Integer;
begin
  with SQLQuery1 do
    if Filtered then
    begin  // FILTER is ON
      RecList.Clear;
      i:= -1;
      DisableControls;
      First;
      while not EOF do
      begin
        Inc(i);
        RecList.Add(IntToStr(RecNo));
        Next;
      end;
      ScrollBar1.Min:= 0;
      ScrollBar1.Max:= RecList.Count-1;
      Edit1.Text:= IntToStr(RecList.Count);
      First;
      EnableControls;
    end  // --------------------------------
    else
    begin  // FILTER is OFF
      ScrollBar1.Min:= 1;
      ScrollBar1.Max:= RecordCount;
      Edit1.Text:= IntToStr(RecordCount);
      First;
    end;
end;


// -------------------------------------------
procedure TForm1.FormCreate(Sender: TObject);
begin
  RecList:= TStringList.Create;
  SetupQuery1;
  SetupQuery2;
  SetupScrollbar;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  RecList.Free;
end;
// -------------------------------------------

procedure TForm1.ComboBox1Select(Sender: TObject);
begin
  if TComboBox(Sender).Text = 'OFF' then
  begin
    SQLQuery1.Filtered:= False;
    SetupScrollbar;
  end
  else
    with SQLQuery1 do
    begin
      Filter:= 'dummy = '+ ComboBox1.Text;
      Filtered:= True;
      SetupScrollbar;
    end;
end;

procedure TForm1.ScrollBar1Scroll(Sender: TObject; ScrollCode: TScrollCode;
  var ScrollPos: Integer);
begin
  if SQLQuery1.Filtered then
    SQLQuery1.RecNo:= StrToInt(RecList[ScrollPos])
  else
    SQLQuery1.RecNo:= ScrollPos;
end;

procedure TForm1.SQLQuery1AfterScroll(DataSet: TDataSet);
begin
  if SQLQuery1.Filtered then
    ScrollBar1.Position:= RecList.IndexOf(IntToStr(DataSet.RecNo))
  else
    ScrollBar1.Position:= DataSet.RecNo;
end;

end.

As I said, this is a workaround but might be useful for someone out there.

Zip file attached, 3 parts! Exe, db and sqlite.dll.

Best regards for all you people.

[xico]

Sorry for the extra entries, it's for the zip files 

[xico]

The last one!       

[teos]

I wonder: what about the onfilter event? If I'm correct that is executed for every record to be matched. My wonder: how about a variable which counts the number of times the event is executed?

If I'm correct, the event is triggered from TDataset at the part where records are fetched.

[Mike.Cornflake]

Hello community!

Specs:
Lazarus 1.1 (2012-05-11)
FPC 2.6.1 (SVN Rev. 37249)
Windows XP SP3
----
MySQL 5.1 on a Linux Server
--------------------------------------

Usually I make my filtering direct with SQL but I wanted to try the Filter property of TSQLQuery.

I made some search but I couldn't find an answer for my problem.

This is the problem: after applying a filter on a SQLQuery, if I scroll the DBGrid by grabbing the DBGrid scrollbar I get the following error: "SQLQueryMain : Could not find the requested record".

Many moons ago I said I'd create a test application that reproduces this issue.  For ease of distribution, I created the test application using sqlite.   The original poster saw this issue with MySQL & with SQLite.  I've seen it with MS SQL (via ODBC (but you'll have to take my word for it, creating a test for MS SQL is useful only to people with SQL Server hanging around)) and now this test code reproduces the issue again with SQLite.    However, before I dig further, or post on the bug tracker, could someone please confirm I'm not doing something stupid with filtering?

My filtering code is nothing more than...

Code: [Select]

procedure TForm1.btnFilterClick(Sender: TObject);
begin
  If SQLQuery1.Active Then
  Begin
    SQLQuery1.Filtered:= False;
    SQLQuery1.FilterOptions:=[];
    SQLQuery1.Filter  := 'Field2 < 500';
    SQLQuery1.Filtered:= True;
  end;
end;
Here's the complete project, including pre-compiled exe (win 32) and sqllite3.dll.  Run the exe.  Press btnConnect once.  Press btnAddRecords twice (each time adds 10,000 records).  Press btnFilter.  Try to scroll the DBGrid using the vertical scrollbar (grab the thumb, drag to a new position and let go.)  Dragging may work a few times, but you'll eventually hit the problem (obviously when you finally end up on a record that the DBGrid thinks is still in the TDataset, but which the TDataset has filtered out).  Exactly as the original poster reported.

Click here to download the project...
http://db.tt/L9bzree8
(https://dl.dropboxusercontent.com/u/59503182/Lazarus/DBGrid%20Error/DBGrid_Error.7z for those who don't like shortened URLs)

The project was built using Lazarus 1.0.8/FPC 2.6.2.  My SVN copy is 64 bit, and I couldn't find a 64bit sqlite.dll to connect with, so I was unable to test with latest...

Previously we got bogged down with assumptions it was RecordCount being wrong and other such.  I'm no longer convinced.  I think the OP and I are either using Filtering incorrectly, or that we do in fact have an issue with DBGrid.

Looking for the following assistance:
* Clarification .Filter is being used correctly...
* If .Filter is being used correctly, then testing with SVN Lazarus to see if the issue has since been solved.

Many thanks, and sincere apologies for taking OVER a year before I remembered to investigate this further... :-(

UPDATE:  Oh, that's interesting.  The issue doesn't appear to occur until you first drag the scrollbar thumbnail right to the very end of the grid.  Thereafter the issue occurs reasonably quickly....