Blob Row too big too fit in Android

Question

How can I limit the size of the image when saving to SQLite? I have this error when I retrieve which I think didn't get the big size of the blob Image from SQLite. However, I tried putting limit 500 in the query like this SELECT id,cash_card,hh_number,cc_image FROM CgList limit 500, but the result is the same, it crashes my application.

In short, Is there any way to reduce the file size of the image when inserting to the SQLite database?

Error

android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=4

Insert data and blob

 public void insertData(String cash_card, String hh_number,String series_number ,byte[] cc_image,byte[] id_image){
    SQLiteDatabase database = getWritableDatabase();
    String sql = "INSERT INTO CgList VALUES (NULL,?, ?, ?, ?, ?)";
    SQLiteStatement statement = database.compileStatement(sql);
    statement.clearBindings();

    statement.bindString(1, cash_card);
    statement.bindString(2, hh_number);
    statement.bindString(3, series_number);
    statement.bindBlob(4, cc_image);
    statement.bindBlob(5, id_image);
    statement.executeInsert();
}

Getting data

  Cursor cursor = ScannedDetails.sqLiteHelper.getData("SELECT id,cash_card,hh_number,cc_image FROM CgList");
    list.clear();
    while (cursor.moveToNext()) {   // the error is here
        int id = cursor.getInt(0);
        String name = cursor.getString(1);
        String price = cursor.getString(2);
        byte[] image = cursor.getBlob(3);

        list.add(new Inventory(name, price, image, id));
    }
    adapter.notifyDataSetChanged();

When I click button to save to SQLite

    btnSubmit.setOnClickListener( new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            sqLiteHelper.insertData(
                    edtCashCard.getText().toString().trim(),
                    edtHhnumber.getText().toString().trim(),
                    edtSeriesno.getText().toString().trim(),
                    imageViewToByte(mPreviewCashcard),
                    imageViewToByte(mPreview4PsId)
            );
        }
    });

Converting Image To Byte

 public static byte[] imageViewToByte(ImageView image) {
   Bitmap bitmap = ((BitmapDrawable)image.getDrawable()).getBitmap();
   ByteArrayOutputStream stream = new ByteArrayOutputStream();
   bitmap.compress(Bitmap.CompressFormat.PNG, 100, stream);
   byte[] byteArray = stream.toByteArray();
   return byteArray;
 }

Updated

I think this is the problem when capturing an image it provide bigger size, I need this because after I capture the image I want to crop the image for some purpose but I want to display the actual capture not the cropped image to another activity

 private void pickCamera() {
    ContentValues values = new ContentValues();
    values.put(MediaStore.Images.Media.TITLE, "NewPic");
    values.put(MediaStore.Images.Media.DESCRIPTION, "Image to Text");
    image_uri = getContentResolver().insert(MediaStore.Images.Media.EXTERNAL_CONTENT_URI,values);
    Intent cameraIntent = new Intent (MediaStore.ACTION_IMAGE_CAPTURE);
    cameraIntent.putExtra(MediaStore.EXTRA_OUTPUT, image_uri);
    startActivityForResult(cameraIntent, IMAGE_PICK_CAMERA_CODE);
}

OnActivityResult

@Override
protected void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) {
    super.onActivityResult(requestCode, resultCode, data);
    if (resultCode == RESULT_OK){
        if (requestCode == IMAGE_PICK_GALLER_CODE){
            CropImage.activity(data.getData()).setGuidelines(CropImageView.Guidelines.ON).start(this);
        }
        if (requestCode == IMAGE_PICK_CAMERA_CODE){

            CropImage.activity(image_uri).setGuidelines(CropImageView.Guidelines.ON).start(this);
        }
    }
    if (requestCode == CropImage.CROP_IMAGE_ACTIVITY_REQUEST_CODE){
        CropImage.ActivityResult result = CropImage.getActivityResult(data);
        if(resultCode ==RESULT_OK){
            Uri resultUri = result.getUri();
            resultUri.getPath();
            mPreviewIv.setImageURI(resultUri);
            BitmapDrawable bitmapDrawable = (BitmapDrawable)mPreviewIv.getDrawable();
            Bitmap bitmap = bitmapDrawable.getBitmap();
            TextRecognizer recognizer = new TextRecognizer.Builder(getApplicationContext()).build();

            if(!recognizer.isOperational()){
                Toast.makeText(this,"Error",Toast.LENGTH_SHORT).show();
            }
            else{
                Frame frame = new Frame.Builder().setBitmap(bitmap).build();
                SparseArray<TextBlock> items = recognizer.detect(frame);
                StringBuilder sb = new StringBuilder();

                for (int i = 0; i<items.size(); i++){
                    TextBlock myItem = items.valueAt(i);
                    sb.append(myItem.getValue());
                    sb.append("\n");
                }
                
                Intent i = new Intent(MainActivity.this, ScannedDetails.class);   
                //camera
                i.putExtra("CashCardImage",image_uri.toString());  //This data pass to another activity
                startActivity(i);
            }
        }
    }
}

Retrieve to another Activity

Bundle extras = getIntent().getExtras();
   String resultUri = extras.getString("CashCardImage");
   Uri myUri = Uri.parse(resultUri);
   mPreviewCashCard.setImageURI(myUri);

The other one I tried when saving to SQLITE the size is not big it's just KIB, I think the problem is on the 1st PickCamera but those code need for cropping an Image

 private void pickCamera() {
   Intent intent = new Intent(ScannedDetails.this, InventoryList.class);
   startActivity(intent);
   }

Answer 1

However, I tried putting limit 500 in the query like this SELECT id,cash_card,hh_number,cc_image FROM CgList limit 500

LIMIT limits the number of rows returned, is does not limit the size of a row.

The issue is that a single row exceeds the capacity of the cache/buffer (CursorWindow) that a Cursor uses. This restriction does not apply when inserting the said row/rows.

The typical solution is to not store images but to instead store a reference, such as a file path, from which the image (or other large item) can then be retrieved.

Here's an example that stores images less than a set size (100k in the example) as a blob and those larger as files in data/data/myimages, which could be another solution.

It is possible to break a large image into chunks of data and store/retrieve that. Here's an example of doing that.

Answer 2

Don't store an image directly in your database. Instead, save the image to a file or to a webserver. Then in the database store the file path or URL to the image.