Derby's handling of NULL values

Question

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?

Derby
DB2

score 4 · Accepted Answer · answered Feb 05 '11 at 20:58

If you use the VALUES clause on your INSERT, you don't have to cast the NULL values:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
VALUES ( 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', null, null 
);

This will work like you expect (i.e. the database can determine that the NULLs correspond to an integer and varchar(500). This works in both DB2 and Derby (and should work in pretty much any other database engine, as well).

You can use VALUES with parameter markers as well, without having to CAST them.

The reason that you have to cast when issuing an insert into ... select from statement is because the SELECT portion takes precedence -- the select statement returns certain data types, regardless of whether they are compatible with the table you're trying to insert them in to. If they aren't compatible, you will either get an error (with strongly typed database engines like DB2 <= 9.5) or the engine will do implicit type conversion (when possible).

Thanks Ian. Rephrasing the SQL is not an option, as it was only a descriptive example. I'll have to expect **any** type of SQL in any form. But good hint about the precedence of `SELECT` in `INSERT .. SELECT`. Actually I have many integration tests that run smoothly in my library, just some fail. My hope was to find a **general** solution to this problem... But maybe I'll just keep this as an open issue hoping for Derby to correct that, as the `INSERT .. SELECT` syntax is not very common. — Lukas Eder, Feb 06 '11 at 08:17

score 2 · Answer 2 · edited Nov 17 '11 at 08:11

2

DB2 uses the null indicator... for example

EXEC SQL INSERT INTO TABNAM (FILD1, FILD2) 
                     VALUES (:HOSTVAR1, :HOSTVAR2:NULL-IND2)  END-EXEC.

notice the NULL-IND2 field which can be set to a -1 to indicate that this field in the database table should be null.

Is there a similar indicator for JDBC or Derby?

edited Nov 17 '11 at 08:11

Lukas Eder

211,314
129
689
1,509

answered Nov 16 '11 at 20:50

Joe Cotton

491
4
5

I wasn't aware of such an indicator, nice to know. I'll have to analyse that. To my knowledge, there is no such indicator in JDCB. In particular, named parameters are not available either, in JDBC. But there is an option of explicitly setting `NULL` along with a JDBC type in the [setNull()](http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setNull(int,+int)) method. Not sure about Derby, either – Lukas Eder Nov 17 '11 at 08:12

score 1 · Answer 3 · answered Oct 07 '16 at 16:08

Maybe this solution helps you:

insert into T_AUTHOR (
    ID, 
    FIRST_NAME, 
    LAST_NAME, 
    DATE_OF_BIRTH, 
    YEAR_OF_BIRTH, 
    ADDRESS
) select 
    1000, 
    'Lukas', 
    'Eder', 
    '1981-07-10', 
    (select YEAR_OF_BIRTH from T_AUTHOR where true = false), 
    (select ADDRESS from T_AUTHOR where true = false) 
from SYSIBM.SYSDUMMY1

This method does not require explicit cast of null, because inner select will return null with required type.

score 1 · Answer 4 · answered Feb 05 '11 at 15:30

1

What if you leave the column values as question-mark substitution values in your PreparedStatement, and then set the values at runtime using PreparedStatement.setObject(N, null)?

In general, your library will prefer to provide column values by substituting parameter values, to handle issues like quotation marks in strings, datatype conversions, etc., and I think that this general substitution mechanism should handle your null value problems as well.

answered Feb 05 '11 at 15:30

Bryan Pendleton

16,128
3
32
56

Thanks for the input Bryan. I don't know why I left the bind variables away. Of course my library will always use bind variables to improve performance / security, etc, instead of inlining the values directly to SQL. I'll adapt the question. – Lukas Eder Feb 05 '11 at 15:33
No worries. It's possible that you're encountering https://issues.apache.org/jira/browse/DERBY-1938, which was only recently fixed. Are you using the latest Derby (10.7)? If not, give it a try and see if the setObject(null) is working better now. – Bryan Pendleton Feb 05 '11 at 19:59
Yes, I'm using Derby 10.7.1.1. But actually that's a good hint with `setObject(null)`. I had a combination of two errors, first the `Connection.prepareStatement()` call didn't accept `null` literals or `cast(? as integer)` expressions (if integer is the wrong type). When I simply prepared `?` for the null value, then my library called `PreparedStatement.setNull(N, Object.class)`, which didn't work either. But `setObject(N, null)` seems to work. I'll double check... – Lukas Eder Feb 06 '11 at 08:33
Hmm, that works in this very case, but is not a general solution. If I simply `SELECT ? FROM SYSIBM.SYSDUMMY1`, then Derby complains that ? is not allowed in the select clause. It's really a tough problem for a SQL-dialect-abstracting framework to solve... – Lukas Eder Feb 06 '11 at 08:49

Derby's handling of NULL values

4 Answers4

Linked