DBGrid error on scrolling, after SQLQuery filter applied

[xico]

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".

But it works if I use the mouse scroll wheel or the DBNavigator!

It seems the DBGrid Scrollbar logic always "sees" the whole SQLQuery result ignoring any filtering while scrolling with the mouse or with the DBNavigator it respects the applied filter.

Is it so by design? Is there something else I should set after applying the filter?

I'll be very thankful for any help I could get.

I tried a very simple application, and here is some of the code I used:

Code: [Select]

// ...

procedure TForm1.ButtonOpenQueryClick(Sender: TObject);
begin
  //...
    with SQLQueryMain do
    try
      // Filtered:= False - designtime
      Close;
      SQL.Clear;
      SQL.Text:= 'SELECT * FROM test.bigtable';
      Open;
    except
      on E: Exception do ShowMessage('excep: '+ E.message);
      on E: EDatabaseError do ShowMessage('dbErr: '+ E.message);
    end;
  //...
end;

// ...

procedure TFormMain.CheckBoxFilterClick(Sender: TObject);
begin
  with TCheckBox(Sender) do
  if State = cbUnchecked then
    SQLQueryMain.Filtered:= False
  else
    if State = cbChecked then
      with SQLQueryMain do
      begin
        if EditFilter.Text = '' then
          begin
            Filtered:= False;
            TCheckBox(Sender).State:= cbUnchecked;
          end
        else
        begin
          FilterOptions:= [foCaseInsensitive];
          Filter:= EditFilter.Text;
          Filtered:= True;
          First;
        end;
      end;
end;



[xico]

I also tried the above situation with the stable version "lazarus-0.9.30.4-fpc-2.6.0-win32" but I got the same result.

Any idea anyone?

[brunello]

procedure TForm1.ButtonOpenQueryClick(Sender: TObject);
begin
  //...
    with SQLQueryMain do
    try
      // Filtered:= False - designtime
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM test.bigtable');
      SQL.Add('');
      Open;
    except
      on E: Exception do ShowMessage('excep: '+ E.message);
      on E: EDatabaseError do ShowMessage('dbErr: '+ E.message);
    end;
  //...
end;

procedure TFormMain.CheckBoxFilterClick(Sender: TObject);
begin
  SQLQueryMain.Close;
  if TCheckBox(Sender).State = cbUnchecked then
    SQLQueryMain.SQL[1] := ''
  else
    SQLQueryMain.SQL[1] := 'WHERE field=' + QuotedStr(EditFilter.Text);
  SQLQueryMain.Open;
end;

[xico]

Hi brunello, thank you very much for your comment.

Unfortunately this is not what I'm looking for. What you suggest is what I usually do, that is, reQuering the Database.

By using the TSQLQuery Filter property, no new Query will be made but instead the DataSet will just be filtered, that is locally done and spares a connection to the Database server.

As I stated on my first post, the Filter works correctly, at least with my test program wich is very simple, the problem is when I scroll the DBGrid by grabbing the DBGrid scrollbars I get this error message "SQLQueryMain : Could not find the requested record".

I created a test database with five columns, the first one as 'ID' (auto increment) and I generated 100000 (one hundred thousand) records.

When I apply a filter, let's say "COL2 <= 50000", I get a result of about 300 records displayed on the DBGrid. I can navigate using the DBNavigator buttons and using the mouse scroll wheel but I get the error stated above when I scroll the DBGrid by grabbing the DBGrid scrollbars.

When I look at the DBGrid Vertical Scrollbar after the filter is applied, and I got a result of 300 records out of 100000, I see that the scrollbar size stays the same when it should get bigger because there are far less records on the DBGrid.

Well, I hope the Gurus out there have a clue about this!

And again, thank you for your suggestion.

[Mike.Cornflake]

Yup - I get the error as well.  I'm using TODBCConnection, connecting to MS SQL.

When I used the Zeos library, connecting to MySQL I didn't get the error.   Filtering worked perfectly.  That was using TZQuery instead of TSQLQUery.  Mind you, that was also using Laz 0.9.28 a year or two ago.   Maybe something has broken in the meantime...

Unfortunately for me, I can't work out to get Zeos to connect to MS SQL in a way that gets me access to more than the first 4Kb of any Blob field...

[Lacak2]

Can you prepare test project (which creates test table populates data etc.), which reproduces error and upload it here se other people can look at it?

[Mike.Cornflake]

Yup, I'll try and get onto that over the next day or two...

[Elmug]

Seems to me that this could be a timing problem of the scroll bar going too fast for the updating of the data in the grid.

You might try scrolling, but at snail speed, to see if the problem still happens?

[teos]

The grid is missing an update that lets it read the number of records from the dataset. Maybe disabling->enabling the datasource helps or maybe a dataset.refresh helps?

[piratewar]

I GOT THAT PROBLEM TOO WITH FIREBIRd DATABASE AND TIBCONNECTION

[Lacak2]

If you set PacketRecords=-1 in your TSQLQuery does something changes (error disappears) ?

[Mike.Cornflake]

G'day,

Apologies for the long delay - the test case is still on my TODO, but I'm swamped at work at the moment, could be several weeks before the pressure decreases.

In the meatime I have tried both PacketRecords:=-1 and .Refresh.  Neither resolved the issue.

Minor update.  The original poster reported "Could not find the requested record".  I had his exact error as well, but I was using my own scrollbar instead of the DBGrid scrollbar.  It's the dataset.RecordCount that doesn't get updated when you apply Filter and my Scrollbar code was using that to determine the range.

So, I'm no longer using my own scrollbar, but using entirely DBGrid functionality, and I still get the same error.  It's also in the scrollbar code for the DBGrid, so I'm guessing that uses RecordCount/RecNo as well.  The controls that work (DBNavigator) only use First/Next/Prior/Last, so they don't encounter the issue.

It doesn't look like it's the DBGrid missing an update, only the filtered dataset appears in the grid.  As I said, the recordcount of TSQLQuery doesn't get updated following a Filter.   Could be functionality by design in TSQLQuery, I've not used it before.  The ZeosLib TZQuery updates RecordCount, and I've got used to that...  Mind you, then why the DBGrid error?  Sigh.  Enough speculation, I will try to make time and get that test case uploaded.

Cheers

Mike

[teos]

OK.

This is definetely a implementation bug of SQLQuery then. I have seen this before on situations where the number of records is read from the database instead of determining it by the number of actual records.

[Mike.Cornflake]

May not be a bug, may be design functionality.  I just searched the bugtracker (should have done that first)

http://bugs.freepascal.org/view.php?id=12238

I see the bug tracker also has an outstanding request for a sample app/test case.  <sigh> I will get onto it... :-)

[teos]

hm.. I see arguments in both issues that make no sense: keeping a recordcount of filtered records, should require to read the entire database (Bill Todd) but he forgets that all records have to be read allready to determine if they meet the filter.

how delphi does this.. well.. Luiz should have read somewhat better.

I think I'd re-open the ticket or file a new one.