At 04:23 PM 02-05-01 +0000, you wrote:

>He need to know the new pk(generator) value right after an insert. I
>told him to use a stored procedure. He said it's to complicated to
>create and maintain a SP for every table. Then I told him to use
>select gen_id(gen_name, 0) from rdb$database and he said this works
>but it would be even nicer if there would be something like the
>returning clause from Oracle(insert into t (seq_id) values
>foo_seq.nextval()) returning seq_id)!
>So my question is: Are these the only two possibilites to solve this
>problem? Are there any plans to support anything like returning in
>Firebird?

I think maybe your friend doesn't understand that the generator is not linked to the table in any way...nor to the transaction in which the BEFORE INSERT trigger fires.

You could inform your friend that generator values are completely outside transaction control so that, rather than look for the value to be returned *after* the insert, he can actually get it just before the insert and know with confidence that he can (a) insert the new row with that pre-obtained generator value and (b) if the insert commits successfully, his application already knows the new PK.

That's what GeneratorLinks in IBO accomplishes. You could show your friend how he could make a generic client function that takes a generator name as input, constructs the sql statement that you recommended to him (select gen_id(<generatorname, 1) from rdb$database) and returns the result into the equivalent of a 64-bit integer variable.

You would have to explain to him that he must ensure that his BEFORE INSERT trigger tests the incoming PK value for null before deciding to fire gen_id() itself.

AFAIK, Firebird has no plans that I've heard of so far to implement something like dynamic sql statements that return results.

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________