Recent

Author Topic: Sorting tsqlquery with indexes working example(Yes it works now)  (Read 15791 times)

snorkel

  • Hero Member
  • *****
  • Posts: 810
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.
« Last Edit: March 08, 2013, 05:36:19 pm by snorkel »
***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 2.06 and FPC 3.0.4
OS: Windows 10 64 bit

snorkel

  • Hero Member
  • *****
  • Posts: 810
Re: Trying to sort a tsqlquery with indexes not work right
« Reply #1 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
« Last Edit: March 08, 2013, 04:34:55 pm by snorkel »
***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 2.06 and FPC 3.0.4
OS: Windows 10 64 bit

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Sorting tsqlquery with indexes working example
« Reply #2 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.

snorkel

  • Hero Member
  • *****
  • Posts: 810
Re: Sorting tsqlquery with indexes working example
« Reply #3 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?
***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 2.06 and FPC 3.0.4
OS: Windows 10 64 bit

Lacak2

  • Guest
Re: Sorting tsqlquery with indexes working example
« Reply #4 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 ?

snorkel

  • Hero Member
  • *****
  • Posts: 810
Re: Sorting tsqlquery with indexes working example
« Reply #5 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.
***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 2.06 and FPC 3.0.4
OS: Windows 10 64 bit

snorkel

  • Hero Member
  • *****
  • Posts: 810
Re: Sorting tsqlquery with indexes working example
« Reply #6 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;
« Last Edit: March 08, 2013, 04:35:54 pm by snorkel »
***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 2.06 and FPC 3.0.4
OS: Windows 10 64 bit

Lacak2

  • Guest
Re: Sorting tsqlquery with indexes working example(Almost)
« Reply #7 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 ... )

snorkel

  • Hero Member
  • *****
  • Posts: 810
Re: Sorting tsqlquery with indexes working example(Almost)
« Reply #8 on: March 08, 2013, 04:32:35 pm »
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
« Last Edit: March 08, 2013, 04:48:52 pm by snorkel »
***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 2.06 and FPC 3.0.4
OS: Windows 10 64 bit

snorkel

  • Hero Member
  • *****
  • Posts: 810
Re: Sorting tsqlquery with indexes working example(Almost)
« Reply #9 on: March 08, 2013, 05:36:00 pm »
***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 2.06 and FPC 3.0.4
OS: Windows 10 64 bit

tatamata

  • Hero Member
  • *****
  • Posts: 758
    • ZMSQL - SQL enhanced in-memory database
Re: Sorting tsqlquery with indexes working example(Almost)
« Reply #10 on: February 23, 2016, 11:02:29 pm »
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
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][+][-]
  1. function TDataModule1.SortBufDataSet(DataSet: TSQLQuery; var Column: TColumn; const FieldName: String): Boolean;
  2. const
  3.   ImageArrowUp=0; //should match image in imagelist
  4.   ImageArrowDown=1; //should match image in imagelist
  5. var
  6.   i: Integer;
  7.   IndexDefs: TIndexDefs;
  8.   IndexName: String;
  9.   IndexOptions: TIndexOptions;
  10.   Field: TField;
  11. begin
  12.   Result := False;
  13.   Field := DataSet.Fields.FindField(FieldName);
  14.   //If invalid field name, exit.
  15.   if Field = nil then Exit;
  16.   //if invalid field type, exit.
  17.   if {(Field is TObjectField) or} (Field is TBlobField) or
  18.     {(Field is TAggregateField) or} (Field is TVariantField)
  19.      or (Field is TBinaryField) then Exit;
  20.   //Get IndexDefs and IndexName using RTTI
  21.   if IsPublishedProp(DataSet, 'IndexDefs') then
  22.     IndexDefs := GetObjectProp(DataSet, 'IndexDefs') as TIndexDefs
  23.   else
  24.     Exit;
  25.   if IsPublishedProp(DataSet, 'IndexName') then
  26.     IndexName := GetStrProp(DataSet, 'IndexName')
  27.   else
  28.     Exit;
  29.   //Ensure IndexDefs is up-to-date
  30.   IndexDefs.Updated:=false; {<<<<---This line is critical as IndexDefs.Update will do nothing on the next sort if it's already true}
  31.   IndexDefs.Update;
  32.   //If an ascending index is already in use,
  33.   //switch to a descending index
  34.   if IndexName = FieldName + '__IdxA'
  35.   then
  36.     begin
  37.       IndexName := FieldName + '__IdxD';
  38.       IndexOptions := [ixDescending];
  39.       Column.Title.ImageIndex:=ImageArrowDown;
  40.     end
  41.   else
  42.     begin
  43.       IndexName := FieldName + '__IdxA';
  44.       IndexOptions := [];
  45.       Column.Title.ImageIndex:=ImageArrowUp;
  46.     end;
  47.   //Look for existing index
  48.   for i := 0 to Pred(IndexDefs.Count) do
  49.   begin
  50.     if IndexDefs[i].Name = IndexName then
  51.       begin
  52.         Result := True;
  53.         Break
  54.       end;  //if
  55.   end; // for
  56.   //If existing index not found, create one
  57.   if not Result then
  58.       begin
  59.         if IndexName=FieldName + '__IdxD' then
  60.           DataSet.AddIndex(IndexName, FieldName, IndexOptions, FieldName)
  61.         else
  62.           DataSet.AddIndex(IndexName, FieldName, IndexOptions);
  63.         Result := True;
  64.       end; // if not
  65.   //Set the index
  66.   SetStrProp(DataSet, 'IndexName', IndexName);
  67. end;                                        
  68.  
« Last Edit: February 23, 2016, 11:38:30 pm by tatamata »

Ericktux

  • Sr. Member
  • ****
  • Posts: 257
Re: Sorting tsqlquery with indexes working example(Yes it works now)
« Reply #11 on: April 09, 2016, 03:52:07 am »
Thanks  :) this is my code:

