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 1:

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.)

Solution 2:

I think you want to fetch a list of songs by an artist, providing the artistId.
I believe that in in each row of the songs table DBHelper.TABLE_SONG there is a column for the id of the artist. If there isn't it should be.
Change your sql statement to this:

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

As I said there must be a column DBHelper.ARTIST_ID or similar to identify the artist of each song.
The ? is the 1 parameter and its value will be artistId.