sorting an OracleDataSet

[vkupisk]

I want to be able to click on a column heading in my DBGrid that displays the OracleDataSet and reorder the dataset by the corresponding field.

Is there a way to sort without rerunning the Query with another ORDER BY?

Thanks,
Vitaly

 

[Marco Kalter]

This is only possible if your DBGrid supports it. The records in the dataset cannot be sorted, but the grid control may be able to change their display order (e.g. TopGrid).

------------------
Marco Kalter
Allround Automations

 

[vkupisk]

Marko, I downloaded TopGrid's Eval. copy and in their example (sorting.dpr) that's exactly what they do, re-execute the query with the new sort order. I think the only way to do it is with a ClientDataSet, but I haven't been able to get all the field types just right so that DataSetProvider could take the data from your OracleDataSet. Thanks for the TopGrid tip though, it does seem like a good product.

QuantumGrid claims to do it, too, but they only have demos for download.

Vitaly

 

[vkupisk]

whoops, marco, sorry for misspelling your name....

 

[F.Hinlopen]

The Quantumgrid can sort (even multisort) by setting properties. There are minor restrictions, most notably that the underlying dataset have a unique field (exactly one).
Given the nature of the restrictions, I think it is highly likely that the underlying query is not being modified and that the sorting is taking place on the client side. Further proof that I can offer is that you are able to set your own compare function.

Greetings,
Frans

 

[vkupisk]

I looked at QuantumGrid's website (actually before TopGrid's) and downloaded their eval version but it turned out to be just a bunch of demos. So I lost heart and didn't look at it again.

 

[smouge]

I use the following code in woll2woll 's infopower, I just made it, so still not optimized or fully tested. If someone has a bettter method, pls let me know:

procedure TLogForm.wwDBGrid1TitleButtonClick(Sender: TObject;
AFieldName: string);
begin
DOA_Sort(DOA_LogView, AFieldName);
end;

procedure DOA_Sort(DOA_Table : TOracleDataSet; AFieldname : string);
var
SQLText: string;
i: integer;
desc: string;
tmp1: string;
begin
SQLText := uppercase(DOA_Table.sql.Text);
i := pos('ORDER BY', SQLText);
if (i 0) then begin
tmp1 := copy(SQLTExt, i, length(SQLText) - i);
Desc := '';
if pos(AFieldname, tmp1) 0 then begin
if pos('DESC', tmp1) = 0 then begin
Desc := ' DESC ';
end;
end;
SQLText := copy(SQLTExt, 0, (i - 1)) + 'ORDER BY ' + AFieldname + Desc;
end
else begin
SQLText := SQLTExt + ' ORDER BY ' + AFieldname;
end;
DOA_Table.sql.Clear;
DOA_Table.sql.add(SQLText);
DOA_Table.refresh;
end;

 

[vkupisk]

I am doing a similar thing but if my program becomes a long-term production tool (not planned now, but you never know) re-firing the query will not be acceptable to our Oracle guardians. I'll try to do it with client data set. Has anyone tried using a client dataset with DOA components?

Vitaly

 

[F.Hinlopen]

Then they're not going to like TopGrid either... What daily ration of Oracle queries do your Oracle police allow? This solution is not like you're filling up the SGA with literal queries instead of parameterized queries.
As to your question, no experience with with ClientDataSets (I'm a lowly Builder Pro user), but perhaps using one of the available in-memory TTable replacements is an option?

------------------
Frans

 

[pemi]

Topgrid (tsGrid in store data mode) is used in PL/SQL developer, sorting is done in memory and works great.

Wondering if Marko can share his code with us.

Petar

 

[Marco Kalter]

I think we can share that. I will check this out and will post the code if possible.

------------------
Marco Kalter
Allround Automations

 

[DaveH]

We use DevExpress's Quantungrid... they do it with an inmemory table local to the client, so no re-firing of the Query. Works great with result sets up to about 1000 rows. Over that, the grid load time becomes noticeable... BUT the sorting is still immediate if you can absorb the longet grid load time.

DAve

 

[vkupisk]

Thanks, Dave, I have since also gone with QuantumGrid -- good product -- getting data local for us is worth the wait even for ~65000 records - not unusual.
Now I am playing with their DBTreelist -- the trick for me there is NOT to load everything at once but only as needed. Won't know if it does the right thing until I test it on production data.

Vitaly