I have written a function to generate a Unique ID (UID). It uses a local table for sets of 10,000 rows, when it has used all these rows up it gets a next set of 10,000:

--

Here is the local table which holds the UID's

CREATE TABLE UIDGenerator
 (
   UID INTEGER DEFAULT 10000,
   NextMaxUID INTEGER DEFAULT 20000
 )


--

CREATE FUNCTION "UID" ()
RETURNS INTEGER
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE Result INTEGER DEFAULT 1;
 DECLARE UID INTEGER;
 DECLARE NextMaxUID INTEGER;

PREPARE Stmt FROM ' SELECT UID, NextMaxUID FROM UIDGenerator ';
OPEN Crsr;
FETCH FIRST FROM Crsr ('UID') INTO UID;
FETCH FROM Crsr ('UID') INTO Result;
FETCH FROM Crsr ('NextMaxUID') INTO NextMaxUID;   
CLOSE Crsr;

IF (UID < NextMaxUID) THEN
 EXECUTE IMMEDIATE
   ' UPDATE UIDGenerator SET UID = UID + 1 ';
 ELSE
 BEGIN
   FetchNewMaxIDFromCloud(); /* This statement finds the next set of 10,000 which are available & resets
                                                          UIDGenerator table values */
   PREPARE Stmt FROM ' SELECT UID, NextMaxUID FROM UIDGenerator ';
   OPEN Crsr;
   FETCH FIRST FROM Crsr ('UID') INTO UID;
   FETCH FROM Crsr ('UID') INTO Result;
   FETCH FROM Crsr ('NextMaxUID') INTO NextMaxUID;   
   CLOSE Crsr;
   EXECUTE IMMEDIATE
       ' UPDATE UIDGenerator SET UID = UID + 1 ';
 END;
 END IF;



END

--

I now want to redefine my tables so they call the Function:

ALTER TABLE "Staff"
ALTER COLUMN "ID" AS INTEGER GENERATED ALWAYS AS UID()

--

"ID" is the primary key.

When I try to run this code I get the error:

"ElevateDB Error #1004 The primary key constraint ID for the table Staff has been violated (NULL values are not allowed in a primary key)"

... I realise there may be a chance of the UID function not returning a value, but this behaviour is not good for me.

I can do more complicated things, like writing triggers on the AFTER INSERT of each table to insert the UID() ... is that the best course, or is there a way of successfully calling the function in the table-definition SQL?

Adam


Probably won't work but what about

ALTER TABLE "Staff"
ALTER COLUMN "ID" AS INTEGER DEFAULT UID()

or whatever the syntax really is. I know DEFAULT can't call external functions from a dll but I think it can from sql functions.

You'll also need to run another bit of SQL first to do the initial population.

Roy Lambert Roy Lambert [Team Elevate]

Adam,

In addition to what Roy said, you could also consider using a BEFORE INSERT trigger to call the function and get the new ID value. Also, take a look at the GENERATED AS IDENTITY columns, perhaps it applies to

--
Fernando Dias
[Team Elevate]

Roy Lambert wrote:

>>Probably won't work but what about

>>ALTER TABLE "Staff"
>>ALTER COLUMN "ID" AS INTEGER DEFAULT UID()

The above works Roy, but not on a row which is a primary key. There seems to be some requirement in the primary key field that a value is definitely generated, and calling a function doesn't seem to be strong enough to guarantee this.

Adam

>The above works Roy, but not on a row which is a primary key. There seems to be some requirement in the primary key field that a value is definitely generated, and calling a function doesn't seem to be strong enough to guarantee this.

In ElevateDB none of the columns in a primary key are allowed to be NULL (gave me some grief when it was introduced) but maybe Tim's just being protective.

Looks like you're back to triggers.

Roy Lambert [Team Elevate]

