I need to lock a single table. With other DBMS I can use the LOCK TABLE SQL for doing this. How can I do this in EDB? I have not found this statement in the docs.

I need to manually lock a table as I can't use a transaction, because I want to use an EMPTY TABLE or a CREATE TABLE. This statements seems not to work with an active transaction.

CREATE PROCEDURE "CreateStudioList" ()
BEGIN
 DECLARE TabName VARCHAR;

 EXECUTE IMMEDIATE 'SELECT Name INTO ? FROM Information.Tables WHERE Name = ''StudioList'''
     USING TabName;

 IF TabName IS NULL THEN
   EXECUTE IMMEDIATE '
     CREATE TABLE "StudioList"
     (
     "StudioName" VARCHAR(30) COLLATE "ANSI_CI",
     "StudioURL" VARCHAR(60) COLLATE "ANSI_CI",
     "LastRelease" DATE,
     "VideoCount" INTEGER,
     CONSTRAINT "PrimaryKey" PRIMARY KEY ("StudioName")
     )';
 END IF;
 
 LOCK TABLE StudioList;  <<<<-- This would be needed to prevent other apllications to write new records before my INSERT is executed as else this will result in PK violations.

 EXECUTE IMMEDIATE 'EMPTY TABLE StudioList';

 EXECUTE IMMEDIATE '
   INSERT INTO StudioList (StudioName, StudioURL, LastRelease, VideoCount)
   (SELECT StudioName, StudioURL, Max(ReleaseDate) as LastRelease, Count(*) as VideoCount FROM VideoList
    WHERE COALESCE(StudioName, '''') <> ''''
    GROUP BY StudioName, StudioURL)';

END

Rolf

Not a perfect answer but what about

SET ControlVar = TRUE;
WHILE ControlVar DO
EXECUTE IMMEDIATE 'EMPTY TABLE StudioList';
SET ControlVar = False;
END WHILE;

START TRANSACTION ON TABLES 'StudioList';
EXECUTE IMMEDIATE 'DELETE FROM TABLE StudioList';

EXECUTE IMMEDIATE '
INSERT INTO StudioList (StudioName, StudioURL, LastRelease, VideoCount)
(SELECT StudioName, StudioURL, Max(ReleaseDate) as LastRelease, Count(*) as VideoCount FROM VideoList
WHERE COALESCE(StudioName, '''') <> ''''
GROUP BY StudioName, StudioURL)';

COMMIT;


and in the best tradition - totally untested

Roy Lambert

Thanks that wuld be a solution, but a Lock Table inside a procedure or script would be a good thing anway.

I do now the Empty Table call outside a transaction and in the transaction I call a Delete From again to be sure it is empty again on the insert statement.

Rolf


I used the loop cos I don't know what will happen if you try an EMPTY TABLE and something else has the table in a transaction

Roy Lambert

Rolf,

<< I need to lock a single table. With other DBMS I can use the LOCK TABLE SQL for doing this. How can I do this in EDB? I have not found this statement in the docs. >>

As Roy indicated, you don't want to use EMPTY TABLE.  Instead, use DELETE FROM inside of a transaction.

EMPTY TABLE physically truncates the table, and as such cannot be rolled back.   Hence, it can't be used inside of a transaction.

Tim Young
Elevate Software
www.elevatesoft.com