Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (herse.apache.org: domain of keith.irwin@gmail.com
 designates 66.249.82.237 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:mime-version:in-reply-to:references:content-type:message-id:content-transfer-encoding:from:subject:date:to:x-mailer;
        b=jyeYZ4+C+PmMBU48pMvuwS4zXFFFBDIcj77SrKOeUk8l2KmQr7JF5wyBhbu+HYdVWJZ8wmCmIU+VTrBMiS7xmBKPd9mtrr/SdJf2raKDC8nN1hAzLbowhLOhjHGLfrgMgJRhpz9iod4axnlBCmiGsCeZmgJ+6hOo5Ew1IFlexmU=
Mime-Version: 1.0 (Apple Message framework v752.3)
In-Reply-To: <455C7E30.10809@apache.org>
References: <2C264B0D-E601-4A2C-954F-B6FF7B3E2D54@gmail.com>
 <455C7E30.10809@apache.org>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <EB70C833-CEA5-4681-9187-9D45AEB26F73@gmail.com>
Content-Transfer-Encoding: 7bit
From: Keith Irwin <keith.irwin@gmail.com>
Subject: Re: have identity_val_local(), want table.curr_id
Date: Thu, 16 Nov 2006 10:22:56 -0800
To: "Derby Discussion" <derby-user@db.apache.org>


On Nov 16, 2006, at 7:05 AM, Daniel John Debrunner wrote:

> Keith Irwin 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).
>
>> Is there some way to do the above without having to use:
>
> Can you use a single INSERT statement for the items?
>
> insert into items (order_id, other) values
>       (identity_val_local(), 'bar'),
>       (identity_val_local(), 'quux');


Hm. I think I could do that if I construct the SQL statement each  
time I need to, rather than using a constant.  What I'd really love  
is the ability to pass in the name of the table for the  
identity_val_local() function.  That would fix everything and not  
require sequences! ;)

Keith

>
> Dan.
>
>
>