CODEWITHSUNDEEP
androidsqlite
user1400538
user1400538

Reputation: 865

Sqlite query returning 0 even if the value is null

Following is the query I use to fetch an int value corresponding to a particular field in my sqlite db table.

"SELECT conn_status FROM profiles WHERE devID = '" + id+"'"

If there is no value saved for 'conn_status' corresponding to the devID provided, then the existing value in the table would be null. I retrieve the data from java as shown:

c.getInt(c.getColumnIndex("conn_status"))

The problem here is, with the given query , c.getInt returns 0 even if the value existing in the field is null. How can I modify this query such that, it returns a different value instead of 0, say 5, if the value is null.

Any help is appreciated.

Upvotes: 8

Views: 11940

Answers (7)

Ryan Manes
Ryan Manes

Reputation: 59

I had the same issue with a supposedly NULL value returning 0 in a c.getInt(index) call.

It turns out that my database had a text value of null. That is, instead of a 0 byte value of NULL, there were 4 characters 'n' 'u' 'l' 'l'. This is confusing because null looks correct. I didn't catch it until I checked the DB with a SQLite DB Browser.

When I replaced those text values, things worked as expected.

I know this is an old question, but hopefully this will save someone else some troubleshooting time.

Upvotes: 0

Kailas Bhakade
Kailas Bhakade

Reputation: 1922

As you want any different value like 5 if column having null value then try following code.

Cursor c = db.rawQuery("SELECT conn_status FROM profiles WHERE devID = '" + id+"'");

    if(c.getCount>0){
    int number = c.getInt(c.getColumnIndex(conn_status));
    return number;
    }else{
    return 5;
    }

if cursor.getCount() return something means query work and database not having null value. If return opposite means query not work and databse having null value for respective query. So try it once.

Upvotes: 2

woong-jae choi
woong-jae choi

Reputation: 23

You can tell if the column is null by calling getType().

Integer locationInfoId;

if (cursor.getType(columnIndex) == Cursor.FIELD_TYPE_NULL){
    locationInfoId = null;
} else {
    locationInfoId = cursor.getInt(columnIndex);
}

columnIndex++;

Upvotes: 0

e-info128
e-info128

Reputation: 4092

In Android use Integer object not int primitive, Integer is nullable, primitive int cause exception. In Model verify if value is null or process the content.

/**
 * Return real integer of value or null
 * @param column_name Name of column in result
 */
public Integer getInt(String column_name){
    try{
        if(cursor.isNull(cursor.getColumnIndex(column_name)))
            return null;
        return cursor.getInt(cursor.getColumnIndex(column_name));
    }catch(Exception e){
        e.printStackTrace();
        Log.e("sqlite_exception", "column " + column_name + " not exists. " + e.getMessage());
        return null;
    }
}

Upvotes: 0

CL.
CL.

Reputation: 180270

In SQLite, you can use the IFNULL function to replace NULL values:

SELECT IFNULL(conn_status, 5) FROM profiles WHERE devID = ?

Upvotes: 16

zeiger
zeiger

Reputation: 700

int in Java is a primitive datatype and cannot be null. Hence, if there is no value returned by your getInt, you get 0. The Integer object may be null so you may want to consider using Integer instead of int if your logic requires you to check for nulls instead of 0's

Upvotes: 3

Geobits
Geobits

Reputation: 22342

You can use the isNull() function. Here's an example:

static int getInt(String columnName)
{
    if(c.isNull(c.getColumnIndex(columnName)))
        return -1;
    return c.getInt(c.getColumnIndex(columnName));
}

Upvotes: 18

Related Questions