Derby NULL Processing

Question

I am new to Derby, and I noticed that I am facing similar issues as when using DB2 RDBMS values ​​with respect to null values. The Derby documentation states that null must be of the type associated with it (that DB2 finally got rid of version 9.7):

http://db.apache.org/derby/docs/10.7/ref/crefsqlj21305.html

Now I'm trying to find a general solution to this problem here, as this will be part of my jOOQ database abstraction library. The following example just documents the problem. Think of another (more complex) example. The following does not 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 

And it does not (which is what jOOQ actually does):

 insert into T_AUTHOR ( ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) select ?, ?, ?, ?, ?, ? from SYSIBM.SYSDUMMY1 

Since the two null values ​​do not have the 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, accordingly,

 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 dropped is unknown:

• In this example, types can be obtained from the insert clause, but this may be difficult or impossible for more general use cases.
• In other examples, I could choose any type for translation (for example, always casting to int ), but this would not work in this example, since you cannot put the value of cast(null as int) in ADDRESS .
• With HSQLDB (another candidate for this problem), I can simply write cast(null as object) , which will work in most cases. But Derby is not of type object .

This problem still annoyed me DB2, and I have not yet found a solution. Does anyone know of a stable and general solution to this problem for any of these RDBMSs?

• Derby
• DB2

Answer 1

If you use the VALUES clause on your INSERT, you do not need to specify 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 as you expect (i.e. the database can determine that NULL matches an integer and varchar (500). This works in both DB2 and Derby (and should work in almost any other database engine )

You can also use VALUES with parameter markers without doing them.

The reason you have to complete the issue of the insert into ... select from statement is because the SELECT part takes precedence - the select statement returns certain data types, regardless of whether they are compatible with the table you are trying to insert into. If they are incompatible, you will either receive an error message (with strongly typed database engines like DB2 <= 9.5), or the engine will do implicit type conversion (when possible).

Answer 2

DB2 uses a null indicator ... for example

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

note the NULL-IND2 field, which can be set to -1 to indicate that this field in the database table should be zero.

Is there a similar indicator for JDBC or Derby?

Answer 3

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

In general, your library will prefer to provide column values, replacing parameter values, handle issues such as quotation marks in strings, data type conversions, etc., and I think this general substitution mechanism should also handle your problems with a null value.

Answer 4

Perhaps this solution will help 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 an explicit expression from null , because inner select returns null with the required type.