Problem/Possible bug with TSQLQuery and sorting (example attached)
lazarus / 16-Databases

----------------------------------------------------------------------
snorkel · 2013-03-20 14:36

I am going to explain what is happening and then I will post a sample
project with a sqlite database to demonstrate.

Basically I have a simple query editor for SQLite with sorting enabled
in the dbgrid using the ontitleclick event, this works until you sort
a column
and then change the SQL to another table and run a query against that
new table, what happens is it raises a error stating that field xxxxx
is not not found, and this only happens if you sort the prior result
set returned.

I tried clearing the indexdefs like this before the new SQL is opened:

Code: [Select]aform.OpenQuery.IndexName:='';
aform.OpenQuery.IndexFieldNames:='';
aform.OpenQuery.IndexDefs.Updated:=false;
aform.OpenQuery.IndexDefs.Clear;
aform.OpenQuery.IndexDefs.Update;
I am doing the sorting like in the example here:
http://wiki.freepascal.org/Grids_Reference_Page#Sorting_columns_or_row
s_in_DBGrid_with_sort_arrows_in_column_header

----------------------------------------------------------------------
snorkel · 2013-03-20 15:51

Ok, attached is a sample project that demonstrates the issue I am
having.
if you don't sort between query opens it works fine, only after a sort
is performed does it raise the error.
I also have it showing a message box that all indexes are cleared.
NOTE: I could be doing something wrong here and that is why I asked
before posting a bug report.

You will need to provide your own copy of SQLite3.dll on windows as I
could not attach the zip with the dll present as it made it to big.

The sample sqlite database is in the zip and is called dds.db.

Open the example db
open query1
sort on last_name or first_name by clicking the column header, you
will see sort arrows.
attempt to open query2, it will raise a error that the last field you
sorted cannot be found, which it can't because it's a new
result set.

Before the queries are opened I clear the indexdefs and I also clear
the indexname property.  I know I could work around this by simply
creating a new tsqlquery each time, but you should not have to do
that.

----------------------------------------------------------------------
snorkel · 2013-03-20 17:17

I think the problem is the indexes simply cannot be cleared.
If I change the code to this in the example:

SQLQuery1.IndexDefs.Clear;
SQLQuery1.IndexDefs.Updated:=false;
SQLQuery1.IndexDefs.Update;
 showmessage(format('There are %d
indexes',[SQLQuery1.IndexDefs.Count]));

the indexes simply reappear in index defs and even if indexdefs.count
is 0 they are still there.

This has to be a bug. Can anyone confirm this yet by the example?

----------------------------------------------------------------------
Lacak2 · 2013-03-21 08:50

Quote from: snorkel on March 20, 2013, 06:17:59 pmI think the problem
is the indexes simply cannot be cleared.
Yes you are right.

You can work around putting:
Code: [Select]        SQLQuery1.UniDirectional:=true;
        SQLQuery1.UniDirectional:=false;    after:
Code: [Select]SQLQuery1.Close;
This will clear indexes.
But it will be good to have better solution ... may be method:
DeleteIndex (like AddIndex) ?

----------------------------------------------------------------------
snorkel · 2013-03-21 14:41

I noticed that the fielddefs has a clearfields method, how about a
clearindexes and a deleteindex, that way you could remove them all in
one operation or one at a time.

Should I post a bug report on this?

I updated the wiki grids reference page with this info for the sorting
example:
http://wiki.lazarus.freepascal.org/Grids_Reference_Page#Sorting_column
s_or_rows_in_DBGrid_with_sort_arrows_in_column_header

----------------------------------------------------------------------
snorkel · 2013-03-21 16:33

I added a procedure to tCustomBufDataset:

Code: [Select]procedure TCustomBufDataset.ClearIndexes;
var
    i:integer;
begin
     For I:=0 to Length(FIndexes)-1 do
         FreeAndNil(Findexes[I]);
     SetLength(FIndexes,0);
     FIndexesCount:=0;
end;

Seems rebuliding the FCL is not that easy on win32, so I just added
all the paths to my project in the other unit files, it then brought
in the source for the FCL-DB and was able to test, and it works fine.

----------------------------------------------------------------------
snorkel · 2013-03-21 17:09

setting the Unidirectional to true and then to false, does indeed
clear the indexes and gets rid of the error, however after looking at
the SetBufUniDirectional it appears there is a memory leak in it as it
simply uses setlength to 0 out the array, however it does not free the
actual indexes.

Shouldn't it be doing:

Code: [Select]  For I:=0 to Length(FIndexes)-1 do
    FreeAndNil(Findexes[I]);
  SetLength(FIndexes,0);

----------------------------------------------------------------------
Lacak2 · 2013-03-22 09:20

Quote from: snorkel on March 21, 2013, 06:09:43 pmsetting the
Unidirectional to true and then to false, does indeed clear the
indexes and gets rid of the error, however after looking at the
SetBufUniDirectional it appears there is a memory leak in it as it
simply uses setlength to 0 out the array, however it does not free the
actual indexes.

Shouldn't it be doing:

Code: [Select]  For I:=0 to Length(FIndexes)-1 do
    FreeAndNil(Findexes[I]);
  SetLength(FIndexes,0);    Yes it seems so

----------------------------------------------------------------------
Lacak2 · 2013-03-22 09:31

Quote from: snorkel on March 21, 2013, 05:33:19 pmI added a procedure
to tCustomBufDataset:

Code: [Select]procedure TCustomBufDataset.ClearIndexes;
var
    i:integer;
begin
     For I:=0 to Length(FIndexes)-1 do
         FreeAndNil(Findexes[I]);
     SetLength(FIndexes,0);
     FIndexesCount:=0;
end;
 You must at least check if dataset is inactive: CheckInactive;
before you allow deleting of indexes.
But question is what is the best method how to introduce this
functionality...

----------------------------------------------------------------------
snorkel · 2013-03-22 14:35

This works well(i tested it)
Seems to work fine in TCustomBufDataset and adding it would not break
any existing
code.

Code: [Select]procedure TCustomBufDataset.ClearIndexes;
var
    i:integer;
begin
     CheckInactive;
     For I:=0 to Length(FIndexes)-1 do
         FreeAndNil(Findexes[I]);
     SetLength(FIndexes,0);
     FIndexesCount:=0;
end;

----------------------------------------------------------------------
AEslabon4 · 2013-09-11 17:14

Hello Snorkel!

I too have the same problem and I try to add your code "ClearIndexes"
to TCustomBufDataset.

When I type: "SQLQuery1." the list shows the ClearIndexes clause. But
when I compile the Project I receive the message:

Error: identifier idents no member "ClearIndexes"

What I'm doing wrong?

I'm working with Mandriva Linux 2010.2, Lazarus: 1.0.10 and FPC: 2.6.2

I'll thank You for the help.

----------------------------------------------------------------------
Vodnik · 2020-11-18 15:41

I ran into the same problem. Testing the provided by snorkel
sortbug_example project under Lazarus 2.0.10 I got error even when
opening the same query without sorting.

----------------------------------------------------------------------
egsuh · 2020-11-19 03:55

I tried your example, and just putting "close" before setting
indexname solved the problem. See my comment below. I did not tested
any further so please check yourself.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.open1Click(Sender: TObject);begin     if not
SQLite3Connection1.Connected then       exit;     try       
SQLQuery1.Close;             // <-- Put this here.        
SQLQuery1.IndexName:='';        SQLQuery1.IndexDefs.Clear;       
showmessage(format('There are %d
indexes',[SQLQuery1.IndexDefs.Count]));        SQLQuery1.SQL.Clear;  
    if sender = open1 then          begin         
 SQLQuery1.SQL.Add(Q1Edit.Text);           open2.Enabled:=true;      
  end else        if sender = open2 then         
 SQLQuery1.SQL.Add(Q2Edit.Text);        SQLQuery1.Open;     except   
  on e:EDatabaseError do         MessageDlg('DB
ERROR',e.message,mtError,[mbok],0);       on e:exception do         
MessageDlg('ERROR',e.message,mtError,[mbok],0);     end;end;

----------------------------------------------------------------------
Vodnik · 2020-11-24 13:34

Thanks, egsuh!
As I understand, indexes should be cleared on closed dataset only.
With your correction, the example works fine. No problems caused by
sorting, mentioned in the initial post.