Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Does EXECUTE IMMEDIATE release tables? |
Sat, Mar 16 2019 4:33 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | The error is
An error occurred with the statement at line 57 and column 20 (Cannot lock the temporary table InProg in the schema Default for exclusive access) IF StoreName <> '' THEN EXECUTE IMMEDIATE 'DELETE FROM InProg WHERE LoggedOn < '+CAST(OnlyIf AS VARCHAR(6)); SET FNam = REPLACE(':','-',CAST(CURRENT_DATE AS VARCHAR(10)) + ' '+ CAST(CURRENT_TIME AS VARCHAR(5))); EXECUTE IMMEDIATE 'EXPORT TABLE InProg TO "' + FNam+ '.csv" IN STORE '+StoreName+' FORMAT DELIMITED'; EXECUTE IMMEDIATE 'DROP TABLE "InProg"'; <<<<<<<<<<<<<<<< END IF; At the point the error occurs I've closed the cursor that was pointing to InProg (in fact I tried closing every cursor in the script) InProg is a temporary table so it will get cleaned up at some point. Up at the start of the script I have PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=''InProg'''; OPEN InfoCursor; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "InProg"'; END IF; and can run the script multiple times so the handle is being released at some point.Full script below my sig Roy Lambert SCRIPT(IN WhichDate DATE, OnlyIf INTEGER, StoreName VARCHAR) BEGIN DECLARE FNam VARCHAR; DECLARE HrCntr INTEGER; DECLARE MinCntr INTEGER; DECLARE PK TIMESTAMP; DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ATable SENSITIVE CURSOR FOR Stmt; DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=''InProg'''; OPEN InfoCursor; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "InProg"'; END IF; CLOSE InfoCursor; EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "InProg" ("AtTime" TIMESTAMP,"EndPoint" TIMESTAMP, LoggedOn INTEGER)'; PREPARE Stmt FROM 'SELECT * FROM InProg'; OPEN ATable; SET PK = WhichDate; SET HrCntr = 0; WHILE HrCntr < 24 DO SET MinCntr = 0; WHILE MinCntr < 60 DO INSERT INTO ATable ('AtTime', 'EndPoint') VALUES (PK, (PK+INTERVAL '59' SECOND) + INTERVAL '999' MSECOND); SET PK = PK + INTERVAL '1' MINUTE; SET MinCntr = MinCntr + 1; END WHILE; SET PK = PK +INTERVAL '1' MINUTE; SET HrCntr = HrCntr + 1; END WHILE; PREPARE ResultStmt FROM 'UPDATE InProg SET LoggedOn = (SELECT COUNT(*) FROM UsersLog X WHERE (X.StartDateTime BETWEEN AtTime AND EndPoint) OR (X.FinishDateTime BETWEEN AtTime AND EndPoint) OR (X.StartDateTime < AtTime AND X.FinishDateTime > EndPoint) )'; EXECUTE ResultStmt; CLOSE ResultCursor; CLOSE ATable; IF StoreName <> '' THEN EXECUTE IMMEDIATE 'DELETE FROM InProg WHERE LoggedOn < '+CAST(OnlyIf AS VARCHAR(6)); SET FNam = REPLACE(':','-',CAST(CURRENT_DATE AS VARCHAR(10)) + ' '+ CAST(CURRENT_TIME AS VARCHAR(5))); EXECUTE IMMEDIATE 'EXPORT TABLE InProg TO "' + FNam+ '.csv" IN STORE '+StoreName+' FORMAT DELIMITED'; EXECUTE IMMEDIATE 'DROP TABLE "InProg"'; END IF; END |
Sat, Mar 16 2019 5:50 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
CLOSE doesn't release the resources, you also have to UNPREPARE the cursor before trying to delete the underlying table. -- Fernando Dias [Team Elevate] |
Sat, Mar 16 2019 9:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
>CLOSE doesn't release the resources, you also have to UNPREPARE the cursor before trying to delete the underlying table. Thanks - that sort of makes sense, and, more importantly, it works. Roy |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |