Recent

Author Topic: Problem/Possible bug with TSQLQuery and sorting (example attached)  (Read 8216 times)

snorkel

  • Hero Member
  • *****
  • Posts: 817
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_rows_in_DBGrid_with_sort_arrows_in_column_header



 
« Last Edit: March 20, 2013, 04:52:37 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 3.0RC2 and FPC 3.2.2
OS: Windows 10 64 bit

snorkel

  • Hero Member
  • *****
  • Posts: 817
Re: Problem/Possible bug with TSQLQuery and sorting
« Reply #1 on: March 20, 2013, 04:51:22 pm »
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***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 3.0RC2 and FPC 3.2.2
OS: Windows 10 64 bit

snorkel

  • Hero Member
  • *****
  • Posts: 817
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #2 on: March 20, 2013, 06:17:59 pm »
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?

***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 3.0RC2 and FPC 3.2.2
OS: Windows 10 64 bit

Lacak2

  • Guest
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #3 on: March 21, 2013, 09:50:21 am »
I 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

  • Hero Member
  • *****
  • Posts: 817
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #4 on: March 21, 2013, 03:41:21 pm »
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_columns_or_rows_in_DBGrid_with_sort_arrows_in_column_header
« Last Edit: March 21, 2013, 04:08:28 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 3.0RC2 and FPC 3.2.2
OS: Windows 10 64 bit

snorkel

  • Hero Member
  • *****
  • Posts: 817
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #5 on: March 21, 2013, 05:33:19 pm »
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.
« Last Edit: March 21, 2013, 06:30:01 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 3.0RC2 and FPC 3.2.2
OS: Windows 10 64 bit

snorkel

  • Hero Member
  • *****
  • Posts: 817
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #6 on: March 21, 2013, 06:09:43 pm »
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);   
***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 3.0RC2 and FPC 3.2.2
OS: Windows 10 64 bit

Lacak2

  • Guest
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #7 on: March 22, 2013, 10:20:00 am »
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);   
Yes it seems so

Lacak2

  • Guest
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #8 on: March 22, 2013, 10:31:14 am »
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;
 
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

  • Hero Member
  • *****
  • Posts: 817
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #9 on: March 22, 2013, 03:35:34 pm »
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; 
« Last Edit: March 22, 2013, 03:47:31 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 3.0RC2 and FPC 3.2.2
OS: Windows 10 64 bit

AEslabon4

  • New Member
  • *
  • Posts: 25
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #10 on: September 11, 2013, 07:14:44 pm »
Hello Snorkel! :D

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? :o

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

I'll thank You for the help.  :-[
The World is my Country, Science my Religion.
Christiaan Huygens

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #11 on: November 18, 2020, 04:41:46 pm »
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

  • Hero Member
  • *****
  • Posts: 1273
Re: Problem/Possible bug with TSQLQuery and sorting (example attached)
« Reply #12 on: November 19, 2020, 04:55:53 am »
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][+][-]
  1. procedure TForm1.open1Click(Sender: TObject);
  2. begin
  3.      if not SQLite3Connection1.Connected then
  4.        exit;
  5.      try
  6.         SQLQuery1.Close;             // <-- Put this here.
  7.         SQLQuery1.IndexName:='';
  8.         SQLQuery1.IndexDefs.Clear;
  9.         showmessage(format('There are %d indexes',[SQLQuery1.IndexDefs.Count]));
  10.         SQLQuery1.SQL.Clear;
  11.         if sender = open1 then
  12.           begin
  13.            SQLQuery1.SQL.Add(Q1Edit.Text);
  14.            open2.Enabled:=true;
  15.           end else
  16.         if sender = open2 then
  17.            SQLQuery1.SQL.Add(Q2Edit.Text);
  18.         SQLQuery1.Open;
  19.      except
  20.        on e:EDatabaseError do
  21.          MessageDlg('DB ERROR',e.message,mtError,[mbok],0);
  22.        on e:exception do
  23.           MessageDlg('ERROR',e.message,mtError,[mbok],0);
  24.      end;
  25. end;              

Vodnik

  • Full Member
  • ***
  • Posts: 210
Re: Problem/Possible bug with TSQLQuery and sorting (example attached) [SOLVED]
« Reply #13 on: November 24, 2020, 02:34:43 pm »
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.

 

TinyPortal © 2005-2018