Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Dropping all indexes in a script |
Tue, Oct 6 2009 6:08 AM | Permanent Link |
Peter | Hello
I use the following script as the basis for some memory tables, and it works well, except where I create an index in the table. If I then re-create the table using different parameters that do not require an index, I get the error message "inxLastName" does not exist. I guess I could re-create the index for the singleton query, but maybe I can have the script clear out the index at the same time the table is dropped. If I call the script below with the SQLStatement similar to 'select * from Client where OrderValue > 30.0', then run an ALTER TABLE statement to add an index inxLastName to the new table, all is well, and the table is sorted on LastName. If I then call the script below with a different statement, such as the singleton 'select * from Client where ClientID = 345' then the index is redundant, but the inxLastName persists from when the same table had been created earlier. SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN AddBool BOOLEAN) BEGIN DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR ResultStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING TableName; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"'; END IF; CLOSE InfoCursor; PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA'; EXECUTE ResultStmt; IF AddBool THEN EXECUTE IMMEDIATE 'ALTER TABLE "'+TableName+'" ADD COLUMN "SelRow" BOOLEAN DEFAULT %s'; END IF; END How can I force the indexes to be dropped, without the calling method knowing the names of the indexes? Let me know if I haven't explained it properly. I'm using ANSI, version 2.03. Regards & TIA Peter |
Tue, Oct 6 2009 6:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
As far as I know dropping the table drops the indices as well so I'm guessing your problem is with the table component you then hook the created memory table up to. Since there's no way to do that in the script then before you call the script set the table component's indexname to '' Roy Lambert [Team Elevate] |
Tue, Oct 6 2009 7:31 AM | Permanent Link |
Peter | Roy
Doh! Of course, I should have seen that. Thanks again. Peter |
This web page was last updated on Thursday, September 26, 2024 at 10:04 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |