Rhett wrote:

> >
> > Create Procedure GeneratePrimaryKey
> > Returns ("PK" Integer )
> > As Begin
> > "PK" = Gen_Id( PrimaryKeyGenerator, 1 );
> > End !!
> > Set Term ; !!
> >
> > I execute this code using both of these:
> >
> > Execute Procedure GeneratePrimaryKey
> > Select PK From GeneratePrimaryKey
> >
> > However, though the PrimarykeyGenerator value is being incremented with
> > both approaches, neither of these approaches are actually returning the
> > generated value. I get back a table with one column and no data.
> >
> > Does anyone have any idea what I am doing wrong?

I couldn't get double quotes through the parser - maybe
we're using different versions, but double quotes are not
necessary and probably don't work.

This did:

SQL> create generator pkgen!!
SQL> create procedure genpk
CON> returns (pk integer)
CON> as begin
CON> pk = gen_id (pkgen, 1);
CON> end !!
SQL> set term ; !!
SQL> execute procedure genpk;

PK
============
1

SQL> execute procedure genpk;

PK
============
2

SQL>

At 07:47 PM 3/21/2001 +0100, Jörg Schiemann wrote:

>Try
>
>DECLARE VARIABLE GEN_NO INTEGER;
>DECLARE VARIABLE GEN_NEXT_NO INTEGER;
>
>SELECT GEN_ID(GEN_YOUR_GENERATOR, 0) FROM RDB$DATABASE INTO :GEN_NO;

^^^^

>GEN_NEXT_NO = GEN_NO + 1;

I strongly recommend that you NOT do that. The function
GEN_ID(GEN_YOUR_GENERATOR, 0)
does not increment the generator. To increment the generator, you
must use a value of 1 or greater, not 0. Unless you increment
the generator, everyone will get the same value. If some programs
increment it and others don't, you'll still get duplicates. Unless
you are ABSOLUTELY sure that everyone will increment the number after
they get it, just use the value returned by the generator.

I happen to prefer:
pk = gen_id (pkgen, 1);
to:
SELECT GEN_ID(GEN_YOUR_GENERATOR, 1) FROM RDB$DATABASE INTO :GEN_NO;
but the two statements are equivalent.


Regards,

Ann
www.ibphoenix.com
We have answers.