David Cuthbert
unread,Jun 9, 2010, 12:56:08 AM6/9/10Sign in to reply to author
Sign in to forward
You do not have permission to delete messages in this group
Sign in to report message
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
to H2 Database
I've searched around a bit on both the groups and the wider web, but
haven't seen this question posed; apologies if I've missed something.
I have a table which contains audit columns which, on the Oracle side,
are populated by a trigger. Logically, these columns should always be
populated; thus, we put NOT NULL constraints on them. The H2 DDL
looks like:
CREATE TABLE foo(
id NUMBER(38) PRIMARY KEY NOT NULL,
user VARCHAR(8) NOT NULL)
CREATE TRIGGER audit_foo
BEFORE INSERT
ON foo
FOR EACH ROW
CALL "org.kanga.h2.AuditTrigger"
AuditTrigger just blindly injects a dummy username into the Object[]
newRow values in its fire() method:
public void fire(Connection conn, Object[] oldRow, Object[]
newRow)
throws SQLException
{
newRow[1] = "TestUser";
}
Alas, H2 rejects any insertion without user:
INSERT INTO foo(id) VALUES(1)
NULL not allowed for column "USER"; SQL statement:
insert into foo(id) values(1) [90006-136] 90006/90006 (Help)
org.h2.jdbc.JdbcSQLException: NULL not allowed for column "USER"; SQL
statement:
insert into foo(id) values(1) [90006-136]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.table.Column.validateConvertUpdateSequence(Column.java:
294)
at org.h2.table.Table.validateConvertUpdateSequence(Table.java:
608)
...
If I drop the NOT NULL on user, it works as expected:
INSERT INTO foo(id) VALUES(1);
COMMIT;
SELECT * FROM foo;
ID USER
1 TestUser
I've also tried "SET MODE Oracle" to try to get the Oracle behavior,
to no avail.
Any ways around this short of dropping the NOT NULL constraint? (Note
that this workaround is not a big deal for me; I'm just using H2 as a
base for our unit tests, and it's been wonderful in this regard.)