db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mark Ashworth" <ma...@cibecs.com>
Subject RE: Retrieving the identity column value after an insert
Date Tue, 01 Aug 2006 13:32:25 GMT
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,
-- 
Kristian

> 
>  
> 
> Kind regards,
> 
> Mark Ashworth
> *Senior Software Developer*
> 
> Cell: +27 84 235 3554
> Email: *marka@cibecs.com <mailto:marka@cibecs.com>*
> 
>  
> 
> 	
> 
> Tel: +27 (011) 253 7600
> Fax: 0866 800 571
> **www.cibecs.com <http://www.cibecs.com>**
> 
> 	
> 
> 	
> 
>  
> 
> 	
> 
> * Everything in this e-mail and attachments relating to the official 
> business of CIBECS (Pty) Ltd (Reg. Number 1981/05934/07) is proprietary 
> to the company. It is confidential, legally privileged and protected by 
> law. CIBECS does not own and endorse any other content. Views and 
> opinions are those of the sender unless clearly stated as being that of 
> CIBECS. The person addressed in the e-mail is the sole authorised 
> recipient. Please notify the sender immediately if it has 
> unintentionally reached you and do not read, disclose or use the content 
> in any way. Whilst all reasonable steps are taken to ensure the accuracy 
> and integrity of information and data transmitted electronically and to 
> preserve the confidentiality thereof, no liability or responsibility 
> whatsoever is accepted if information or data is, for whatever reason, 
> corrupted or does not reach its intended destination.
> 
>  
> 



Mime
View raw message