Sqlite database query based on a string name

Question

I created sqlite database inside my app and trying to save highlighted text , Saving is all working fine , even retrieving all highlights is working fine , but when I query for highlights based on bookname I am getting an error.

Here is what I tried so far - this is my oncreate of sqlite database:

@Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_HIGHLIGHTS + "("  
            + KEY_ID + " INTEGER PRIMARY KEY," +KEY_BOOKNAME +" TEXT," + KEY_HIGHLIGHT+" TEXT,"   + KEY_TEXTSIZE + " INTEGER,"  
            + KEY_PAGENO + " INTEGER" + ")";  
    db.execSQL(CREATE_CONTACTS_TABLE);  
}

This is working fine

  public List<HighlightSave> getAllHighlights() {

    List<HighlightSave> saveAllHighlights=new ArrayList<HighlightSave>();

    String selectQuery="SELECT * FROM "+TABLE_HIGHLIGHTS;

    SQLiteDatabase db=this.getWritableDatabase();

    Cursor cursor=db.rawQuery( selectQuery,null);

    if(cursor.moveToFirst()) { 
        do {
            HighlightSave hs=new HighlightSave();
            hs.setBookName(cursor.getString(1));
            hs.setHighlightedText(cursor.getString(2));

            hs.setTextSize(Integer.parseInt(cursor.getString(3)));
            hs.setPageno(Integer.parseInt(cursor.getString(4)));

            saveAllHighlights.add(hs);
        } while(cursor.moveToNext());
    }

    cursor.close();
    db.close();

    return saveAllHighlights;
}

Problem is when I try to query using a bookname like below

 public List<HighlightSave> getBookHighlights(String bookname) {

    SQLiteDatabase db= this.getReadableDatabase();

    List<HighlightSave> AllHighlightsOfBook= new ArrayList<HighlightSave>();

       Cursor cursor = db.query(TABLE_HIGHLIGHTS, new String[] {KEY_BOOKNAME,KEY_HIGHLIGHT,KEY_TEXTSIZE,KEY_PAGENO}, KEY_BOOKNAME + "=?", new String[] { bookname }, null, null, null, null);

    if(cursor.moveToFirst()){

        do{
            HighlightSave hs=new HighlightSave();
            hs.setBookName(cursor.getString(1));
            hs.setHighlightedText(cursor.getString(2));

            hs.setTextSize(Integer.parseInt(cursor.getString(3)));
            hs.setPageno(Integer.parseInt(cursor.getString(4)));

            AllHighlightsOfBook.add(hs);
        } while(cursor.moveToNext());
    }

    cursor.close();
    db.close();

    return AllHighlightsOfBook; 
}

I get this error

12-05 11:03:48.025: E/AndroidRuntime(8811): FATAL EXCEPTION: main
12-05 11:03:48.025: E/AndroidRuntime(8811): Process: com.wowio.ebookreader, PID: 8811
12-05 11:03:48.025: E/AndroidRuntime(8811): java.lang.IllegalStateException: Couldn't read row 0, col 4 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

12-05 11:03:48.025: E/AndroidRuntime(8811): at android.database.CursorWindow.nativeGetString(Native Method) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.database.CursorWindow.getString(CursorWindow.java:438) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51) 12-05 11:03:48.025: E/AndroidRuntime(8811): at com.eminosoft.epubreader.HighlightDatabase.getBookHighlights(HighlightDatabase.java:160) 12-05 11:03:48.025: E/AndroidRuntime(8811): at com.eminosoft.epubreader.HighlightFragment.onActivityCreated(HighlightFragment.java:66) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.support.v4.app.Fragment.performActivityCreated(Fragment.java:1508) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:958) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1115) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.support.v4.app.BackStackRecord.run(BackStackRecord.java:682) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.support.v4.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1478) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.support.v4.app.FragmentManagerImpl$1.run(FragmentManager.java:446) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.os.Handler.handleCallback(Handler.java:739) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.os.Handler.dispatchMessage(Handler.java:95) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.os.Looper.loop(Looper.java:148) 12-05 11:03:48.025: E/AndroidRuntime(8811): at android.app.ActivityThread.main(ActivityThread.java:5422) 12-05 11:03:48.025: E/AndroidRuntime(8811): at java.lang.reflect.Method.invoke(Native Method) 12-05 11:03:48.025: E/AndroidRuntime(8811): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) 12-05 11:03:48.025: E/AndroidRuntime(8811): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

