Is there any way to user NEXTVAL( ...) with AUTO_INCREMENT PK?
exappl088
I have all my tables setup with int AUTO_INCREMENT PRIMARY KEY
and in some cases my transactions involve inserting linked records in
different tables and i want to be able to setup iBatis's sqlmap insert
statements.
E.G i want to begin a transaction, get the NEXTVAL( ) key for the
table of the audit
text messages, insert the msg record into the audit message table
and then insert the audit record into the Audit Table with the FK of
the message
record in the audit record. Most of my audit records don't need a
audit msg
record.
Why is there not some "alias" for the H2 generated sequence that is
created
for an AUTO_INCREMENT PK column, that i can use in NEXTVAL()?
E.G why can't i use something like
NEXTVAL('AUTO_INCREMENT.TABLENAME')
or some other syntax that would reference the proper sequence for that
table.
I also was a bit puzzled when i noticed that when i do a
SELECT IDENTITY() FROM AUDITMSG;
i get back a result set that has as many records, all with the same
IDENTITY() value,
as the AUDITMSG table currently contains, and as the table grows, so
does the
result set? Is this what that select is supposed to do? i was
expecting one row;
The multiple rows causes a iBatis to try to return a List in place of
an Integer object.
skyfox01_99
> I also was a bit puzzled when i noticed that when i do a
> SELECT IDENTITY() FROM AUDITMSG;
> i get back a result set that has as many records, all with the same
> IDENTITY() value,
Don't specify a FROM clause (i.e. just use "SELECT IDENTITY()"). The
resultset will contain a single row.
Thomas Mueller
> Why is there not some "alias" for the H2 generated sequence
> that is created for an AUTO_INCREMENT PK column,
> that i can use in NEXTVAL()?
Is there something like this in another database? In H2,
auto-increment columns use a sequence, and you could query the
sequence directly, but that is also non-standard.
You could create a sequence of course.
Regards,
Thomas
exappl088
On Aug 30, 7:24 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
AUTO_INCREMENT is a super standard (vs SQL 2003) extension
anyway, and the whole point of AUTO_INCREMENT in the first place
is that the database creates the sequence just so i don't have to
create
or manage it, it seem rather illogical NOT to have a way to reference
it
with a system generated alias, especially since sequences essentially
exist in their own namespace anyway (as they can only be referenced
in limited specific context's in SQL syntax) so it would not seem that
an auto generation of an alias would create a naming conflict with
explicit sequences with well a well chosen prefix, say "AUTO_" or
some such.
AUTO_INCREMENT also is implemented to account for the
fact that a PK column can be INT, or SMALLINT (i use both) as
well as BIGINT/IDENTITY and so i don't have to deal with the
type conflicts in my java code due to an explicit sequence returning
Long objects for a Short key, etc, etc. I.E. the great value of
AUTO_INCREMENT is ease of use for the (me) the programmer.
Using the excuse "what other DB's" do with this non-standard extension
seems, IMHO, irrelevant, and if H2 shows how complete support
for AUTO_INCREMENT can be done and the ease of use it adds to SQL
programming, perhaps that will lead to your design becoming the
standard (likely due to imitation by others); which would be promoted
by documenting this as an "ease of use" feature, so as to highlight
how it
simplifies the java code.
IMHO, AUTO_INCREMENT is better that explicit sequences because
of "locality" of the syntax within CREATE TABLE definition, which
makes
the table definition more self documenting when reading it later ( i
guess
i have spent too much time maintaining code written by myself/
others).
Perhaps you might consider adding alternate syntax of
AUTO_INCREMENT[('sequencename')], which would essentially allow
creation of a sequence within the create table, but still retain the
benefit
of not having to deal with the type differences of a explicit
sequence.
regards, John
Thomas Mueller
> sequences essentially
> exist in their own namespace anyway
Yes, but sequence names could collide with other sequence names. One
solution is to use some rule like: AUTO_<tableName> or
AUTO_<schemaName>_<tableName>. This is how it worked in H2 up to some
point. Until it turned out it's a problem sometimes (I don't remember
the details, but it had to do with renaming objects, and RUNSCRIPT to
import data from another database). Anyway, now H2 uses the prefix
'SYSTEM_SEQUENCE' and a UUID. Example:
SYSTEM_SEQUENCE_7167FEA7_4DB8_44CE_92E9_0CE9FB883403. A collision is
very very unlikely. See also
http://www.h2database.com/html/advanced.html#uuid
> "ease of use" feature
There is CALL IDENTITY()
http://www.h2database.com/html/functions.html#identity
> Perhaps you might consider adding alternate syntax of
> AUTO_INCREMENT[('sequencename')], which would essentially allow
> creation of a sequence within the create table, but still retain the
> benefit
> of not having to deal with the type differences of a explicit
> sequence.
There is a 'standard' way to do that:
create sequence abc;
create table test(id bigint default next value for abc);
Regards,
Thomas