sqlite fetching data using foreign key: Cannot bind argument at index 1 because the index is out of range. The statement has 0 parameters

I am trying to fetch a record from the sqlite database in Android, and having trouble. It often throws java.lang.IllegalArgumentException and gives me the same message.

 mListSongs = mSongDao.getSelectedSongs(artist_id);



public List<Song> getSelectedSongs(Long artistId) {
    List<Song> listSongs = new ArrayList<Song>();
    String selectQuery = "SELECT " + DBHelper.SONG_PATH + " FROM " + DBHelper.TABLE_SONG + " s, "
            + DBHelper.TABLE_ARTIST + " a WHERE s."
            + DBHelper.SONG_ID + " = a.'" + DBHelper.ARTIST_ID + "'";               
    String[] selectionArgs = new String[]{String.valueOf(artistId)};
    Cursor cursor;

    cursor = mDatabase.rawQuery(selectQuery, selectionArgs);



    cursor.moveToFirst();

    while (!cursor.isAfterLast()) {
        Song song = cursorToSelectSong(cursor);
        listSongs.add(song);
        cursor.moveToNext();
    }
    cursor.close();
    return listSongs;
}
private Song cursorToSelectSong(Cursor cursor) {Song song = new Song(); song.setmSong_path(cursor.getString(3)); return song;}

Solution

The issue is that you are supplying an argument, as per String[] selectionArgs = new String[]{String.valueOf(artistId)}; and then cursor = mDatabase.rawQuery(selectQuery, selectionArgs); but that the statement (the SELECT statement) has no place-holder (an ?) within it.

So you have 1 argument but the statement has 0 parameters to substitute the argument for.

Changing :-

String selectQuery = "SELECT " + DBHelper.SONG_PATH + " FROM " + DBHelper.TABLE_SONG + " s, "
        + DBHelper.TABLE_ARTIST + " a WHERE s."
        + DBHelper.SONG_ID + " = a.'" + DBHelper.ARTIST_ID + "'";

to :-

String selectQuery = "SELECT " + DBHelper.SONG_PATH + " FROM " + DBHelper.TABLE_SONG + " s, "
        + DBHelper.TABLE_ARTIST + " a WHERE s."
        + DBHelper.SONG_ID + "=?";

Introduces the parameter and it, the ?, will be substituted for the artist_id passed to the method.

Alternately using :-

String selectQuery = "SELECT " + DBHelper.SONG_PATH + " FROM " + DBHelper.TABLE_SONG + " s, "
    + DBHelper.TABLE_ARTIST + " a WHERE s."
    + DBHelper.SONG_ID + " =" + String.valueOf(artist_id);

along with :-

cursor = mDatabase.rawQuery(selectQuery, null);

would also work BUT is open to SQL injection (but not really as it's a long that has been passed, which cannot be a String that could contain dangerous SQL).

i.e. no arguments are passed into rawQuery and therefore there is no expectation that the statement should contain a parameter place-holder (?).

However, there is no need to JOIN the ARTIST table as the SONG table has the ARTIST_ID column.

You'd only need the JOIN if you wanted other details about the ARTIST e.g. artist name (which you probably already know as you've ascertained the ARTIST_ID when invoking the method).

As such the simplified :-

String selectQuery = "SELECT " + DBHelper.SONG_PATH + " FROM " + DBHelper.TABLE_SONG + " WHERE " + DBHelper.SONG_ID + "=?";

would suffice.

Regarding Cursor issues I'd suggest trying :-

cursor = mDatabase.rawQuery(selectQuery, selectionArgs);
DatabaseUtils.dumpCursor(cursor); //<<<<<<<<<< will output the contents of the cursor to the log
while(cursor.moveToNext()) {
    String songpath = cursor.getString(cursor.getColumnIndex(DBHelper.SONG_PATH));
    Log.d("EXTRACTEDPATH", "Extracted PATH " + songpath); //<<<<<<<<<< output extracted path to the log
    Song newsong = new Song();
    newsong.setmSong_path(songpath);
    listSongs.add(newsong);
}
cursor.close();
return listSongs;

}

Dumps the Cursor immediately after it is retrieved
Uses simpler loop
Uses column name to derive the column offset
outputs the data from the column (if it shows path in log, but you still get empty path in list then it's either setmSong_path that is wrong or how you are getting data from the List.)