Hi,

How to delete Indexed Field from Full Text Indexing list using SQL?

I have tried
DROP INDEX  "MyT"."MyIndexedF", but got error that my index does not exists.

Regards,
Mike

Mike


You can't do it that way

Essentially you have to re-specify the full text index and then it will be rebuilt. In TMaN I allow users to set some of the fields for FTI or not - here's my code

// Now we check full text indexing
luMandN.Open;
FTIFields := luMandN.TextIndexFields;
luMandN.Close;
FTI := slOptsStr('_FullTextIndex');
if FTI <> '' then begin
 Stoppers := TStringList.Create;
 if FileExists(IncludeTrailingBackSlash(AppPath) + 'StopWords.tmn') then Stoppers.LoadFromFile(IncludeTrailingBackSlash(AppPath) + 'StopWords.tmn');
 FTIMod := False;
 FTIWanted := '';
 if FTI[1] = 'Y' then begin
  FTIWanted := FTIWanted + '_Subject,';
  FTIMod := 0 = Pos('_Subject', mnd.luMandN.TextIndexFields);
 end else if (FTI[1] = 'N') and (0 <> Pos('_Subject', mnd.luMandN.TextIndexFields)) then begin
  FTIWanted := FTIWanted + 'X,';
  FTIMod := True;
 end;
 if FTI[2] = 'Y' then begin
  FTIMod := FTIMod or (0 = Pos('_Message', mnd.luMandN.TextIndexFields));
  FTIWanted := FTIWanted + '_Message,';
 end else if (FTI[2] = 'N') and (0 <> Pos('_Message', mnd.luMandN.TextIndexFields)) then begin
  FTIWanted := FTIWanted + 'X,';
  FTIMod := True;
 end;
 if FTI[3] = 'Y' then begin
  FTIMod := FTIMod or (0 = Pos('_Comments', mnd.luMandN.TextIndexFields));
  FTIWanted := FTIWanted + '_Comments,';
 end else if (FTI[3] = 'N') and (0 <> Pos('_Comments', mnd.luMandN.TextIndexFields)) then begin
  FTIWanted := FTIWanted + 'X,';
  FTIMod := True;
 end;
 if FTI[4] = 'Y' then begin
  FTIMod := FTIMod or (0 = Pos('_Headers', mnd.luMandN.TextIndexFields));
  FTIWanted := FTIWanted + '_Headers,';
 end else if (FTI[4] = 'N') and (0 <> Pos('_Headers', mnd.luMandN.TextIndexFields)) then begin
  FTIWanted := FTIWanted + 'X,';
  FTIMod := True;
 end;
 if FTIMod or slOptsBool('_ForceReindex', False) then begin
  Screen.Cursor := crHourGlass;
  Stoppers.Delimiter := ' ';
  gpQuery.SQL.Clear;
  gpQuery.SQL.Add('ALTER TABLE MandN');
  gpQuery.SQL.Add('TEXT INDEX (' + StringReplace(FTIWanted + '_Flags', 'X,', '', [rfReplaceAll]) + ')');
  gpQuery.SQL.Add('STOP WORDS ' + QuotedStr(Stoppers.DelimitedText));
  gpQuery.SQL.Add('SPACE CHARS #1+#2+#3+#4+#5+#6+#7+#8+#9+#10+#11+#12+#13+#14+#15+#16+#17+#18+#19+#20+#21+#22+#23+#24+#25+#26+#27+#28+#29+#30+#31+#32');
  gpQuery.SQL.Add('INCLUDE CHARS ''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_-.@''');
  gpQuery.SQL.Add('NOBACKUP');
  gpQuery.ExecSQL;
  gpQuery.Close;
  gpQuery.UnPrepare;
  gpQuery.SQL.Clear;
  slOpts.Values['_ForceReindex'] := 'False';
  UpdateSysOpts('', '', nil, slOpts);
  Screen.Cursor := crDefault;
  MessageDlg('Its probably a good idea to close down and restart TMaN after this re-index', mtInformation, [mbOK], 0);
 end;
 Stoppers.Free;
end;

Roy Lambert

Mike,

<< How to delete Indexed Field from Full Text Indexing list using SQL? >>

Just use this:

ALTER TABLE MyTable
TEXT INDEX ()

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


That's fine if he wants to get rid of the lot but from the use of "Full Text Indexing list" I'm guessing there are several fields involved and he only wants rid of one.

Roy Lambert

Roy,

<< That's fine if he wants to get rid of the lot but from the use of "Full
Text Indexing list" I'm guessing there are several fields involved and he
only wants rid of one. >>

In that case he only needs to specify the fields that he wants to keep
(instead of "()"), and that will give him what he wants.

--
Tim Young
Elevate Software
www.elevatesoft.com