getGeneratedKeys() returns only "SCOPE_IDENTITY()" when multiple auto generated columns exist

[David Parks]

I have a table with an IDENTITY column, and an AUTO_GENERATE column.

Naturally I need the values of both columns after an insert.

But it appears that a call to getGeneratedKeys() is only returning one
value "SCOPE_IDENTITY()" value: a Long.

Can anyone help me understand this behavior and what I might do to
obtain both values?

[Thomas Mueller]

Hi,

I'm sorry, this is not supported.

May I ask what is your use case? IDENTITY and AUTO_GENERATE are
basically the same, why do you need two such columns?

Regards,
Thomas

[David Parks]

Ah, the old, “write good code” argument. J

 

I was migrating code which previously did things a little differently (previously it was JDO so the column, which is now an IDENTITY, was just an undefined object reference, handled in an undefined way by the underlying implementation) and this was a literal translation of that code which maintained an AUTO_GENERATE column separately since the primary key couldn’t be relied upon to work exactly that way in JDO.

 

Yesterday I refactored the code to remove the extra AUTO_GENERATE column and make use of the PRIMARY KEY IDENTITY column which produces the identical result, as you noted.

 

So it’s fair to use the “write good code, dummie” argument I think. J

 

Dave

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11

[Thomas Mueller]

Hi,

Thanks! H2 internally uses SCOPE_IDENTITY for getGeneratedKeys(), and
changing this would be quite some work. Therefore I would like to
understand how important this issue is.

Regards,
Thomas

[David Parks]

I meant to say, previously, that it’s not important, at least not for this use case, since the two auto-generate columns were identical, the two can logically be replaced by one, which is better code in general (just required a minor refactoring which should have been done anyway).

 

The only use case I could think of (and I’m not promoting this use case as important, myself) would be if you configured an identity and also an auto-generate that doesn’t follow the typical 1..2..3..4 sequence.

 

So if I were you I would *not* worry about this request unless you hear real use-case requirements from other users. I’m sure there are a hundred better things to focus on.

 

A note in the docs might be nice though, since JDBC, and (in my case) Spring both expressly support this feature. Perhaps even an unsupported exception if the user calls Statement.execute(sql, stringArrayOfAutoGenColumnNames) with more than one column name for auto generation.  This way we could easily see that it’s not supported rather than troubleshooting what appears, at first glance, to be a bug.

 

Thanks,

David

 

 

From: h2-da...@googlegroups.com [mailto:h2-da...@googlegroups.com] On Behalf Of Thomas Mueller
Sent: Tuesday, April 05, 2011 12:54 PM
To: h2-da...@googlegroups.com
Subject: Re: getGeneratedKeys() returns only "SCOPE_IDENTITY()" when multiple auto generated columns exist

 

Hi,

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

No virus found in this message.
Checked by AVG - www.avg.com

Version: 10.0.1209 / Virus Database: 1500/3550 - Release Date: 04/04/11

[Maaartin]

On Apr 5, 7:53 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Thanks! H2 internally uses SCOPE_IDENTITY for getGeneratedKeys(), and
> changing this would be quite some work. Therefore I would like to
> understand how important this issue is.

I think I could use something like this... but I'm not sure, if I'm
speaking about exactly the same thing. Consider the following example:

DROP ALL OBJECTS;
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b INT UNIQUE, c
INT);
CREATE TABLE t2 (b INT PRIMARY KEY, c INT);
@loop 5 INSERT INTO t1 (b) VALUES (2*?+1);
@loop 5 INSERT INTO t2 VALUES (?, 2*(?%2)+1);
MERGE INTO t1 (b, c) KEY (b) SELECT * FROM t2;

Now, returning the generated key only leads to something like (null,
null, 6, 7, 8) or more probably just (6, 7, 8), which may be quite
useless as you may need additional columns in order to know which row
is which. I'm not sure if this can be done using
Statement.getGeneratedKeys(), but it works fine using the Firebird-
like syntax

MERGE INTO t1 (b, c) KEY (b) SELECT * FROM t2 RETURNING (t1.a, t1.b);

I wrote Firebird-like, since Firebird supports RETURNING for INSERT
only
http://www.firebirdsql.org/refdocs/langrefupd21-insert.html

IMHO, this RETURNING clause turns updates into queries in a very
useful way, and allows returning all information one can ever need. I
have no idea how complicated is the implementation. I didn't find it
on the roadmap, should I open an issue?

[Thomas Mueller]

Hi,

I will add a feature request for "Support INSERT INTO / UPDATE / MERGE
... RETURNING to retrieve the generated key(s)."

Regards,
Thomas