Print Page - Sorting tsqlquery with indexes working example(Yes it works now)

Programming => Databases => Topic started by: snorkel on March 06, 2013, 01:22:18 am

Title: Sorting tsqlquery with indexes working example(Yes it works now)
Post by: snorkel on March 06, 2013, 01:22:18 am

I am using this example:
http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDataSeta

But it seems to have issues with tsqlquery, it's not releasing the indexes, and even if I set the max to 100 it will eventually error out.

I can't seem to find a decent example of how to sort the tsqlquery with a dbgrid.

Title: Re: Trying to sort a tsqlquery with indexes not work right
Post by: snorkel on March 06, 2013, 09:30:41 pm

In case anyone cares this works and displays the sort arrow from the
defined title image list.

(openquery in the code is a tsqlquery object)

Code: [Select]

procedure TSQLForm.ResultsGridTitleClick(Column: TColumn);
var
  ASC_IndexName,DESC_IndexName:string;
begin
     ASC_IndexName:='ASC_'+Column.FieldName;
     DESC_IndexName:='DESC_'+Column.FieldName;
     //indexes cant sort binary types such as ftmemo, should add blob types to this as well
     if(Column.Field.DataType = ftmemo) or (Column.Field.DataType = ftwidememo)then Exit;
     //check if a Ascending index already exists for this column, if not create one
     if OpenQuery.IndexDefs.IndexOf(ASC_IndexName) = -1 then
        openquery.AddIndex(ASC_IndexName,column.FieldName,[]);
     //check if a Descending index already exists for this column, if not create one
     if OpenQuery.IndexDefs.IndexOf(DESC_IndexName) = -1 then
        openquery.AddIndex(DESC_IndexName,column.FieldName,[ixDescending]);
     //ensure index defs are up to date
     OpenQuery.IndexDefs.Updated:=false;  {<<<----It won't work without this line}
     OpenQuery.IndexDefs.Update;
     //use the column tag to toggle ASC/DESC
     column.tag :=  not column.tag;
     if boolean(column.tag) then
      begin
           Column.Title.ImageIndex:=12;
           openquery.IndexName:=ASC_IndexName;
      end else
          begin
               Column.Title.ImageIndex:=13;
               OpenQuery.IndexName:=DESC_IndexName;
          end;
     if (flastcolumn <> nil) and (flastcolumn <> Column) then
        flastcolumn.Title.ImageIndex:=-1;
     flastcolumn:=column;
end;

The one in the wiki does not work properly and does not need all the RTTI stuff anymore:
http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDataSeta

Title: Re: Sorting tsqlquery with indexes working example
Post by: goodname on March 06, 2013, 10:58:16 pm

Thanks for posting the response. Was looking in to doing this a little while back but didn't go into the depth that you did. It would be good if you could update the wiki with this information as well.

Title: Re: Sorting tsqlquery with indexes working example
Post by: snorkel on March 06, 2013, 11:02:07 pm

hi,
I was playing around with this but there appears to be a issue with tsqlquery indexdefs in 2.6.2

even though I have the max indexes count set to 100 I get the max number of indexes used error.
So put a loop to check what's in indexdefs and it only adds the indexes for the first column I click on, then after that the indexes just keep building up because the other columns are not in indexdefs even after having been added.

Can anyone verify?

Title: Re: Sorting tsqlquery with indexes working example
Post by: Lacak2 on March 07, 2013, 01:50:11 pm

Do you set MaxIndexesCount before you open bufdataset ?
(I assume that yes)

Then you use AddIndex() and you get error :
"The maximum amount of indexes is reached" ?

Can you poste here small exampe which will show it ?

Title: Re: Sorting tsqlquery with indexes working example
Post by: snorkel on March 07, 2013, 04:59:31 pm

Yes, I set max indexes to 100 and then use the code I posted.

It works great if I only click on one column title to sort, as soon as I add another index for another column it can't find them in the indexdefs list anymore and so it keeps adding new indexes.

If I loop through index defs it only shows 4 indexes, one called "DEFAULT_ORDER", one with no name and the first two that where added when I clicked on the first column I wanted to sort.

I am using TSQLQuery with SQLite by the way and the rest is in my signature.

So I thought I would try indexdefs.clear, but that doesn't work either, it does not clear out the indexes.

I will do a sample SQLite example in win32 to demonstrate this behavior.

Title: Re: Sorting tsqlquery with indexes working example
Post by: snorkel on March 07, 2013, 07:32:57 pm

Well, I thought I would be clever and simply reuse the two initial indexes as they are the only ones that are in indexdefs, but that does not work either even though I change the fieldnames to the current column, it seems to always be holding on the first ones added.
It seems there is a pointer issue with indexdefs, the list is not holding the correct pointer to the actual index:

Code: [Select]

procedure TSQLForm.ResultsGridTitleClick(Column: TColumn);
var
  ASC_IndexName,DESC_IndexName:string;
  x:integer;
  aindex:TIndexDef;
begin
     ASC_IndexName:='ASC_';
     DESC_IndexName:='DESC_';
     //indexes cant sort binary types such as ftmemo, should add blob types to this as well
     if(Column.Field.DataType = ftmemo) or (Column.Field.DataType = ftwidememo)then Exit;
     //check if a Ascending index already exists for this column, if not create one
     x:=OpenQuery.IndexDefs.IndexOf(ASC_IndexName);
     if x = -1 then
        openquery.AddIndex(ASC_IndexName,column.FieldName,[]) else
        begin
             aindex:= openquery.IndexDefs.Items[x];
             aindex.Fields:=column.FieldName;
        end;
     //check if a Descending index already exists for this column, if not create one
     x:= OpenQuery.IndexDefs.IndexOf(DESC_IndexName);
     if x = -1 then
        openquery.AddIndex(DESC_IndexName,column.FieldName,[ixDescending]) else
           begin
             aindex:= openquery.IndexDefs.Items[x];
             aindex.Fields:=column.FieldName;
           end;

     //ensure index defs are up to date
     OpenQuery.IndexDefs.Updated:=false;  {<<<<---Won't work without this line}
     openquery.IndexDefs.Update;
     //use the column tag to toggle ASC/DESC
     column.tag :=  not column.tag;
     if boolean(column.tag) then
      begin
           Column.Title.ImageIndex:=12;
           openquery.IndexName:=ASC_IndexName;
      end else
          begin
               Column.Title.ImageIndex:=13;
               OpenQuery.IndexName:=DESC_IndexName;
          end;
     if (flastcolumn <> nil) and (flastcolumn <> Column) then
        flastcolumn.Title.ImageIndex:=-1;
     flastcolumn:=column;
end;

Title: Re: Sorting tsqlquery with indexes working example(Almost)
Post by: Lacak2 on March 08, 2013, 08:02:31 am

Try this:

Code: [Select]

openquery.IndexDefs.Updated:=false;
openquery.IndexDefs.Update;

(Update does nothing if Updated is true ... )

Title: Re: Sorting tsqlquery with indexes working example(Almost)
Post by: snorkel on March 08, 2013, 04:32:35 pm

Quote from: Lacak2 on March 08, 2013, 08:02:31 am

Try this:
Code: [Select]
openquery.IndexDefs.Updated:=false; openquery.IndexDefs.Update;
(Update does nothing if Updated is true ... )

That did the trick. Sorting works now with the original version I posted.

Thanks Lacak2, I just assumed that update would update it regardless.

I will update the wiki with my example.

Thanks again for helping me with this, much appreciated.

I updated the wiki for the original example by adding this line to it:

IndexDefs.Updated:=false; {<<<<---This line is critical as IndexDefs.Update will do nothing on the next sort if it's already true}

http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDataSet (http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDataSet)

Title: Re: Sorting tsqlquery with indexes working example(Almost)
Post by: snorkel on March 08, 2013, 05:36:00 pm

I added a new section to the DBgrid reference page in the wiki, check it out:

http://wiki.lazarus.freepascal.org/Grids_Reference_Page#Sorting_columns_or_rows_in_DBGrid_with_sort_arrows_in_column_header (http://wiki.lazarus.freepascal.org/Grids_Reference_Page#Sorting_columns_or_rows_in_DBGrid_with_sort_arrows_in_column_header)

Title: Re: Sorting tsqlquery with indexes working example(Almost)
Post by: tatamata on February 23, 2016, 11:02:29 pm

Quote from: snorkel on March 08, 2013, 04:32:35 pm

Quote from: Lacak2 on March 08, 2013, 08:02:31 am
Try this:
Code: [Select]
openquery.IndexDefs.Updated:=false; openquery.IndexDefs.Update;
(Update does nothing if Updated is true ... )

That did the trick. Sorting works now with the original version I posted.

Thanks Lacak2, I just assumed that update would update it regardless.

I will update the wiki with my example.

Thanks again for helping me with this, much appreciated.

I updated the wiki for the original example by adding this line to it:

IndexDefs.Updated:=false; {<<<<---This line is critical as IndexDefs.Update will do nothing on the next sort if it's already true}

http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDataSet (http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDataSet)

Hi, Snorkel, I tried to use this enhanced wiki code with showing up and down arrow (Column.Title.ImageIndex:=ImageArrowDown and Column.Title.ImageIndex:=ImageArrowUp respectively) on sorting a column, but no arrow is shown.
Can you look at this, why arrow sign is not shown in column title? Thanks.

Code: Pascal [Select][+]

function TDataModule1.SortBufDataSet(DataSet: TSQLQuery; var Column: TColumn; const FieldName: String): Boolean;
const
  ImageArrowUp=0; //should match image in imagelist
  ImageArrowDown=1; //should match image in imagelist
var
  i: Integer;
  IndexDefs: TIndexDefs;
  IndexName: String;
  IndexOptions: TIndexOptions;
  Field: TField;
begin
  Result := False;
  Field := DataSet.Fields.FindField(FieldName);
  //If invalid field name, exit.
  if Field = nil then Exit;
  //if invalid field type, exit.
  if {(Field is TObjectField) or} (Field is TBlobField) or
    {(Field is TAggregateField) or} (Field is TVariantField)
     or (Field is TBinaryField) then Exit;
  //Get IndexDefs and IndexName using RTTI
  if IsPublishedProp(DataSet, 'IndexDefs') then
    IndexDefs := GetObjectProp(DataSet, 'IndexDefs') as TIndexDefs
  else
    Exit;
  if IsPublishedProp(DataSet, 'IndexName') then
    IndexName := GetStrProp(DataSet, 'IndexName')
  else
    Exit;
  //Ensure IndexDefs is up-to-date
  IndexDefs.Updated:=false; {<<<<---This line is critical as IndexDefs.Update will do nothing on the next sort if it's already true}
  IndexDefs.Update;
  //If an ascending index is already in use,
  //switch to a descending index
  if IndexName = FieldName + '__IdxA'
  then
    begin
      IndexName := FieldName + '__IdxD';
      IndexOptions := [ixDescending];
      Column.Title.ImageIndex:=ImageArrowDown;
    end
  else
    begin
      IndexName := FieldName + '__IdxA';
      IndexOptions := [];
      Column.Title.ImageIndex:=ImageArrowUp;
    end;
  //Look for existing index
  for i := 0 to Pred(IndexDefs.Count) do
  begin
    if IndexDefs[i].Name = IndexName then
      begin
        Result := True;
        Break
      end;  //if
  end; // for
  //If existing index not found, create one
  if not Result then
      begin
        if IndexName=FieldName + '__IdxD' then
          DataSet.AddIndex(IndexName, FieldName, IndexOptions, FieldName)
        else
          DataSet.AddIndex(IndexName, FieldName, IndexOptions);
        Result := True;
      end; // if not
  //Set the index
  SetStrProp(DataSet, 'IndexName', IndexName);
end;                                         
 

Title: Re: Sorting tsqlquery with indexes working example(Yes it works now)
Post by: Ericktux on April 09, 2016, 03:52:07 am

Thanks :) this is my code:

Code: Pascal [Select][+]

var
  Form2: TForm2;
  FLastColumn: TColumn; //store last grid column we sorted on  
.....
 
procedure TForm2.DBGrid1TitleClick(Column: TColumn);
const
  ImageArrowUp=0; //should match image in imagelist
  ImageArrowDown=1; //should match image in imagelist
var
  ASC_IndexName, DESC_IndexName:string;
  procedure UpdateIndexes;
  begin
    // Ensure index defs are up to date
    SQLQuery1.IndexDefs.Updated:=false; {<<<--This line is critical. IndexDefs.Update will not
    update if already true, which will happen on the first column sorted.}
    SQLQuery1.IndexDefs.Update;
  end;
begin
  ASC_IndexName:='ASC_'+Column.FieldName;
  DESC_IndexName:='DESC_'+Column.FieldName;
  // indexes can't sort binary types such as ftMemo, ftBLOB
  if (Column.Field.DataType in [ftBLOB,ftMemo,ftWideMemo]) then
    exit;
  // check if an ascending index already exists for this column.
  // if not, create one
  if SQLQuery1.IndexDefs.IndexOf(ASC_IndexName) = -1 then
  begin
    SQLQuery1.AddIndex(ASC_IndexName,column.FieldName,[]);
    UpdateIndexes; //ensure index defs are up to date
  end;
  // Check if a descending index already exists for this column
  // if not, create one
  if SQLQuery1.IndexDefs.IndexOf(DESC_IndexName) = -1 then
  begin
    SQLQuery1.AddIndex(DESC_IndexName,column.FieldName,[ixDescending]);
    UpdateIndexes; //ensure index defs are up to date
  end;
 
  // Use the column tag to toggle ASC/DESC
  column.tag := not column.tag;
  if boolean(column.tag) then
  begin
    Column.Title.ImageIndex:=ImageArrowUp;
    SQLQuery1.IndexName:=ASC_IndexName;
  end
  else
  begin
    Column.Title.ImageIndex:=ImageArrowDown;
    SQLQuery1.IndexName:=DESC_IndexName;
  end;
  // Remove the sort arrow from the previous column we sorted
  if (FLastColumn <> nil) and (FlastColumn <> Column) then
    FLastColumn.Title.ImageIndex:=-1;
  FLastColumn:=column;
end;     

And in the Event Create:

Code: Pascal [Select][+]

procedure TForm2.FormCreate(Sender: TObject);
begin
  SQLQuery1.MaxIndexesCount:=100; // para poder ordenar las columnas   
  //  ......   your code
end;

also we need a imagelist1 and put two 16x16 images, then link inside properties DBgrid "Titleimagelist=imagelist"

Greetings from Peru :) :) :)

Title: Re: Sorting tsqlquery with indexes working example(Yes it works now)
Post by: tatamata on April 09, 2016, 07:35:27 am

Pascalito, can you attach these two images of arrows up and down? Thnx.

Title: Re: Sorting tsqlquery with indexes working example(Yes it works now)
Post by: Ericktux on April 12, 2016, 04:31:38 pm

of course my friend :)
if you want more icons you can get them in "IconFinder"

