db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Retrieving the identity column value after an insert
Date Tue, 01 Aug 2006 14:08:57 GMT
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).

Hi again,

I see. My proposal won't work for that scenario.

Do you need to use ij, or can you use JDBC instead?

It should be possible to code a solution using getAutoGeneratedKeys(), 
but that won't help you a bit if you must use ij of course.

If you don't get any usable solution from the list, feel free to add an 
improvement/feature request in Jira ([1]) for the ij tool.
BTW, are you able to do what you need to do in other tools?



Sorry I can't help more,
-- 
Kristian

[1] http://issues.apache.org/jira/browse/DERBY


> 
> 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,


Mime
View raw message