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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
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,
