Title: TIBtable dosn't use descending indices : work around
Question: Having a TIBtable with a descending index in indexname property ,
the tIBtable dosn't sort recordes in descennding order ?
Answer:
TIbtable dosn't recognize descending indexes : Work around
We have a Table named Articles whith a field named Code and we have created a
descending index in this field named CODED we want to use a TIBtable to get data
from 'articles' table sorted by code descending we must set indexname property to 'CODED'
we do this but the table when opened sort records by code ascending so we show the indexdefs property collection 'CODED' indexdef already exists but ixDescending doesn't appear in options.
In order to update indexdefs list TIBtable uses the protected methed UpdateIndexDefs
but the implementation of this method is wrong
/****************************************************/
procedure TIBTable.UpdateIndexDefs;
var
Opts: TIndexOptions;
Flds: string;
Query, SubQuery: TIBSQL;
begin
if not (csReading in ComponentState) then begin
if not Active and not FSwitchingIndex then
FieldDefs.Update;
IndexDefs.Clear;
Database.InternalTransaction.StartTransaction;
Query := TIBSQL.Create(self);
try
FPrimaryIndexFields := '';
Query.GoToFirstRecordOnExecute := False;
Query.Database := DataBase;
Query.Transaction := Database.InternalTransaction;
Query.SQL.Text :=
'Select I.RDB$INDEX_NAME, I.RDB$UNIQUE_FLAG, I.RDB$INDEX_TYPE, ' + {do not localize}
'I.RDB$SEGMENT_COUNT, S.RDB$FIELD_NAME from RDB$INDICES I, ' + {do not localize}
'RDB$INDEX_SEGMENTS S where I.RDB$INDEX_NAME = S.RDB$INDEX_NAME '+ {do not localize}
'and I.RDB$RELATION_NAME = ' + '''' + {do not localize}
FormatIdentifierValue(Database.SQLDialect, FTableName) + '''';
Query.Prepare;
Query.ExecQuery;
while (not Query.EOF) and (Query.Next nil) do
begin
with IndexDefs.AddIndexDef do
begin
Name := TrimRight(Query.Current.ByName('RDB$INDEX_NAME').AsString); {do not localize}
Opts := [];
if Pos ('RDB$PRIMARY', Name) = 1 then Include(Opts, ixPrimary); {do not localize} {mbcs ok}
if Query.Current.ByName('RDB$UNIQUE_FLAG').AsInteger = 1 then Include(Opts, ixUnique); {do not localize}
if Query.Current.ByName('RDB$INDEX_TYPE').AsInteger = 2 then Include(Opts, ixDescending); {do not localize}
Options := Opts;
if (Query.Current.ByName('RDB$SEGMENT_COUNT').AsInteger = 1) then {do not localize}
Fields := Trim(Query.Current.ByName('RDB$FIELD_NAME').AsString) {do not localize}
else begin
SubQuery := TIBSQL.Create(self);
try
SubQuery.GoToFirstRecordOnExecute := False;
SubQuery.Database := DataBase;
SubQuery.Transaction := Database.InternalTransaction;
SubQuery.SQL.Text :=
'Select RDB$FIELD_NAME from RDB$INDEX_SEGMENTS where RDB$INDEX_NAME = ' + {do not localize}
'''' +
FormatIdentifierValue(Database.SQLDialect, Name) +
'''' + 'ORDER BY RDB$FIELD_POSITION'; {do not localize}
SubQuery.Prepare;
SubQuery.ExecQuery;
Flds := '';
while (not SubQuery.EOF) and (SubQuery.Next nil) do
begin
if (Flds = '') then
Flds := TrimRight(SubQuery.Current.ByName('RDB$FIELD_NAME').AsString) {do not localize}
else begin
Query.Next;
Flds := Flds + ';' + TrimRight(SubQuery.Current[0].AsString);
end;
end;
Fields := Flds;
finally
SubQuery.Free;
end;
end;
if (ixDescending in Opts) then
DescFields := Fields;
if ixPrimary in Opts then
FPrimaryIndexFields := Fields;
end;
end;
finally
Query.Free;
Database.InternalTransaction.Commit;
end;
end;
end;
/*******************************************************/
to include the ixDesending in options of indexdef IBX test if'RDB$INDEX_TYPE'=2:
if Query.Current.ByName('RDB$INDEX_TYPE').AsInteger = 2 then Include(Opts, ixDescending); {do not localize}
but the correct value is 'RDB$INDEX_TYPE'=1 so we must change the line in IBTable.pas to :
if Query.Current.ByName('RDB$INDEX_TYPE').AsInteger = 1 then Include(Opts, ixDescending); {do not localize}
and recompile VCLIB50.dpk , after this the ixDescending appear in options but the table already dosn t sort records in descending order , the second problem is that IBX
generates a query to retreive data fo the TIBtable by using a private method GenerateSQL in TIBTable.pas
/**************************************************/
procedure TIBTable.GenerateSQL;
var
i: Integer;
SQL: TStrings;
OrderByStr: string;
bWhereClausePresent: Boolean;
begin
bWhereClausePresent := False;
Database.CheckActive;
Transaction.CheckInTransaction;
if IndexDefs.Updated = False then
IndexDefs.Update;
if IndexFieldNames '' then
OrderByStr := FormatFieldsList(IndexFieldNames)
else if IndexName '' then
OrderByStr := FormatFieldsList(IndexDefs[IndexDefs.Indexof (IndexName)].Fields)
else if FDefaultIndex and (FPrimaryIndexFields '') then
OrderByStr := FormatFieldsList(FPrimaryIndexFields);
SQL := TStringList.Create;
SQL.Text := 'select ' + {do not localize}
FormatIdentifier(Database.SQLDialect, FTableName) + '.*, '
+ 'RDB$DB_KEY as IBX_INTERNAL_DBKEY from ' {do not localize}
+ FormatIdentifier(Database.SQLDialect, FTableName);
if Filtered and (Filter '') then
begin
SQL.Text := SQL.Text + ' where ' + Filter; {do not localize}
bWhereClausePresent := True;
end;
if (MasterSource nil) and (MasterSource.DataSet nil) and (MasterFields '') then
begin
if bWhereClausePresent then
SQL.Text := SQL.Text + ' AND ' {do not localize}
else
SQL.Text := SQL.Text + ' WHERE '; {do not localize}
ExtractLinkfields;
if FDetailFieldsList.Count IBError(ibxeUnknownError, [nil]);
for i := 0 to FMasterFieldsList.Count - 1 do
begin
if i 0 then
SQL.Text := SQL.Text + 'AND ';
SQL.Text := SQL.Text +
FormatIdentifier(Database.SQLDialect, FDetailFieldsList.Strings[i]) +
' = :' +
FormatIdentifier(Database.SQLDialect, FMasterFieldsList.Strings[i]);
end;
end;
if OrderByStr '' then
SQL.Text := SQL.Text + ' order by ' + OrderByStr; {do not localize}
SelectSQL.Assign(SQL);
RefreshSQL.Text := 'select ' + {do not localize}
FormatIdentifier(Database.SQLDialect, FTableName) + '.*, '
+ 'RDB$DB_KEY as IBX_INTERNAL_DBKEY from ' {do not localize}
+ FormatIdentifier(Database.SQLDialect, FTableName) +
' where RDB$DB_KEY = :IBX_INTERNAL_DBKEY'; {do not localize}
WhereDBKeyRefreshSQL.Assign(RefreshSQL);
InternalPrepare;
SQL.Free;
end;
/******************************************************/
To use Index or IndexFieldnames property of TIBtable the method constructs an order by string : OrderByStr
if IndexFieldNames '' then
OrderByStr := FormatFieldsList(IndexFieldNames)
else if IndexName '' then
OrderByStr := FormatFieldsList(IndexDefs[IndexDefs.Indexof (IndexName)].Fields)
else if FDefaultIndex and (FPrimaryIndexFields '') then
OrderByStr := FormatFieldsList(FPrimaryIndexFields);
Suppose that whe have a table "Articles" having A Column named Code and a descending
index coded and we set indexname property to CODED
IBX will generate an orderByStr = 'CODE' so the result dosnt use the descending index because
FormatFieldsList function deosnt any thing than replacing ';' in indexfieldnames by ','
so to have the descending index working we change the line
if IndexName '' then
OrderByStr := FormatFieldsList(IndexDefs[IndexDefs.Indexof (IndexName)].Fields)
To
if IndexName '' then
begin
OrderByStr := FormatFieldsList(IndexDefs[IndexDefs.Indexof (IndexName)].Fields)
if ixDescending in IndexDefs[IndexDefs.Indexof (IndexName)].Options then
OrderByStr := OrderByStr + ' Desc ';
end
We recompile DCLIB50.dpk and it work so if you indicate a descending index for a TIBtable it will sort records in descending order.