Dmitry Yemanov wrote:

>"Jim Starkey" <jas@...> wrote:
>
>
>>I built a scheme in blr (blr_store2) to handling this, but it maps very
>>poorly into SQL. To be completely honest, it maps poorly into
>>everything else, too.
>>
>>
>
>Why? The RETURNING clause does this job perfectly. IIRC, this question was
>raised (and partially discussed) in fb-devel. This is what Oracle offers:
>
>PSQL: <statement> [RETURNING <values> INTO <variables>]
>Client: <statement> [RETURNING <values>]
>
>where <statement> is INSERT, UPDATE or DELETE.
>
>
>

There's nothing wrong with that solution other than it isn't SQL. Sure,
anyone can call anything SQL if they wish, but calling it SQL doesn't
make it SQL.

That grumble aside, I don't have any problem with the syntax above. It
does solve the problem of returning an value generated automatically or
by trigger. It was that problem and the type of solution sketched above
that motivated blr_store2. And it's nice, I guess, that somebody made
productive use of blr_store2, which has been sitting around gathering
dust for over two decades now.

My point wasn't that SQL could be extended to handle it, but from an
application programming point of view, it is far more convenient to make
an API call to get a unique value than it is to fetch one from the
database, particularly if you're using an API that doesn't support the
constructs above, which, I believe, include all know Firebird APIs.
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].

Databases are only a piece of an application system that stretches from
a register file in the corner of a piece of silicon that stretches
through copper and fiber back to copper through more silicon to an LCD
panel in front of a busy person with something better to do than master
computer gook. Any prospective feature should be considered not on it's
own merits but as a Lego in the grand scheme of things (if the sponsor
has enough money and is willing to part with it, skip this step).

The unique key generation problem as been around forever. I think I
invented the generator (any historian out there?) which work nicely with
triggers. Oracle called them sequences, which I think is a better
term. Other people invented magic field with mystic side effects. The
problem remained of how to return the suckers to a host program that
needed to know. The scheme above is better than average but doesn't fit
well into established APIs (note: blr has no trouble at all). JDBC has
a particularly dim mechanism, more evidence that all the smart people
have left.

When I see a generally recognized problem that's been around for 20
years with a number of workable but inelegant solutions, none of which
have caught on, it suggests to me that the original idea is in question.

So, once again, my recommendation is to give up hacking in a
non-conforming solution, drop back, and punt. (Paul: can you translate
that into rugby or may non-American football?) Let's expose the
sequence, make everyone happy, and get on with it.

[The above is predicated on the probably unwarranted assumption that the
SQL committee didn't slip it into the standard when I wasn't watching.]

--

Jim Starkey
Netfrastructure, Inc.
978 526-1376