Title: Re: Sorting tsqlquery with indexes working example(Yes it works now)
Post by: Vodnik on November 16, 2020, 08:46:08 pm

I have used just this code in my application, compiled with Lazarus 2.0.4 for Windows.
It was nice that when column callid is once sorted, newly loaded data becomes sorted, too.
It work fine for a couple of years, until I have upgraded Lazarus to 2.0.10.
Now it works the very first time only, attempt to load new data causes exception
DBQuery1: Index 'ASC_callid' not found.
I didn't investigate how the indexes work. Maybe there is quite a simple idea how to bring this code back to life?

Title: Re: Sorting tsqlquery with indexes working example(Yes it works now)
Post by: antoniocangiano76 on October 14, 2021, 09:18:15 pm

Quote

I have used just this code in my application, compiled with Lazarus 2.0.4 for Windows.
It was nice that when column callid is once sorted, newly loaded data becomes sorted, too.
It work fine for a couple of years, until I have upgraded Lazarus to 2.0.10.
Now it works the very first time only, attempt to load new data causes exception
DBQuery1: Index 'ASC_callid' not found.
I didn't investigate how the indexes work. Maybe there is quite a simple idea how to bring this code back to life?

Lazarus 2.0.12 FPC 3.2.0 Win10 21H1 64bit
Same problem...Here is my steps
1) I set index on first column (OK)
2) Then set a filter on query q1 with a Tedit with this code :

