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

Solution 1:

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.

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

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

Solution 2:

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

Solution 3:

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:

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

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