SCOPE_IDENTITY() returning NULL

[Gili]

Hi,

I am invoking the following statements using Flyway:

INSERT INTO permission VALUES();

INSERT INTO users (version, last_modified, insert_permission_id) VALUES (0, NOW(), SCOPE_IDENTITY());

permission has a single IDENTITY column. I am expecting SCOPE_IDENTITY() to return the inserted ID in the second statement. Any ideas why this is not happening?

I am using H2 1.3.176.

Thanks,

Gili

[Noel Grandin]

On 2014-07-02 08:22 AM, Gili wrote:
> permission has a single IDENTITY column. I am expecting SCOPE_IDENTITY() to return the inserted ID in the second
> statement. Any ideas why this is not happening?
>

Do you have a standalone test case?

This is working for me:

create table permission(id identity);
create table users(version int, permission_id int, foreign key (permission_id) references permission(id));
insert into permission values();
insert into users values (0, scope_identity());

[cowwoc]

Hi Noel,

Let me know if this works for you:

CREATE TABLE permission (id IDENTITY);
CREATE TABLE users (id IDENTITY, version INT NOT NULL, last_modified
TIMESTAMP NOT NULL,
insert_permission_id BIGINT NOT NULL);

INSERT INTO permission VALUES();
INSERT INTO users (version, last_modified, insert_permission_id) VALUES
(0, NOW(), SCOPE_IDENTITY());

PS: Replacing SCOPE_IDENTITY() with IDENTITY() fixes the problem but
it's not clear why.

And a follow-up question: SCOPE_IDENTITY() says "changes [...] within
Java functions are ignored". What are "Java functions" in this context?
I am only aware of triggers.

Thanks,
Gili

[Noel Grandin]

SCOPE_IDENTITY() appears to only reflect sequence values from the current statement.

I'll update the docs to this effect.

[cowwoc]

Thank you.

I assume SCOPE_IDENTITY() was added for compatibility with other
databases. Is this behavior (only reflecting sequence values) consistent
with that? Or do you need to open a bug report to improve its
compatibility? (I'm happy to simply use IDENTITY() but others might care)

Gili

[Noel Grandin]

I don't know since it predates my involvement with H2, but I'm guess it was added for compat with SQL-Server.
see: http://msdn.microsoft.com/en-us/library/ms190315.aspx

Based on that description, it seems to be working largely correctly.

[cowwoc]

Makes sense. Thanks!

Gili

[Gili]

On second thought I think this issue should be reopened.

1. The microsoft documentation doesn't say this function is limited to sequences. I think it should work on all tables.
2. I need a way to get the AUTO_INCREMENT column of a row I just inserted, but my triggers are causing IDENTITY() to return the wrong value. All the forums recommend using SCOPE_IDENTITY() for this purpose, but it doesn't work for H2.

Is there a workaround I can use in the meantime?

Thanks,

Gili

[Gili]

To be clear, I believe my definition of SCOPE_IDENTITY() is a function that returns the same value as http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()

Gili

[Gili]

Noel, I think you were wrong about this only working for sequences.

I ran into http://stackoverflow.com/a/12319823/14731 which states that SET resets the value of SCOPE_IDENTITY() and provides a workaround (which worked for me).

Is there an open bug report for the fact that SET should not reset SCOPE_IDENTITY()?

Gili

On Friday, September 5, 2014 12:52:15 AM UTC-4, Gili wrote:

[Gili]

I filed a bug report at https://code.google.com/p/h2database/issues/detail?id=585

Gili