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 (asf.osuosl.org: local policy)
Message-ID: <44CF6973.5090808@decoursey.net>
Date: Tue, 01 Aug 2006 09:47:15 -0500
From: Paul J DeCoursey <paul@decoursey.net>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Retrieving the identity column value after an insert
References: <E1G7uMB-0002Od-Mq@www33a.your-server.co.za>
In-Reply-To: <E1G7uMB-0002Od-Mq@www33a.your-server.co.za>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mark Ashworth wrote:
> Hi,
>
> The problem is when you want to insert rows into other tables for each of
> the rows that have been inserted into to the user's table and those tables
> have identity fields. In this case it seems that without a variable to hold
> the userid, the IDENTITY_VAL_LOCAL() returns the new identity value from
> those other tables (which is to be expected).
>
> Regards,
> Mark P Ashworth
>
> -----Original Message-----
> From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM] 
> Sent: 01 August 2006 03:27 PM
> To: Derby Discussion
> Subject: Re: Retrieving the identity column value after an insert
>
> Mark Ashworth wrote:
>   
>> Good Day,
>>
>>  
>>
>> I would like to run the following SQL in ij
>>
>>  
>>
>> INSERT INTO users (name) VALUES ('test');
>>
>>  
>>
>> And after the insert statement use the inserted id value to complete 
>> other inserts in other tables.
>>
>>  
>>
>> INSERT INTO attr (user_id, value) VALUES( INDENTITY_VAL_LOCAL(), 'test 
>> attr');
>>
>> INSERT INTO attr (user_id, value) VALUES( INDENTITY_VAL_LOCAL(), 'test 
>> attr2'); <!-Problem here because the IDENTITY_VAL_LOCAL() now contains 
>> the id that was generated in the previous statement.
>>
>>  
>>
>> How do I assign the identity value to a variable?
>>     
>
> Hello Mark,
>
> I don't know how to assign the identity value to a variable, but if you 
> can use multiple row INSERT statements, you should be able to do what 
> you want.
>
> INSERT INTO users (name) VALUES ('test');
> INSERT INTO attr (user_id,value) VALUES
> 	(IDENTITY_VAL_LOCAL(), 'test attr'),
> 	(IDENTITY_VAL_LOCAL(), 'test attr2');
>
> The multiple row insert will not update the value of IDENTITY_VAL_LOCAL.
> Note that not all databases support this form of the INSERT statement.
>
>
>
>
> Regards,
>   
try SELECT IDENTITY_VAL_LOCAL() FROM users, I think that the 
IDENTITY_VAL_LOCAL() is per table, I have some stored procedures that do 
this and it seems to work.  I'm not for certain that it is correct.

Paul