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 mi...@mail.ru on 2005/02/14 15:34:59 UTC
IDENTITY_VAL_LOCAL
Greetings!
I'm experiencing a problem using the IDENTITY_VAL_LOCAL function. Any
query with this function fails with an SQLException with message:
Unexpected token: IDENTITY_VAL_LOCAL in statement ........
I have the identity column in one of my tables, on which I successfully
perform single-record inserts. Well, it seems it just doesn't even
parse! I'm really upset about it. I have to use a workaround like:
SELECT MAX(TABLE_ID) FROM MY_TABLE;
instead of IDENTITY_VAL_LOCAL().
Can anyone help?
I'm using:
JRE 1.4.2
Apache DERBY v.1.7.3
Thanks!
Michael.
Re: IDENTITY_VAL_LOCAL
Posted by Stanley Bradbury <br...@Mutagen.Net>.
michaelbaranov@mail.ru wrote:
>Greetings!
>
>I'm experiencing a problem using the IDENTITY_VAL_LOCAL function. Any
>query with this function fails with an SQLException with message:
>
> Unexpected token: IDENTITY_VAL_LOCAL in statement ........
>
>I have the identity column in one of my tables, on which I successfully
>perform single-record inserts. Well, it seems it just doesn't even
>parse! I'm really upset about it. I have to use a workaround like:
>
>SELECT MAX(TABLE_ID) FROM MY_TABLE;
>
>instead of IDENTITY_VAL_LOCAL().
>
>Can anyone help?
>
>I'm using:
>JRE 1.4.2
>Apache DERBY v.1.7.3
>
>Thanks!
>Michael.
>
>
>
>
>
From the docuementation I am guessing that an insert must have just
been performed on that table before using this function, unlike the
MAX() function that performs a table scan of any numeric column and
returns the highest value regardless of when it was inserted.
From the Docs:
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.
Re: IDENTITY_VAL_LOCAL
Posted by Mamta Satoor <ma...@Remulak.Net>.
michaelbaranov@mail.ru wrote:
> Greetings!
>
> I'm experiencing a problem using the IDENTITY_VAL_LOCAL function. Any
> query with this function fails with an SQLException with message:
>
> Unexpected token: IDENTITY_VAL_LOCAL in statement ........
>
> I have the identity column in one of my tables, on which I successfully
> perform single-record inserts. Well, it seems it just doesn't even
> parse! I'm really upset about it. I have to use a workaround like:
>
> SELECT MAX(TABLE_ID) FROM MY_TABLE;
>
> instead of IDENTITY_VAL_LOCAL().
>
> Can anyone help?
>
> I'm using:
> JRE 1.4.2
> Apache DERBY v.1.7.3
>
> Thanks!
> Michael.
Hi Michael,
Following is an example of how you would use IDENTITY_VAL_LOCAL() in your sql. You can find
more examples in functionTests/tests/lang/autoincrement.sql
ij> create table t1 (x int, s1 int generated always as identity);
0 rows inserted/updated/deleted
ij> insert into t1 (x) values (1);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1
ij> insert into t1 (x) values (2);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
2
Also, you can find documentation on IDENTITY_VAL_LOCAL at following location in the Reference Manual
http://incubator.apache.org/derby/manuals/reference/sqlj82.html#HDRIDENTITYVALLOCAL.
Hope this helps,
Mamta