<Adam Brett> wrote in message news:1130AEC7-9229-43F4-8309-D2B72DFFF85D@news.elevatesoft.com...
> I have written a function to generate a Unique ID (UID). It uses a local table for sets of 10,000 rows, when it has
used all these rows up it gets a next set of 10,000:
>
> --
>
> Here is the local table which holds the UID's
>
> CREATE TABLE UIDGenerator
>   (
>     UID INTEGER DEFAULT 10000,
>     NextMaxUID INTEGER DEFAULT 20000
>   )
>
>
> --
>
> CREATE FUNCTION "UID" ()
> RETURNS INTEGER
> BEGIN
>   DECLARE Crsr CURSOR FOR Stmt;
>   DECLARE Result INTEGER DEFAULT 1;
>   DECLARE UID INTEGER;
>   DECLARE NextMaxUID INTEGER;
>
> PREPARE Stmt FROM ' SELECT UID, NextMaxUID FROM UIDGenerator ';
> OPEN Crsr;
> FETCH FIRST FROM Crsr ('UID') INTO UID;
> FETCH FROM Crsr ('UID') INTO Result;
> FETCH FROM Crsr ('NextMaxUID') INTO NextMaxUID;
> CLOSE Crsr;
>
> IF (UID < NextMaxUID) THEN
>   EXECUTE IMMEDIATE
>     ' UPDATE UIDGenerator SET UID = UID + 1 ';
>   ELSE
>   BEGIN
>     FetchNewMaxIDFromCloud(); /* This statement finds the next set of 10,000 which are available & resets
>                                                            UIDGenerator table values */
>     PREPARE Stmt FROM ' SELECT UID, NextMaxUID FROM UIDGenerator ';
>     OPEN Crsr;
>     FETCH FIRST FROM Crsr ('UID') INTO UID;
>     FETCH FROM Crsr ('UID') INTO Result;
>     FETCH FROM Crsr ('NextMaxUID') INTO NextMaxUID;
>     CLOSE Crsr;
>     EXECUTE IMMEDIATE
>         ' UPDATE UIDGenerator SET UID = UID + 1 ';
>   END;
>   END IF;
>
>
>
> END
>
> --
>
> I now want to redefine my tables so they call the Function:
>
> ALTER TABLE "Staff"
> ALTER COLUMN "ID" AS INTEGER GENERATED ALWAYS AS UID()
>
> --
>
> "ID" is the primary key.
>
> When I try to run this code I get the error:
>
> "ElevateDB Error #1004 The primary key constraint ID for the table Staff has been violated (NULL values are not
allowed in a primary key)"
>
> .. I realise there may be a chance of the UID function not returning a value, but this behaviour is not good for me.
>
> I can do more complicated things, like writing triggers on the AFTER INSERT of each table to insert the UID() ... is
that the best course, or is there a way of successfully calling the function in the table-definition SQL?
>

<Adam Brett> wrote in message news:1130AEC7-9229-43F4-8309-D2B72DFFF85D@news.elevatesoft.com...

It looks like the function is not returning a value ie it needs the following added at the end

RETURN Result

John

John


Actually reading the function must be regarded as cheating!

Roy Lambert

Damn it John you are right!

... actually the following:

CREATE TABLE "Staff"
(
"ID" INTEGER DEFAULT UID()  NOT NULL,
"FirstName" VARCHAR(40) COLLATE "ANSI" NOT NULL,
"LastName" VARCHAR(40) COLLATE "ANSI" NOT NULL,
"NICode" VARCHAR(12) COLLATE "ANSI",
"DateCreated" TIMESTAMP DEFAULT Current_TimeStamp,
"Current" BOOLEAN DEFAULT True,
"NID" INTEGER GENERATED ALWAYS AS NUID()
,
CONSTRAINT "ID" PRIMARY KEY ("ID")
)

Works perfectly if my UID() function is written properly

I am a very happy boy, as I _think_ this UID concept will be a really good way to manage my disconnected-multiple-sites application. I'm just trying to figure out the whole script, if I get it working I will post it to the newsgroups

Roy,

<< or whatever the syntax really is. I know DEFAULT can't call external
functions from a dll but I think it can from sql functions. >>

DEFAULT expressions can use any user-defined function, including those that
refer to external modules.  Same with GENERATED expressions.  COMPUTED
expressions can not, however, use user-defined functions due to the remote
client-side/no catalog access constraint.

--
Tim Young
Elevate Software
www.elevatesoft.com