I am new to Derby and I noticed that I face similar problems as when using the DB2 RDBMS as far as null
values are concerned. The Derby documentation states, that a null
value must have a type associated with it (something that DB2 finally got rid of in version 9.7):
http://db.apache.org/derby/docs/10.7/ref/crefsqlj21305.html
Now, I am trying to find a general solution to this problem here as this will be a part of my database abstraction library jOOQ. The below example just documents the problem. Think of any other (more complex) example. The following doesn't work:
insert into T_AUTHOR (
ID, FIRST_NAME, LAST_NAME,
DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS)
select
1000, 'Lukas', 'Eder',
'1981-07-10', null, null
from SYSIBM.SYSDUMMY1
Neither does this (which is what is actually done by jOOQ):
insert into T_AUTHOR (
ID, FIRST_NAME, LAST_NAME,
DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS)
select ?, ?, ?, ?, ?, ?
from SYSIBM.SYSDUMMY1
Because the two null
values have no type associated with it. The solution would be to write something like this:
insert into T_AUTHOR (
ID, FIRST_NAME, LAST_NAME,
DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS)
select
1000, 'Lukas', 'Eder',
'1981-07-10', cast(null as int), cast(null as varchar(500))
from SYSIBM.SYSDUMMY1
Or like this, respectively
insert into T_AUTHOR (
ID, FIRST_NAME, LAST_NAME,
DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS)
select
?, ?, ?, ?, cast(? as int), cast(? as varchar(500))
from SYSIBM.SYSDUMMY1
But very often, in Java, the type that null
should be cast to is unknown:
- In this example, the types could be derived from the insert clause, but that might prove to be complicated or impossible for more general use-cases.
- In other examples, I could just pick any type for the cast (e.g. always casting to
int
), but that wouldn't work in this example, as you cannot put a cast(null as int)
value into ADDRESS
.
- With HSQLDB (another candidate for this problem), I can simply write
cast(null as object)
which will work in most cases. But Derby does not have an object
type.
This problem has been annoying me with DB2 before and I haven't found a solution yet. Does anyone know of a stable, and general solution to this problem for any of these RDBMS?
See Question&Answers more detail:
os