Problems with database Query when looking for a String

Question

enter code hereI am trying to write a query to extract all rows in my Android SQLite database table where the location reference = "a string". I am using the following code in my database helper class:

public Cursor fetchComponentsForLocation(String locationRef) {
    Cursor mCursor =
            rmDb.query(true, LOCATIONS_TABLE, new String[] {
                    LOCATION_ID, RUN_LINK, AREA_LINK, INSPECTION_LINK, LOCATION_REF, RACKING_SYSTEM, COMPONENT, POSITION, RISK, ACTION_REQUIRED, NOTES_GENERAL, MANUFACTURER, TEXT1, TEXT2, TEXT3, TEXT4, NOTES_SPEC}, 
                    LOCATION_REF + "=" + locationRef, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
}

And I call it in my activity as follows:

    // Get a Cursor for the list items
    Cursor listComponentCursor = rmDbHelper.fetchComponentsForLocation(locationRef);
    startManagingCursor(listComponentCursor);           

    // set the custom list adapter     
    setListAdapter(new MyListAdapter(this, listComponentCursor));

Which then gets used in my ListAdapter to fill my list view. Now in my other activities, when I am fetching rows based on the id (i.e. a long), this code works fine. When I try it with String I get the following error:

Caused by: android.database.sqlite.SQLiteException: **no such column: g:** , while compiling: SELECT DISTINCT _id, run_link, area_link, inspection_link, location_reference, racking_system, component, position, risk, action_required, notes_general, manufacturer, text1, text2, text3, text4, notes_spec FROM location_table WHERE location_reference=g

As you can see, in this case the String = 'g', but it seems to looking for a column called 'g' instead of looking at the data!

Very confused why this works with long but not String. Help appreciated as always.

Simon Dorociak · Accepted Answer · 2012-10-09 09:54:48Z

3

Change yours with this:

String[] columns = {LOCATION_ID, RUN_LINK, AREA_LINK, 
                    INSPECTION_LINK, LOCATION_REF, RACKING_SYSTEM, COMPONENT,
                    POSITION, RISK, ACTION_REQUIRED, NOTES_GENERAL, MANUFACTURER, TEXT1, TEXT2, TEXT3, TEXT4, NOTES_SPEC};
String[] selection = LOCATION_REF + "= ?";

Cursor c = rmDb.query(true, LOCATIONS_TABLE, columns, selection, new String[] {locationRef},
                    null, null, null, null);

I recommend to you use placeholders. This way is cleaner and much more safe.

answered Oct 9, 2012 at 9:54

Simon Dorociak

33.5k10 gold badges70 silver badges107 bronze badges

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

1 Comment

Scamparelli Over a year ago

Thank you deceiver - I will look through my code and revise where necessary. Thanks.

dave.c · Accepted Answer · 2012-10-09 09:53:26Z

1

I think you are missing a couple of single quotes around the string you are searching for:

LOCATION_REF + "='" + locationRef+"'", null,

answered Oct 9, 2012 at 9:53

dave.c

10.9k5 gold badges42 silver badges64 bronze badges

1 Comment

Scamparelli Over a year ago

Fantastic - thanks for your help. Obvious when you know!! :)

Nermeen · Accepted Answer · 2012-10-09 09:53:18Z

1

The where should look like LOCATION_REF + "='" + locationRef + "'" as the strings in the query should be inclused with ''

answered Oct 9, 2012 at 9:53

Nermeen

16k5 gold badges62 silver badges73 bronze badges

1 Comment

Scamparelli Over a year ago

Thank you for responding. Much appreciated.

Mohsin Naeem · Accepted Answer · 2012-10-09 09:54:55Z

1

use this line

rmDb.query(true, LOCATIONS_TABLE, new String[] {
                LOCATION_ID, RUN_LINK, AREA_LINK, INSPECTION_LINK, LOCATION_REF, RACKING_SYSTEM, COMPONENT, POSITION, RISK, ACTION_REQUIRED, NOTES_GENERAL, MANUFACTURER, TEXT1, TEXT2, TEXT3, TEXT4, NOTES_SPEC}, 
                LOCATION_REF + "='" + locationRef+"'", null,
                null, null, null, null);

actually LOCATION_REF if of type String, so you need to put commas there. SQLite Convention.

answered Oct 9, 2012 at 9:54

Mohsin Naeem

12.7k5 gold badges42 silver badges54 bronze badges

Collectives™ on Stack Overflow

Problems with database Query when looking for a String

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related