5

I'm trying do retrieve an image from SQLite database, but the CURSOR always get no results. If I try to select without the image columnm, it works just fine.

 Livro selecionarLivro(String titulo, String autor)

{
    SQLiteDatabase db = this.getReadableDatabase();

    String[] dados = new String[2];
    dados[0] = titulo;
    dados[1] = autor;

    Livro livro1 = null;

    Cursor cursor;
    String[] campos =  {COLUNA_CODIGO, COLUNA_TITULO, COLUNA_AUTOR, COLUNA_IMG};

    cursor = db.query(TABELA_LIVROS, campos, COLUNA_TITULO + "=? AND " + COLUNA_AUTOR + "=?", dados, null, null, null, null);

    if(cursor.moveToNext()){
        livro1 = new Livro();
        livro1.setTitulo(cursor.getString(cursor.getColumnIndex(COLUNA_TITULO)));
        livro1.setAutor(cursor.getString(cursor.getColumnIndex(COLUNA_AUTOR)));
        livro1.setImgFile(cursor.getBlob(cursor.getColumnIndex(COLUNA_IMG)));
    }
    db.close();

    return livro1;
}

It returns "null" because CURSOR doesn't have results. The INSERT method is working.

henrique1299
  • 53
  • 1
  • 5
  • Your issue should result in an exception assuming that *select without the image column works just fine.* The issue is likely that column **COLUNA_IMG** doesn't exist or that the blob cannot be retrieved due to it's size. As such you should edit your question and include the stack-trace (if you don't know how see [Debug your app](https://developer.android.com/studio/debug/)). If the column **COLUNA_IMG** has recently been added, deleting the App's data or uninstalling the App and rerunning may correct a column not found issue. – MikeT Mar 04 '19 at 21:58

1 Answers1

4

There are two likely issues both of which would result in an exception which could be determine by looking at the log.

Issue 1 - column not found

The first common issue is that a column has been added to the table by amending the code that is used by the onCreate method.

  • e.g. you may have changed the table creation SQL to include defining the column COLUNA_IMG since the App has been run.

The onCreate method only runs once, when the database was first created. It does not run every time the App is run or every time an instance of the DatabaseHelper class is created.

This issue could be determined by looking at the log, in which case you could find something similar to :-

2019-03-05 11:17:10.446 8872-8872/aaa.so54989458 E/SQLiteLog: (1) no such column: img
2019-03-05 11:17:10.447 8872-8872/aaa.so54989458 D/AndroidRuntime: Shutting down VM
2019-03-05 11:17:10.455 8872-8872/aaa.so54989458 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: aaa.so54989458, PID: 8872
    android.database.sqlite.SQLiteException: no such column: img (code 1 SQLITE_ERROR): , while compiling: SELECT codigo, titulo, autor, img FROM livros WHERE titulo=? AND autor=?
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1408)
        at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1255)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1126)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1332)
        at aaa.so54989458.DBHelper.selecionarLivro(DBHelper.java:70)
        at aaa.so54989458.MainActivity$2.onItemClick(MainActivity.java:78)
        at android.widget.AdapterView.performItemClick(AdapterView.java:318)
        at android.widget.AbsListView.performItemClick(AbsListView.java:1159)
        at android.widget.AbsListView$PerformClick.run(AbsListView.java:3136)
        at android.widget.AbsListView$3.run(AbsListView.java:4052)
        at android.os.Handler.handleCallback(Handler.java:873)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:193)
        at android.app.ActivityThread.main(ActivityThread.java:6669)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
  • Note that if you are using Android Studio, then it highlights the line at aaa.so54989458.DBHelper.selecionarLivro(DBHelper.java:70) and that by clicking on the link it takes you to the code where the exception was raised in your code.
    • (note that the method in which the error occurred is your selecionarLivro method, other aspects such as the Class and package names would be as per your code).

Fix for Issue 1

The fix for the above issue, would be to do one of the following :-

  1. Delete/Clear the App's data (via Settings).
  2. Uninstall the App

Then, assuming the code is correct, rerunning the App.

Issue 2 - Image too large.

Images are not ideal candidates for storing in databases, rather it is better to store something that indicates the image to be used (e.g. a path to the image). The image itself is comprised of bytes, often a very large number of bytes. Although SQLite can hold pretty large images.

