Rod Taylor <rbt@rbt.ca> writes:
> Yeah.. JBoss is very annoying in this regard. A temporary solution seems
> to be to use BEFORE triggers to force the sequence to be used for the
> default value. You could also do this with an INSTEAD rule (something
> like the below):
>
> CREATE OR REPLACE RULE rulename AS ON INSERT
> TO tablename
> DO INSTEAD
> INSERT INTO tablename
> ( id, col1, ...)
> VALUES ( DEFAULT, NEW.col1, ...);
That's a good workaround for 3.2.1. As regards the upcoming 3.2.2, I
just found out that it has PostgreSQL-specific code to handle this,
though it's an incomplete fix. To summarize:
- add <entity-command>postgresql-fetch-seq</entity-command> to each affected entity (or the <defaults> section) in
jbosscmp-jdbc.xml; this resolves the "failed to insert null" problem and also makes sure JBoss knows the id of the
newlycreated row.
- apply my auto_increment patch
- replace the bogus auto-increment template in the PostgreSQL and PostgreSQL 7.2 mappings in standardjbosscmp-jdbc.xml
withthe following:
<auto-increment-template>?1 AUTO_INCREMENT</auto-increment-template>
the current template (?1) works if you set the corresponding <cmp-field>'s <sql-type> to SERIAL - but if you define
arelation that involves that field, the other endpoint will also be defined as SERIAL! Obviously that's not a recipe
forsuccess.
> AUTO_INCREMENT is non-standard (MySQL only?), however the SQL200X
> proposals do have support for the more common IDENTITY syntax which can
> accomplish the same job as well as many others. (PostgreSQL does NOT
> have the general identity implementation yet)
the IDENTITY syntax is very similar to AUTO_INCREMENT, in fact you can
apply s/auto_increment/identity/ to my patch and have a useful subset
of it :) JBoss already knows about IDENTITY, since Hypersonic SQL and
MS SQL both support it.
(interestingly, JBoss doesn't seem to know that DB/2 also supports it)
> What you're looking for is the ability to force the column to use the
> IDENTITY even when the client provides a specific value:
>
> CREATE TABLE test(col integer GENERATED ALWAYS AS IDENTITY);
as mentioned above, that's no longer a problem with JBoss 3.2.2.
> See sections 10.22, 10.23, 11.3, and 11.4 of the SQL200X working draft
> for full details.
>
> ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf
I believe this is more up to date:
ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FDIS/4FDIS1-02-Foundation-2003.pdf
DES
--
Dag-Erling Smørgrav - des@des.no