db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dan Scott" <deni...@gmail.com>
Subject Re: have identity_val_local(), want table.curr_id
Date Thu, 16 Nov 2006 02:22:47 GMT
On 15/11/06, Keith Irwin <keith.irwin@gmail.com> wrote:
> Folks---
>
> I want to run something similar in a single transaction (just a loop
> that runs each query one after another then commits):
>
> Assuming table (shorthand):
>    order (order_id autoincrement, name)
>    items (item_id autoincrement, order_id, name)
>
> And queries:
>    insert into orders (name) values ('foo');
>    insert into items (order_id, other) values (identity_val_local(),
> 'bar');
>
> This works fine.   It picks up the order_id inserted automatically
> (via autoincrement) on the order table and uses it in the items table
> foreign key.
>
> However, when I do the following:
>
>    insert into orders (name) values ('foo');
>    insert into items (order_id, other) values (identity_val_local(),
> 'bar');
>    insert into items (order_id, other) values (identity_val_local(),
> 'quux');
>
> I get a constraint error because the second identity_val_local() call
> refers to the recent item table autoincrement, and not the order
> autoincremented key (which makes sense reading the docs).
>
> With Oracle, or PostgreSQL, I can do the following:
>
>    insert into orders (name) values ('foo');
>    insert into items (order_id, other) values (order_seq.currval(),
> 'bar');
>    insert into items (order_id, other) values (order_seq.currval(),
> 'quux');
>
> and things work nicely.  I don't have to run selects, or use JDBC id
> generation key retrievals to make this stuff work.
>
> Nor do I have to add a fancy callback mechanism to my nice little
> query API just to get that value out and then back in.
>
> Is there some way to do the above without having to use:
>
>      stmt = conn.prepareStatement(q.getSql(),
> Statement.RETURN_GENERATED_KEYS);
>
> then snag the id (rs.next().get(1)) to insert into my next
> transaction?  What I want is to do the whole thing inside the SQL
> statements, as I can with all the other Databases I've every used.
> (I don't especially need help doing it the harder way. ;)
>
> Anyway, thanks for any tips.
>
> Regards,
>
> Keith

Hi Keith:

It sounds like you're asking for support for SEQUENCE objects.

There's a JIRA issue open
(http://issues.apache.org/jira/browse/DERBY-712) that requests the
addition of this feature; I would suggest voting for it if you're
interested in raising the priority of this feature.

Dan

Mime
View raw message