View Issue Details

IDProjectCategoryView StatusLast Update
0003468JEDI VCL00 JVCL Componentspublic2007-06-19 08:34
ReportertmstaedtAssigned ToInformatix 
PrioritynormalSeverityfeatureReproducibilityalways
Status resolvedResolutionwon't fix 
Product Version3.10 
Target VersionFixed in Version 
Summary0003468: JvDBUltimGrid does not sort columns when DataSet is TQuery - workaround, solution
DescriptionDid a workaround for above problem by setting the OnUserSort variable in my
FormCreate method like
the following:
      if not Assigned(UltimGrid.OnUserSort) then
       UltimGrid.OnUserSort := JvDBUltimGrid1UserSort;


Additional Informationand the method JvDBUltimGrid1UserSort:

procedure TFormBase.JvDBUltimGrid1UserSort(Sender: TJvDBUltimGrid;
  var FieldsToSort: TSortFields; SortString: String; var SortOK: Boolean);

const
  JvGridSort_ASC = True;
  JvGridSort_UP = True;
  JvGridSort_DESC = False;
  JvGridSort_DOWN = False;
var
  aFieldName: string;
  aSQL: string;
  SortSQL: string;
  OrigSQL: string;
  OrderByStr: string;
  DSet : TDataSet;
 SortQuery : TQuery;

  procedure BuildSQL;
  var
   FTS, MaxFTS: integer;
  begin
   SortSQL := aSQL;
    MaxFTS := Length(FieldsToSort) - 1;
    for FTS := 0 to MaxFTS do
    begin
      if FTS = 0 then
        OrderByStr := 'ORDER BY '
      else
        OrderByStr := ', ';

      aFieldName := FieldsToSort[FTS].Name;
      if FieldsToSort[FTS].Order = JvGridSort_ASC then
        SortSQL := SortSQL + OrderByStr + aFieldName
      else
        SortSQL := SortSQL + OrderByStr + aFieldName + ' DESC';
    end;

    SortQuery.SQL.Text := SortSQL;
    SortQuery.Open;
  end;

begin
 DSet := Sender.DataSource.DataSet;
  if DSet is TQuery then
   SortQuery := TQuery(DSet);

  if Assigned(SortQuery) and (SortQuery.Active = True) then
    begin
      SortQuery.DisableControls;
      aFieldName := FieldsToSort[0].Name;
      aSQL := SortQuery.SQL.Text;
      OrigSQL := SortQuery.SQL.Text;
      SortQuery.Close;
      try
        if Pos('ORDER BY', UpperCase(aSQL)) > 0 then
          Delete(aSQL, Pos('ORDER BY', UpperCase(aSQL)), Length(aSQL) -
Pos('ORDER BY', UpperCase(aSQL)));
        try
          SortSQL := '';
          BuildSQL;

        except
          SortSQL := '';
     BuildSQL;
        end;
      except
        SortQuery.SQL.Text := OrigSQL;
        SortQuery.Open;
      end;
      SortOK := true;
      SortQuery.EnableControls;
    end;

end;


Regards,
TagsNo tags attached.

Activities

jfudickar

2006-01-26 00:40

developer   ~0008457

This should not be added to the grid directly.

If you do this, the bde units are included to each project using the grid.

This could be included to an different unit, maybe JvDBUltimGridSortBDE or so.


Any other comments ?

Informatix

2006-01-26 06:55

developer   ~0008461

As the unit header says: set SortWith=swUserFunc if you want to use your own sorting function and assign OnUserSort (mandatory). I don't understand why you're talking of a workaround. A workaround for what ?
Your procedure BuildSQL seems to provide the same thing as "ORDER BY " + the SortString value so it is rather useless. And your replacement function for ORDER BY doesn't suit my needs (I have usually another SQL order after the ORDER BY clause) BUT it's not a bad idea to add a function to do this kind of thing. It just needs to be rewritten to use the existing code and have a larger scope.
I agree with Jens: don't make any reference to the BDE objects. Just do a function to replace the ORDER BY clause in a string. That will be useful enough.

Fred

obones

2006-04-04 09:15

administrator   ~0008912

Anything more to be done on this?

tmstaedt

2006-04-04 09:23

reporter   ~0008914

I don't know? Should a UserSort method be available in the grid or not? That's up to the designers of that component. We use the above code currently, and it works for us!

Informatix

2006-04-24 03:44

developer   ~0009176

As I said, it's a good idea to write a function to change the ORDER BY clause, but your current code has some flaws: it doesn't use the existing code, it is restricted to BDE and it presumes ORDER BY is the last clause (any LIMIT clause is deleted).

Informatix

2007-03-01 04:31

developer   ~0011245

Any news ?

ZENsan

2007-03-01 08:05

reporter   ~0011246

I think that must NOT be implemented by Jedi!!
tmstaedt can't define one function:
ChangeQueryOrder(QQ: TQuery; Grid: TDBGrid;Column: TColumn);{there you can check if Column.Field.Type = fkCalculated.. and so on}

and tyhen in this procedure developer can simply do what he needs - and then in every grid simply assign OnTitleClick method which calls this procedure. But not in the component!!! Which will change sql! If there is UNION... or something another..

It is not acceptable I think! Do not do that obones! :)

Informatix

2007-03-06 02:55

developer   ~0011287

I agree.

obones

2007-06-19 08:33

administrator   ~0013462

Ok then, no change will be done.

Issue History

Date Modified Username Field Change
2006-01-25 09:01 tmstaedt New Issue
2006-01-26 00:40 jfudickar Note Added: 0008457
2006-01-26 06:55 Informatix Note Added: 0008461
2006-04-04 09:15 obones Note Added: 0008912
2006-04-04 09:15 obones Status new => feedback
2006-04-04 09:23 tmstaedt Note Added: 0008914
2006-04-24 03:44 Informatix Note Added: 0009176
2006-06-27 06:19 obones Status feedback => assigned
2006-06-27 06:19 obones Assigned To => Informatix
2007-03-01 04:31 Informatix Note Added: 0011245
2007-03-01 08:05 ZENsan Note Added: 0011246
2007-03-06 02:55 Informatix Note Added: 0011287
2007-06-19 08:33 obones Status assigned => resolved
2007-06-19 08:33 obones Resolution open => won't fix
2007-06-19 08:33 obones Note Added: 0013462