The android SDK implementation, that extracts data into a Cursor is more restrictive.

The Cursor is effectively a buffer that holds a partial snapshot of the whole data. The underlying snapshot, a Cursor Window, is restricted in the Android SDK to 2M and that at a minimum must be able to hold a single row. You are guaranteed a failure if you have an image that is 2M or more. You are very likely to get a failure or issues if an image is say greater than 0.5M.

An example of an error where an image is too large for the Cursor Window, even though it has successfully been stored in the database is :-

2019-03-05 11:33:46.878 9059-9059/aaa.so54989458 W/CursorWindow: Window is full: requested allocation 2097152 bytes, free space 1789213 bytes, window size 2097152 bytes
2019-03-05 11:33:46.886 9059-9059/aaa.so54989458 W/CursorWindow: Window is full: requested allocation 2097152 bytes, free space 1983933 bytes, window size 2097152 bytes
2019-03-05 11:33:46.886 9059-9059/aaa.so54989458 W/CursorWindow: Window is full: requested allocation 2097152 bytes, free space 2096653 bytes, window size 2097152 bytes
2019-03-05 11:33:46.887 9059-9059/aaa.so54989458 E/SQLiteQuery: exception: Row too big to fit into CursorWindow requiredPos=3, totalRows=4; query: SELECT * FROM livros
2019-03-05 11:33:46.887 9059-9059/aaa.so54989458 D/AndroidRuntime: Shutting down VM
2019-03-05 11:33:46.895 9059-9059/aaa.so54989458 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: aaa.so54989458, PID: 9059
    android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=3, totalRows=4
        at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:859)
        at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
        at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
        at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:157)
        at android.database.sqlite.SQLiteCursor.onMove(SQLiteCursor.java:128)
        at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:237)
        at android.widget.CursorAdapter.getItemId(CursorAdapter.java:258)
        at android.widget.AbsListView$RecycleBin.retrieveFromScrap(AbsListView.java:7105)
        at android.widget.AbsListView$RecycleBin.getScrapView(AbsListView.java:6816)
        at android.widget.AbsListView.obtainView(AbsListView.java:2365)
        at android.widget.ListView.measureHeightOfChildren(ListView.java:1408)
        at android.widget.ListView.onMeasure(ListView.java:1315)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1535)
        at android.widget.LinearLayout.measureVertical(LinearLayout.java:825)
        at android.widget.LinearLayout.onMeasure(LinearLayout.java:704)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
        at android.support.v7.widget.ContentFrameLayout.onMeasure(ContentFrameLayout.java:143)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.support.v7.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:401)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1535)
        at android.widget.LinearLayout.measureVertical(LinearLayout.java:825)
        at android.widget.LinearLayout.onMeasure(LinearLayout.java:704)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
        at com.android.internal.policy.DecorView.onMeasure(DecorView.java:716)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:2718)
        at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1572)
        at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1855)
        at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:1460)
        at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:7183)
        at android.view.Choreographer$CallbackRecord.run(Choreographer.java:949)
        at android.view.Choreographer.doCallbacks(Choreographer.java:761)
        at android.view.Choreographer.doFrame(Choreographer.java:696)
        at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:935)
        at android.os.Handler.handleCallback(Handler.java:873)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:193)
        at android.app.ActivityThread.main(ActivityThread.java:6669)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
  • Note the first three lines are for previous rows into which the image could fit (basically the messages are saying sorry couldn't get more the rows as the CursorWindow is not full.)

Fix for Issue 2

There is no simple fix unless you can reduce the size of the images to an acceptable/manageable size.

The recommended approach is to store the images elsewhere and to then store the path or part thereof in the database.

  • Here's an example that uses a combination of storing the path of images larger than 100k in the database but storing the image, if 100k or less in the database (which can be more efficient).
Community
  • 1
  • 1
MikeT
  • 51,415
  • 16
  • 49
  • 68
  • https://stackoverflow.com/questions/57107489/sqliteblobtoobigexception-row-too-big-to-fit-into-cursorwindow-while-writing-to/63803272#63803272 – Leo Vitor Sep 09 '20 at 00:45