Code: Pascal  [Select][+][-]
  1. var
  2.   Form2: TForm2;
  3.   FLastColumn: TColumn; //store last grid column we sorted on  
  4. .....
  5.  
  6. procedure TForm2.DBGrid1TitleClick(Column: TColumn);
  7. const
  8.   ImageArrowUp=0; //should match image in imagelist
  9.   ImageArrowDown=1; //should match image in imagelist
  10. var
  11.   ASC_IndexName, DESC_IndexName:string;
  12.   procedure UpdateIndexes;
  13.   begin
  14.     // Ensure index defs are up to date
  15.     SQLQuery1.IndexDefs.Updated:=false; {<<<--This line is critical. IndexDefs.Update will not
  16.     update if already true, which will happen on the first column sorted.}
  17.     SQLQuery1.IndexDefs.Update;
  18.   end;
  19. begin
  20.   ASC_IndexName:='ASC_'+Column.FieldName;
  21.   DESC_IndexName:='DESC_'+Column.FieldName;
  22.   // indexes can't sort binary types such as ftMemo, ftBLOB
  23.   if (Column.Field.DataType in [ftBLOB,ftMemo,ftWideMemo]) then
  24.     exit;
  25.   // check if an ascending index already exists for this column.
  26.   // if not, create one
  27.   if SQLQuery1.IndexDefs.IndexOf(ASC_IndexName) = -1 then
  28.   begin
  29.     SQLQuery1.AddIndex(ASC_IndexName,column.FieldName,[]);
  30.     UpdateIndexes; //ensure index defs are up to date
  31.   end;
  32.   // Check if a descending index already exists for this column
  33.   // if not, create one
  34.   if SQLQuery1.IndexDefs.IndexOf(DESC_IndexName) = -1 then
  35.   begin
  36.     SQLQuery1.AddIndex(DESC_IndexName,column.FieldName,[ixDescending]);
  37.     UpdateIndexes; //ensure index defs are up to date
  38.   end;
  39.  
  40.   // Use the column tag to toggle ASC/DESC
  41.   column.tag := not column.tag;
  42.   if boolean(column.tag) then
  43.   begin
  44.     Column.Title.ImageIndex:=ImageArrowUp;
  45.     SQLQuery1.IndexName:=ASC_IndexName;
  46.   end
  47.   else
  48.   begin
  49.     Column.Title.ImageIndex:=ImageArrowDown;
  50.     SQLQuery1.IndexName:=DESC_IndexName;
  51.   end;
  52.   // Remove the sort arrow from the previous column we sorted
  53.   if (FLastColumn <> nil) and (FlastColumn <> Column) then
  54.     FLastColumn.Title.ImageIndex:=-1;
  55.   FLastColumn:=column;
  56. end;    
           

And in the Event Create:

Code: Pascal  [Select][+][-]
  1. procedure TForm2.FormCreate(Sender: TObject);
  2. begin
  3.   SQLQuery1.MaxIndexesCount:=100; // para poder ordenar las columnas  
  4.   //  ......   your code
  5. end;

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

Greetings from Peru  :) :) :)





tatamata

  • Hero Member
  • *****
  • Posts: 758
    • ZMSQL - SQL enhanced in-memory database
Re: Sorting tsqlquery with indexes working example(Yes it works now)
« Reply #12 on: April 09, 2016, 07:35:27 am »
Pascalito, can you attach these two images of arrows up and down? Thnx.

Ericktux

  • Sr. Member
  • ****
  • Posts: 257
Re: Sorting tsqlquery with indexes working example(Yes it works now)
« Reply #13 on: April 12, 2016, 04:31:38 pm »

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

Vodnik

  • Full Member
  • ***
  • Posts: 184
Re: Sorting tsqlquery with indexes working example(Yes it works now)
« Reply #14 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?

 

TinyPortal © 2005-2018