Weird, that didnt work for me. I only used the quotes as the last ditch
effort. I tried dozens of variants. Your version also returns null for
me.

We wound up putting a Suspend at the end of the procedure and calling it
with select syntax.

I am on version 1.0.0.326. Not sure if that explains it though.

Thanks for helping!

-rg

-----Original Message-----
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Wednesday, March 21, 2001 3:45 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] using Gen_id from a stored proc


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.



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/