CODEWITHSUNDEEP
javasqljdbc
minus
minus

Reputation: 320

Usage of Types.NULL in JDBC

I am wondering about the intended usage of the Types.NULL constant in the JDBC. I am guessing from the javadoc that a literal value in a SQL statement can have the type Types.NULL.

For example, the null literal in this statement, could have the type Types.NULL:

SELECT * FROM integer_table WHERE integer_column = null

I have also seen examples where the null literal is assigned the type Types.CHAR and then changed to Types.Integer when the type of integer_column is discovered by the SQL processor. Are both approaches correct? Is one more correct than the other?

I am also guessing that you cannot have a column that is the type Types.NULL because it is not a 'SQL Type'. Is this correct?

Are there any other usages?

NOTE: I am aware of how null comparisons behave in SQL, but this is just an example and is not relevant to my question.

Upvotes: 3

Views: 5213

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109045

In a query which has ? IS NULL as the condition, the parameter can be described as a NULL-type, and in that case the JDBC driver can report it as a Types.NULL.

With regard to NULL SQL:2011 says:

Every data type includes a special value, called the null value, sometimes denoted by the keyword NULL. This value differs from other values in the following respects:

  • Since the null value is in every data type, the data type of the null value implied by the keyword NULL cannot be inferred; hence NULL can be used to denote the null value only in certain contexts, rather than everywhere that a literal is permitted.

So the NULL type is not really a type, but all types at once, on the other hand given my example (a parameter in an ? IS NULL), you need something to indicate that the value can be anything that is null or not null.

This for example is the case for Jaybird (the Firebird JDBC driver); disclosure: I am one of the Jaybird developers. When a parameter is of type NULL, a call to any setXXX with any value will make that parameter not null except for a value of null (or a call to setNull()) . Also some (most?) drivers will allow a call to setNull() with the specific sql type and also with Types.NULL.

Upvotes: 2

Related Questions