Title: Methods for Cutting and Pasting SQL Select Order by Clause
Question: Most of data access components don't have method for sorting in memory records. So the only way for sorting records is add an order by clause to the query and reopen it. These method make this task easier to do.
Answer:
Here are the methods:
procedure CutOrderByClause(ASQL:TStrings);
var
aParser:TParser;
aStream:TMemoryStream;
tokenPos,orderByClausePos:integer;
begin
orderByClausePos:=-1;
aParser:=nil;
aStream:=TMemoryStream.Create;
try
ASQL.SaveToStream(aStream);
aStream.Position:=0;
aParser:=TParser.Create(aStream);
//find previous order by clause position
while aParser.TokentoEof do
begin
tokenPos:=aParser.SourcePos;
if lowercase(aParser.TokenString)='order' then
begin
orderByClausePos:=tokenPos;
aParser.NextToken;
if (aParser.Token=toEof)or(lowercase(aParser.TokenString)'by') then
raise Exception.Create('Incorrect order by clause!')
else
begin
aParser.NextToken;
while aParser.TokentoEof do
begin
if lowercase(aParser.TokenString)='select' then
raise Exception.Create('Unable to cut order by clause!');
aParser.NextToken;
end;
break;
end;
end;
aParser.NextToken;
end;
//cut previous order by clause
if orderByClausePos=0 then
begin
aStream.Size:=orderByClausePos;
aStream.Position:=0;
ASQL.LoadFromStream(aStream);
end;
finally
aParser.Free;
aStream.Free;
end;
end;
procedure SortSQL(ASQL:TStrings;OrderByClauseArr:array of string);
var
i,n,u:integer;
orderByClause:string;
begin
CutOrderByClause(ASQL);
if length(orderByClauseArr)0 then
begin
//construct new order by clause
u:=high(orderByClauseArr);
orderByClause:='order by ';
n:=length(orderByClause);
for i:=0 to u do
begin
if length(orderByClause)n then
orderByClause:=orderByClause+', ';
orderByClause:=orderByClause+orderByClauseArr[i];
end;
//append new order by clause into SQL
if length(orderByClause)n then
ASQL.Append(orderByClause);
end;
end;
This event handler shows you how to use them:
procedure TfrmSortSQL.btnChangeSQLClick(Sender: TObject);
var
orderByClauseArr:array of string;
i,u:integer;
begin
u:=memSortFields.Lines.Count;
SetLength(orderByClauseArr,u);
dec(u);
for i:=0 to u do
orderByClauseArr[i]:=memSortFields.Lines[i];
if u=0 then
SortSQL(memSQL.Lines,orderByClauseArr)
else
CutOrderByClause(memSQL.Lines);
end;
Or you can simply use it like below:
SortSQL(memSQL.Lines,['deptno desc','empno','1',inttostr(aField.FieldNo)]);
OK that's all. I hope this article can help your database project development.