Android SQL Query multiple WHERE args

Question

For some reason my sql.query is failing because of the way it is formatted. I have no idea why. It does not crash nor does it throw an error. It simply does not return a value. I am using two WHERE arguments in the query. I see in the Log.d that both arguments are successfully passed to the method.

public static String fetchLightTableD(String flightRowId, String filenamePrefix){

    String[] lightcolumns = new String[] { KEY_ROWID, KEY_TID, KEY_NAME, KEY_LUXVALUE, KEY_TRANSMITTED };
    Log.d("CSV", "SQL fetchLightTableD, revceived tid - " + filenamePrefix + " " + flightRowId);
    Cursor lightcursor = myDatabase.query(LIGHT_TABLE, lightcolumns, KEY_TID + "=" + filenamePrefix + " AND " + KEY_ROWID + "=" + flightRowId, null, null, null, null);
    String lightTableResults = "";

    int iRowId = lightcursor.getColumnIndex(KEY_ROWID);
    int iTid = lightcursor.getColumnIndex(KEY_TID);
    int iName = lightcursor.getColumnIndex(KEY_NAME);
    int iLux = lightcursor.getColumnIndex(KEY_LUXVALUE);
    int iTrans = lightcursor.getColumnIndex(KEY_TRANSMITTED);

    for (lightcursor.moveToFirst(); !lightcursor.isAfterLast(); lightcursor
            .moveToNext()) {
        lightTableResults = lightTableResults
                + lightcursor.getString(iRowId) + ","
                + lightcursor.getString(iTid) + ","
                + lightcursor.getString(iName) + ","
                + lightcursor.getString(iLux) + ","
                + lightcursor.getString(iTrans) + "\n";


    }

    return lightTableResults;

}

It should be filtering the table for the row that matches both the rowid and the filenameprefix. I have also tested removing one or the other WHERE args to make sure values actually exist. Of course they do, so there is something wrong with my AND. Can anyone see where it is incorrect?

The equivalent should be as so:

SELECT lightcolumns 
FROM LIGHT_TABLE 
WHERE KEY_TID=rowid AND KEY_TID=filenamePrefix;

If filenamePrefix is a string, as I strongly suspect, you have to surround its value by ' (single quote) — Phantômaxx
– Phantômaxx, Commented Jul 2, 2014 at 7:33
well both are technically strings. If I remove the WHERE for the TID then it functions fine. Surrounding it with single quotes did not work. — user3705399
– user3705399, Commented Jul 2, 2014 at 7:35

Giru Bhai · Accepted Answer · 2014-07-02 07:42:15Z

3

Use your query like this

 Cursor lightcursor = myDatabase.query(LIGHT_TABLE,lightcolumns,
            KEY_TID "=?"+ " AND " + KEY_ROWID +"=?",
            new String[] { filenamePrefix , flightRowId},null, null, null);

instead of

Cursor lightcursor = myDatabase.query(LIGHT_TABLE, lightcolumns, KEY_TID + "=" + filenamePrefix + " AND " + KEY_ROWID + "=" + flightRowId, null, null, null, null);

answered Jul 2, 2014 at 7:42

Giru Bhai

14.4k5 gold badges50 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3705399 Over a year ago

Good job. I will study why this argument format worked and not the original. Makes no sense.

Siva · Accepted Answer · 2014-07-02 07:45:22Z

0

try this

String Query = "SELECT " + lightcolumns + " FROM "+ LIGHT_TABLE + " WHERE " + KEY_TID + " = "+ rowid + " AND " + KEY_TID + " = " +filenamePrefix ; 

Cursor lightcursor = myDatabase.rawquery(Query ,null);

answered Jul 2, 2014 at 7:45

Siva

9,10112 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

Android SQL Query multiple WHERE args

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related