How to search for NULL in Android database query & OR statement in selection

Question

a couple of queries regarding a database query:

So I have the following database query, which has a WHERE statement which includes three String variables (rackingSystem, manufacturer, "Amber Risk").

public Cursor fetchComponentsAndSpecForManufacturer(long inspectionId, String rackingSystem, String manufacturer) {
    Cursor mCursor =
            rmDb.query(true, DAMAGED_COMPONENTS_TABLE, new String[] {
                    DAMAGED_COMPONENT_ID,
                    LOCATION_LINK,
                    RUN_LINK,
                    AREA_LINK,
                    INSPECTION_LINK,
                    LOCATION_REF,
                    RACKING_SYSTEM,
                    COMPONENT,
                    COMPONENT_TYPE,
                    QUANTITY,
                    POSITION,
                    RISK,
                    ACTION_REQUIRED,
                    NOTES_GENERAL,
                    MANUFACTURER,
                    TEXT1,
                    TEXT2,
                    TEXT3,
                    TEXT4,
                    NOTES_SPEC,
                    SPEC_SAVED}, 
                    INSPECTION_LINK + " = " + inspectionId + " AND " + RACKING_SYSTEM + " = ? AND " + MANUFACTURER + " = ? AND " + RISK + " = ? ", 
                    new String[] {rackingSystem, manufacturer, "Amber Risk"},
                    COMPONENT_TYPE + ", " + TEXT1 + ", " + TEXT2 + ", " + TEXT3 + ", " + TEXT4 + ", " + NOTES_SPEC,
                    null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

Question 1:

How do I search for NULL as, in the above example, manufacturer may be NULL in the database (as the user may not have inputed anything into the database for that bit yet).

Question 2:

Can I include an OR statement in the WHERE statement? So at the moment I am seraching for all records that are "Amber Risk", but I need to also include in this list entries that are "Red Risk". If I can do this, how should I edit the WHERE statement?

Walloper

sql where X is null or isnull(x)? – Accommodation

Answer 1

How do I search for NULL as, in the above example, manufacturer may be NULL in the database

Use MANUFACTURER + " IS NULL".

Can I include an OR statement in the WHERE statement?

Yes. Let's search for null or empty manufacturers:

MANUFACTURER + " IS NULL OR " + MANUFACTURER + " = ''"

---

So at the moment I am seraching for all records that are "Amber Risk", but I need to also include in this list entries that are "Red Risk".

INSPECTION_LINK + " = " + inspectionId + " AND " + RACKING_SYSTEM + " = ? AND " + 
        MANUFACTURER + " IS NULL AND (" + RISK + " = ? OR " + RISK + " = ?)", 
new String[] {rackingSystem, manufacturer, "Amber Risk", "Red Risk"},

Shammy

Ah, but I search MANUFACTURER using a variable (manufacturer) - this may or may not be NULL. If it is NULL, it currently causes my app to crash (any idea why this might be?). Thanks for confirmation of OR statement though, will give this a shot. – Walloper

"but I search MANUFACTURER using a variable (manufacturer)" Ok, so you want to ignore the manufacturer portion entirely when it is null? – Shammy

I'm afraid not, the MANUFACTURER may be NULL, but there may be other data attached to those records that I need to show (i.e. the manufacturer would just be blank when I output the data).. – Walloper

You should just have two separate queries and use an if statement to choose which version to run: if(manufacturer == null). – Shammy

Ok, blimey that'll work. I'll give it a shot, thanks so much. If you fancy another similar question to answer: #14173859 :) – Walloper