CODEWITHSUNDEEP
java.netdatabasenull
prabhahar
prabhahar

Reputation:

How to Pass NULL Data Type?

What is the datatype for NULL when passing that value for no data into a database?

Upvotes: 4

Views: 17709

Answers (13)

MAbraham1
MAbraham1

Reputation: 1768

Prabhahar, each type of database driver has its own way of handling NULL. You will have to examine the driver API for the specific database.

For example if you are using the Java Derby database, simply pass in the Java native type, null as shown in Ian Bjorhovde's answer to "Derby's Handling of 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 
);

Here is another null example of JDBC:Inserting null to Integer column: 

pst.setNull(4, java.sql.Types.INTEGER);

Upvotes: 0

user250343
user250343

Reputation: 1183

Usually SQL NULL does not have a type associated with it. However there are exceptions. Database engines postgresql and derby (javadb) require that null has a type. In other words they do not support untyped null. So query conditions like NULL IS NULL may fail. Here, NULL must be given a type, the expected type of the target that processes the NULL value. In this case this appears silly because there is no target and this can be counterproductive.

See CAST function: -- you must cast NULL as a data type to use it

See Queries with guarded null Parameter fail and Add support for setObject(arg, null)

Please vote for these issues so that the odd database engines change their ways.

Upvotes: 0

nvogel
nvogel

Reputation: 25534

In SQL a NULL is a "mark" (something other than a value) that can apply to any SQL type. So it is orthogonal to type.

Upvotes: 1

antoni.rasul
antoni.rasul

Reputation: 668

Actually, in PowerShell comparing $null -eq $null gives False. Also, -not $null will give you True, so here it seems to be reprezented as False. I know, PowerShell might not be a good example, but still :)

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425683

Datatype for NULL is as meaningless as datatype for 0: it can be INTEGER, FLOAT or a VARCHAR. You cannot tell it just from the value.

NULL is legitimate value in almost every datatype domain, which means the absence of actual value.

It's also meaningless to discuss datatypes out of context of certain RDBMS.

In SQLite, for instance, datatypes are value-bound, not column-bound, and NULL is a first-class datatype per se.

In Oracle, the datatypes are more strictly defined. For instance, this query works:

SELECT  COALESCE(dt, i)
FROM    (
        SELECT  CAST(NULL AS DATE) AS dt, CAST(NULL AS DATE) i
        FROM    dual
        ) q

and this does not:

SELECT  COALESCE(dt, i)
FROM    (
        SELECT  CAST(NULL AS DATE) AS dt, CAST(NULL AS NUMBER) i
        FROM    dual
        ) q

, because the latter query returns two columns of different datatypes, both of them having values of NULL, and COALESCE requires both arguments to have same datatype.

It's better to say that a NULL of any datatype can be implicitly converted to a NULL on another datatype.

For instance, a VARCHAR can be implicitly converted to a INTEGER if it has value of 0, but cannot if it has value of 'some_string'.

For NULL's, any datatype can be implicitly converted to any other datatype, if the implicit conversion between them is allowed at all.

Upvotes: 1

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66672

Null does not have a specific data type in SQL. Any nullable column or variable can contain null. Null is never equal or unequal to anything. You can cast a variable holding null to another variable and get null, for example:

declare @a integer
set @a = null
select convert (float, @a)

----------------------
NULL

(1 row(s) affected)

Upvotes: 3

CannibalSmith
CannibalSmith

Reputation: 4820

NULL can be cast (converted) to any data type yet data type comparisons with NULL always return FALSE.

Upvotes: -1

bobby
bobby

Reputation: 336

I think the question defeats itself. If NULL had a datatype, wouldn't you be forced to change it with every instantiation outside of its default. For example, when you create it as a character, but then force it into an object's value?

NULL==NULL

That is all.

Upvotes: 0

Fermin
Fermin

Reputation: 36111

http://en.wikipedia.org/wiki/Null_(SQL)

Upvotes: -1

Greg
Greg

Reputation: 321776

Usually NULL is its own datatype - the type of 1 is "INTEGER", the type of the type of NULL is "NULL"

Upvotes: 1

Hemant
Hemant

Reputation: 19826

There is NO data type of NULL. NULL itself means ABSENCE of data. When there is no data, how can it have type?

Upvotes: 6

pedrofernandes
pedrofernandes

Reputation: 16874

I think DBNULL or NULL is a special type.

Upvotes: 0

Frans Bouma
Frans Bouma

Reputation: 8357

NULL is the value for 'undefined'. So any type in a database can be 'undefined', as it's a property of the column: a value of a row for the specific column can be 'undefined' which means it's 'NULL', no matter what the type is. As long as the column is nullable.

Upvotes: 0

Related Questions