Roman Rokytskyy wrote:

>
>
>>Virtually all of my application level programming is in Java, for
>>example, and JDBC has no way to support the construct above. [OK, I
>>suppose you could argue that it doesn't have a way to access a
>>sequence, either, which would rather put me on the spot].
>>
>>
>
>Unfortunately(?) there is a standard API in JDBC 3.0 for this case,
>that's why I wrote that it would be nice addition for JayBird:
>
>public ResultSet getGeneratedKeys()
> throws SQLException
>
>
>

That's a different issue. What was proposed was an open ended INSERT
... RETURNING <variable>... The getGeneratedKeys may overlap a useful
subset, but that's about it.

Among the problems of getGeneratedKeys is that it's unimplementable in
most database systems, include all that support triggers and
sequences/generators. A database engine can capture sequence values,
but I don't think any can track it from sequence to key, particularly
when a trigger concatenates a string on the front or end of the sequence
value. And what happens if there is one trigger to assign a unique
value, another trigger to log the insert, and a third to queue something
to a replication log? All reference the generate key, possibly as part
of their own primary key, and each may access other sequences. What
comes back in the resultSet from getGeneratedKeys? Nobody knows because
the JDBC guys didn't bother to specify. How could this possible be used
in a portable manner? Answer: it can't. The JDBC solution is flawed.
Maybe it could be fixed or qualified. In any case, it strikes me as a
mighty poor solution to a problem. I'm hardly going to complain is
somebody wants to collect sequence values generated during a statement,
but I'm not going to delude myself that that is an acceptable solution
to the more general problem.

Personally, I think the INSERT ... RETURNING construct has merit whether
or not blessed by the silly committee (if I didn't, I wouldn't have
crammed blr_store2 down DEC's throat). When we rework the SQL API
(please note the intentionally missing D), we should make provision to
support it properly. But first we should toss around alternatives
(capturing record as resultSet, returning dbkey, etc etc etc).