You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Mark Ashworth <ma...@cibecs.com> on 2006/08/01 13:08:01 UTC

Retrieving the identity column value after an insert

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?

 

Kind regards,

Mark Ashworth
Senior Software Developer

Cell: +27 84 235 3554
Email:  <ma...@cibecs.com> marka@cibecs.com






 

Tel: +27 (011) 253 7600
Fax: 0866 800 571
 <http://www.cibecs.com> 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.

 


Re: Retrieving the identity column value after an insert

Posted by Paul J DeCoursey <pa...@decoursey.net>.
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



Re: Retrieving the identity column value after an insert

Posted by Stanley Bradbury <St...@gmail.com>.
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
>   
=======   SNIP ========
A possible way to get around the problem is to use a temporary table to 
persist the values then perform the other table inserts by selecting 
from the temp/work table.

HTH


Re: Retrieving the identity column value after an insert

Posted by Kristian Waagan <Kr...@Sun.COM>.
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,


RE: Retrieving the identity column value after an insert

Posted by Mark Ashworth <ma...@cibecs.com>.
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 <ma...@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.
> 
>  
> 



Re: Retrieving the identity column value after an insert

Posted by Kristian Waagan <Kr...@Sun.COM>.
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 <ma...@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.
> 
>  
>