Hi

I am playing with temporary tables and need to check if an index exists.

The code below does not work:

Query.Sql.Clear;
Query.Sql.Add('select * from information.indexes where tablename=' + QuotedStr(TTab.TableName) + ' and name=' + QuotedStr(cIdxName);
Query.Open;
if Query.RecordCount > 0 then begin
  Query.Sql.Clear;
  Query.Sql.Add('drop index "' + cIdxName + '" from "' + TTab.TableName + '"');
  Query.Sql.ExecSql;
end;

But the code below works like expected.

try
  Query.Sql.Clear;
  Query.Sql.Add('drop index "' + cIdxName + '" from "' + TTab.TableName + '"');
  Query.Sql.ExecSql;
except
end;

Is there a way to access the indexes on temporary tables ?

Eduardo

Jose


Just had a quick look and you're right, there's also no createsql code in the information.temporarytables. I suppose the logic is that since they are temporary you must have created them within the program so you should know.

Roy Lambert

Eduardo,

You don't have to explicitly drop the temp indexes - they will be gone when you drop the temporary table or close the session.

--
Fernando Dias
[Team Elevate]

Fernando

<<
You don't have to explicitly drop the temp indexes - they will be gone when you drop the temporary table or close the session.
>>

I know that but I have this table generated dinamically in my app where users can switch the order of data available just by clicking on title of the column. Then I create an index called "INDEX" and if the user click in the same column again the I drop the INDEX index and create a new one using descending option.

I know I can create this in the other manners but my question just reside on "is there a way to query temporary indexes ?".

BTW

Thanks for feedback

Eduardo

Eduardo,

<<"is there a way to query temporary indexes ?">>

None that I am aware of, sorry.

--
Fernando Dias
[Team Elevate]

Jose


From that I'm guessing you have a TEDBTable component connected to some visual controls> I don't know and won't have time to experiment until this afternoon but if I'm right I'm wondering about checking the tables indexdefs or indexfieldsnames or IndexName properties

Roy Lambert

Jose


Had a bit of a play

procedure TForm2.Button1Click(Sender: TObject);
var
sl: TStringList;
Cntr: integer;
begin
EDBQuery1.Sql.Clear;

EDBQuery1.Sql.Add('CREATE TEMPORARY TABLE Fred AS SELECT * FROM Transactions WITH DATA');
EDBQuery1.ExecSQL;

EDBQuery1.Sql.Clear;
EDBQuery1.Sql.Add('CREATE INDEX "AInOut" ON "Fred" ("_InOut")');
EDBQuery1.ExecSql;

EDBQuery1.Sql.Clear;
EDBQuery1.Sql.Add('CREATE INDEX "DInOut" ON "Fred" ("_InOut" DESC)');
EDBQuery1.ExecSql;

EDBQuery1.Sql.Clear;
EDBQuery1.Sql.Add('CREATE INDEX "Origin" ON "Fred" ("_Origin")');
EDBQuery1.ExecSql;

EDBTable1.Open;
sl := TStringList.Create;
for Cntr := 0 to EDBTable1.IndexDefs.Count - 1 do sl.Add(EDBTable1.IndexDefs[Cntr].Name + '...' + EDBTable1.IndexDefs[Cntr].Fields + '... ' + EDBTable1.IndexDefs[Cntr].DescFields);
showmessage(sl.Text);
end;

gives me the list of indices on the temporary table which I had hoooked onto EDBTable1.

Roy Lambert

Roy,

Thaks for that - I wouldn't think of it because I always think in therms of SQL first
I'll use your tip to build a TempIndexExists(TableName, IndexName: string): boolean function that might be handy.

--
Fernando Dias
[Team Elevate]

Fernando


>Thaks for that - I wouldn't think of it because I always think in therms of SQL first

That can't be right otherwise you'd have been using a query and altering the ORDER BY <VBG>

>I'll use your tip to build a TempIndexExists(TableName, IndexName: string): boolean function that might be handy.

Good idea - don't forget to post in the extensions ng

Roy

Eduardo,

<< Is there a way to access the indexes on temporary tables ? >>

No, it is assumed that you are creating them (within the same area of code), so you know which indexes are present.

Tim Young
Elevate Software
www.elevatesoft.com