Hi,

I have created the following function UID() for generating unique ids for a few tables.

BEGIN
 DECLARE UID INTEGER;
 DECLARE uidgenerator CURSOR FOR Stmt;

 PREPARE Stmt FROM ' SELECT UID FROM UIDGenerator ';
 OPEN uidgenerator;
 FETCH FIRST FROM uidgenerator ('UID') INTO UID;
  EXECUTE IMMEDIATE
      ' UPDATE UIDGenerator SET UID = UID + 1 ';
 FETCH FIRST FROM uidgenerator ('UID') INTO result;

 CLOSE uidgenerator;

RETURN result;
END

In each table the ID is generated by calling UID(). This part is working.

However I can be sure that no update conflicts will appear when this function is called simultaneously?

Regards,

Mike

Mike


I've not done it but you should be able to start a transaction to effectively lock the table.

However, unless you're intending to extend the scheme later on is there a reason why you can't use the inbuilt "autoinc" facility?

eg here's the key for my companies table

"_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,

Roy Lambert

Hi Roy,

I have a few tables which should use an unique id in the same range.

Adding START TRANSACTION ON TABLES 'UIDGenerator ' TIMEOUT 3000; would do the trick I think.

Regards,

Mike