Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Temporary indexes |
Tue, Jan 29 2019 2:13 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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 |
Wed, Jan 30 2019 6:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jan 30 2019 12:22 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Wed, Jan 30 2019 1:55 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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 |
Wed, Jan 30 2019 4:04 PM | Permanent Link |
Fernando Dias Team Elevate | Eduardo,
<<"is there a way to query temporary indexes ?">> None that I am aware of, sorry. -- Fernando Dias [Team Elevate] |
Thu, Jan 31 2019 2:31 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
Thu, Jan 31 2019 9:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Jan 31 2019 9:44 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Fri, Feb 1 2019 2:01 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
Fri, Feb 1 2019 1:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |