db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Fri, 13 May 2005 14:57:33 GMT
Hi Paul,

Following is what Derby's Reference Guide says about IDENTITY_VAL_LOCAL

Derby supports the IDENTITY_VAL_LOCAL function.


The IDENTITY_VAL_LOCAL function is a non-deterministic function that
returns the most recently assigned value for an identity column, where
the assignment occurred as a result of a single row INSERT statement
using a VALUES clause.

The IDENTITY_VAL_LOCAL function has no input parameters. The result is
a DECIMAL(31,0), regardless of the actual data type of the
corresponding identity column.

The value returned by the IDENTITY_VAL_LOCAL function is the value
assigned to the identity column of the table identified in the most
recent single row INSERT statement.The INSERT statement must contain a
VALUES clause on a table containing an identity column. The assigned
value is an identity value generated by Derby. The function returns a
null value when a single row INSERT statement with a VALUES clause has
not been issued for a table containing an identity column.

The result of the function is not affected by the following:
• A single row INSERT statement with a VALUES clause for a table without an
identity column
• A multiple row INSERT statement with a VALUES clause
• An INSERT statement with a fullselect

So, as long as there is a single row INSERT statement with a VALUES
clause on the table with the generated always column, this function
should work fine for what you are trying to achieve.

Also, there are quite a few test cases for this function in
org.apache.derbyTesting.functionTests.tests.lang.autoincrement.sql to
see some examples of this function.

Hope this helps,
On 5/12/05, Paul J. Lucas <pauljlucas@mac.com> wrote:
>        If I have a tables like:
>                CREATE TABLE location (
>                    location_id    INT NOT NULL PRIMARY KEY
>                                   GENERATED ALWAYS AS IDENTITY,
>                    path           VARCHAR(255) NOT NULL UNIQUE
>                );
>                CREATE TABLE image (
>                    image_id       INT NOT NULL PRIMARY KEY
>                                   GENERATED ALWAYS AS IDENTITY,
>                    file_name      VARCHAR(64) NOT NULL,
>                    location_id    INT REFERENCES location,
>                    date_time      TIMESTAMP NOT NULL
>                );
>        I want to be able to insert a new location, obtain the
>        auto-generated location_id, and use that in an insert of a new
>        image.
>        If I do the fisrt insert, then:
>                SELECT IDENTITY_VAL_LOCAL() FROM location;
>        then use that value for the second insert image.location_id,
>        will I be guaranteed to get the right value for location_id,
>        i.e., the one for the first insert and not some other insert
>        that may have happened in between my firt insert and the
>        SELECT?
>        Pharsed another way, are the operations of:
>                INSERT INTO location ... ;
>                SELECT IDENTITY_VAL_LOCAL() FROM location;
>                INSERT INTO image ... ;
>        atomic so that the value I use in the second insert will be the
>        correct one?
>        If not, how do I do what I want?
>        When I was doing this sort of thing using Oracle, Oracle has
>        "sequences" that can be used for this purpose.  I suppose that
>        for Derby I could have a seperate table that's just:
>                CREATE TABLE location_id (
>                    next_id INT NOT NULL PRIMARY KEY
>                            GENERATED ALWAYS AS IDENTITY,
>                    junk    INT
>                );
>        then, before I do my first insert, do:
>                INSERT INTO location_id (junk) VALUES (0);
>                SELECT IDENTITY_VAL_LOCAL() FROM location_id;
>        then use *that* location_id for both of my inserts.  But is this
>        necessary?
>        Thanks.
>        - Paul
View raw message