Code: Pascal [Select][+]

procedure Tf_param.te_filtroChange(Sender: TObject);
begin
   if Trim(te_filtro.Text) <> '' then
  begin
    if not q1.Filtered then q1.Filtered := True;
    q1.Refresh;
  end else
    q1.Filtered := False;
end;      

and ...

Code: Pascal [Select][+]

procedure Tf_param.q1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
var
  i,k: integer;
begin
  for i := 0 to dbg1.Columns.Count -1 do begin
    if dbg1.Columns[i].Visible then begin;
       Accept := Pos( iif( togglebox1.checked,UpperCase(te_filtro.Text),te_filtro.Text ),
                      iif( togglebox1.checked,UpperCase(dbg1.Columns[i].Field.AsString),
                      dbg1.Columns[i].Field.AsString) ) > 0;
       if Accept then exit;
    end;
  end;
  //   if Accept = False then Accept := Pos(UpperCase(te_filtro.Text), UpperCase(DataSet.FieldByName('DESC2').AsString)) > 0;
end;  

3) When I insert first char in Tedit, I got "q1 _ Index 'ASC_DESC' not found
4) Error occours in Tedit onchange at

Code: Pascal [Select][+]

q1.Refresh; 

Any ideas?
Thanks

Title: Re: Sorting tsqlquery with indexes working example(Yes it works now)
Post by: wp on October 14, 2021, 11:28:49 pm

Your description does not provide enough information. What kind of query is q1? The title of the thread mentions sqlquery, but I cannot believe this because then you'd filter the query result by the sql clause, not by the OnFilterRecord event.

Ideally you should provide a simple small compilable project which shows the error. Remove everything from your project which is not related to this issue and check that the bug still exists, then upload the .pas, .lfm, .lpi, .lpr and database file, all packed into a shared zip.