db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Keith Irwin <keith.ir...@gmail.com>
Subject have identity_val_local(), want table.curr_id
Date Thu, 16 Nov 2006 01:45:54 GMT
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

Mime
View raw message