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

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]

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