CODEWITHSUNDEEP
androiddatabase
user387184
user387184

Reputation: 11053

inserting null as Integer value into a database

I am trying to store a null value into the database but everytime I load the value I get 0.

I declare the field just like "intVal integer"

This is how I retrieve it:

Integer x;

x = cursor.getInt(cursor.getColumnIndexOrThrow(MyDBAdapter.KEY_X));

Is that reliable? or is it undefined?

So it seems to me one cannot save null as an undefined integervalue

Many thanks

Upvotes: 8

Views: 8015

Answers (3)

Jefferson Lima
Jefferson Lima

Reputation: 5426

As Rajdeep Dua mentioned, SQLite uses a dynamic typing. Here's what the documentation says:

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

The Android documentation says that the result of getInt() when the column value is null is implementation defined. For me, it returns 0 as well.

So I used getString() instead, although the result of this method is also implementation defined, for me, it returns null when the column value is null, or a string containing the number otherwise.

So the code looks like this:

Strint strVal = cursor.getInt(cursor.getColumnIndexOrThrow(MyDBAdapter.KEY_X));

if(strVal != null){
    int intVal = Integer.parseInt(strVal);
}

Upvotes: 0

Graham Borland
Graham Borland

Reputation: 60691

From the getInt() documentation:

Returns the value of the requested column as an int. The result and whether this method throws an exception when the column value is null, the column type is not an integral type, or the integer value is outside the range [Integer.MIN_VALUE, Integer.MAX_VALUE] is implementation-defined.

So, it's an implementation detail which you shouldn't rely on.

You can still get the effect you want, though: you simply do the null check before you read the value.

int index = cursor.getColumnIndexOrThrow(MyDBAdapter.KEY_X);
Integer x = null;
if (!cursor.isNull(index)
    x = cursor.getInt(index);

// now x is either null or contains a valid value

Upvotes: 16

Rajdeep Dua
Rajdeep Dua

Reputation: 11230

SQLite is dynamically typed. Since you defined column of type Integer and it didn't find a value it returned 0 based on the hint.

Try using String as the type of column

Upvotes: 0

Related Questions