so i thought some problem with cursor so initialised it this way inside getBookHighlights()

String query = "Select * from "+TABLE_HIGHLIGHTS+ " Where " +KEY_BOOKNAME + " = " + bookname ;
    Cursor cursor=  db.rawQuery(query,null);

then i am getting this error

12-05 11:20:06.514: E/AndroidRuntime(12305): FATAL EXCEPTION: main
12-05 11:20:06.514: E/AndroidRuntime(12305): Process: com.wowio.ebookreader, PID: 12305
12-05 11:20:06.514: E/AndroidRuntime(12305): android.database.sqlite.SQLiteException: near "Raven": syntax error (code 1): , while compiling: Select * from highlights Where book_Name = The Raven
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at com.eminosoft.epubreader.HighlightDatabase.getBookHighlights(HighlightDatabase.java:146)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at com.eminosoft.epubreader.HighlightFragment.onActivityCreated(HighlightFragment.java:66)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.Fragment.performActivityCreated(Fragment.java:1508)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:958)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1115)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.BackStackRecord.run(BackStackRecord.java:682)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1478)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.support.v4.app.FragmentManagerImpl$1.run(FragmentManager.java:446)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.os.Handler.handleCallback(Handler.java:739)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.os.Handler.dispatchMessage(Handler.java:95)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.os.Looper.loop(Looper.java:148)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at android.app.ActivityThread.main(ActivityThread.java:5422)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at java.lang.reflect.Method.invoke(Native Method)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
12-05 11:20:06.514: E/AndroidRuntime(12305):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

What am I doing wrong? (I uninstalled app before every run so there won't be any problem with previous database)

can you test on emulator for once? run your app and open sqlite db from adm and check if the table has data — Rushi Ayyappa
– Rushi Ayyappa, Commented Dec 5, 2016 at 6:13

IntelliJ Amiya · Accepted Answer · 2016-12-05 06:14:25Z

2

Rectify your SELECT query at first

 String query = "SELECT * FROM " + TABLE_HIGHLIGHTS+ " WHERE " + KEY_BOOKNAME + " = '" + bookname + "'";

Then Uninstall old App and run Again .

answered Dec 5, 2016 at 6:14

IntelliJ Amiya

75.8k15 gold badges169 silver badges199 bronze badges

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

Comments

MashukKhan · Accepted Answer · 2016-12-05 07:17:16Z

2

The error regarding to fetch the column values.

Column index starts with the zero in the Sqlite cursor.

java.lang.IllegalStateException: Couldn't read row 0, col 4 from CursorWindow.

So the meaning of the above line is 4 column is not available in the cursor object

Your try with 0 to 3. I think It should be resolved.

edited Dec 5, 2016 at 7:17

MashukKhan

1,9642 gold badges29 silver badges49 bronze badges

answered Dec 5, 2016 at 6:22

Kona Suresh

1,8541 gold badge16 silver badges27 bronze badges

1 Comment

Manohar Over a year ago

didn't try this answer ,because above answer worked fine so i am sticking to that any way thanks for help +1 :)

Rushi Ayyappa · Accepted Answer · 2016-12-05 06:26:16Z

1

    String query="select * from "+TABLE_HIGHLIGHTS+" where "+KEY_BOOKNAME+"='"+bookname+"'";

edited Dec 5, 2016 at 6:26

answered Dec 5, 2016 at 6:17

Rushi Ayyappa

2,8682 gold badges20 silver badges33 bronze badges

2 Comments

Manohar Over a year ago

Thanks it works but, intellij answered it first so accepted his answer, any way +1 thanks for help :)

Rushi Ayyappa Over a year ago

ya no proble.Thanks for +1

Collectives™ on Stack Overflow

Sqlite database query based on a string name

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related