how to get image stored as string in android sqlite operation

Question

I am beginner in android programming. I tried to write a code to save some data about the product in sqlite database. about the product image, i created a column productImage which is TEXT type. after getting the image using the camer, i created Bitmap object from it:

Bitmap bm = (Bitmap) imageReturnedIntent.getExtras().get("data");

and then encoded it into string base 64:

productImageString = new ImageEncodeDecode().BitMapToString(bm);

public String BitMapToString(Bitmap bitmap){
    ByteArrayOutputStream baos=new ByteArrayOutputStream();
    bitmap.compress(Bitmap.CompressFormat.PNG,100, baos);
    byte [] b=baos.toByteArray();
    String temp=Base64.encodeToString(b, Base64.DEFAULT);
    return temp;
}

and finnaly send it into the product object and inserted it into the database:

b.addProduct(new ProductInfo(title.getText().toString(), details.getText().toString(),
                                phone.getText().toString(), dateOfPost, price.getText().toString(), productImageString,
                                logedInUser));

Although I created the column productImage as TEXT type, the compiler stores the encoded image string. but when it tries to get the product information using getAllProducts() it gives the following exception: (the cause of the problem is in this line : productInfo.setThumbnail(cursor.getString(4)); )

04-15 20:41:03.601 5946-5946/com.mobileapp4u.sp.espareparts E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.mobileapp4u.sp.espareparts, PID: 5946
    java.lang.RuntimeException: Unable to start activity ComponentInfo{com.mobileapp4u.sp.espareparts/com.mobileapp4u.sp.espareparts.Main3Activity}: android.database.sqlite.SQLiteException: unknown error (code 0 SQLITE_OK): Unable to convert BLOB to string
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2892)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3027)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:101)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:73)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1786)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:164)
        at android.app.ActivityThread.main(ActivityThread.java:6656)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:823)
     Caused by: android.database.sqlite.SQLiteException: unknown error (code 0 SQLITE_OK): Unable to convert BLOB to string
        at android.database.CursorWindow.nativeGetString(Native Method)
        at android.database.CursorWindow.getString(CursorWindow.java:465)
        at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51)
        at com.mobileapp4u.sp.espareparts.Database.DatabaseHandler.getAllProducts(DatabaseHandler.java:229)
        at com.mobileapp4u.sp.espareparts.Main3Activity.prepareProducts(Main3Activity.java:201)
        at com.mobileapp4u.sp.espareparts.Main3Activity.onCreate(Main3Activity.java:134)
        at android.app.Activity.performCreate(Activity.java:7117)
        at android.app.Activity.performCreate(Activity.java:7108)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1262)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2867)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3027) 
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78) 
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:101) 
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:73) 
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1786) 
        at android.os.Handler.dispatchMessage(Handler.java:106) 
        at android.os.Looper.loop(Looper.java:164) 
        at android.app.ActivityThread.main(ActivityThread.java:6656) 
        at java.lang.reflect.Method.invoke(Native Method) 
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438) 
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:823)

How can I solve this problem?

this is the table creation part of the database code:

String CREATE_PRODUCTS_TABLE;
        CREATE_PRODUCTS_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_PRODUCTS + " (" + KEY_PRODUCT_ID +
                " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + KEY_PRODUCT_TITLE + " TEXT, " + KEY_PRODUCT_DETAILS + " TEXT, " + KEY_PRODUCT_DATE_OF_POST +
                " TEXT, " + KEY_PRODUCT_IMAGE + " TEXT, "
                + KEY_PRODUCT_PHONE + " TEXT, " + KEY_PRODUCT_PRICE + " INTEGER, " +
                KEY_USER_NameFK + " TEXT, " +
                "FOREIGN KEY ("+KEY_USER_NameFK+") REFERENCES "+TABLE_USERS+"("+KEY_NAME+"));";

        db.execSQL(CREATE_PRODUCTS_TABLE);

Insertion code:

public void addProduct(ProductInfo productInfo)
    {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_PRODUCT_TITLE, productInfo.getTitle());
        values.put(KEY_PRODUCT_DETAILS, productInfo.getDescription());
        values.put(KEY_PRODUCT_DATE_OF_POST, productInfo.getDateOfPost().toString());
        values.put(KEY_PRODUCT_IMAGE, productInfo.getThumbnail());
        values.put(KEY_PRODUCT_PHONE, productInfo.getPhone());
        values.put(KEY_PRODUCT_PRICE, productInfo.getPrice());
        values.put(KEY_USER_NameFK, productInfo.getUserNameFK());

        db.insert(TABLE_PRODUCTS, null, values);
        db.close();
    }

retrieving code:

public List<ProductInfo> getAllProducts()
    {
        List<ProductInfo> allProductsList = new ArrayList<ProductInfo>();
        String selectQuery = "SELECT * FROM " + TABLE_PRODUCTS;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        Log.d("Database Operation", "Table Created");
        // looping through all rows and adding to list
        if(cursor.moveToFirst())
        {
            do
            {
                //byte[] c = (cursor.getString(4)).getBytes();
                ProductInfo productInfo = new ProductInfo();
                productInfo.setProductId(Integer.parseInt(cursor.getString(0)));
                productInfo.setTitle(cursor.getString(1));
                productInfo.setDescription(cursor.getString(2));
                productInfo.setDateOfPost(cursor.getString(3));
                productInfo.setThumbnail(cursor.getString(4));
                productInfo.setPhone(cursor.getString(5));
                productInfo.setPrice(cursor.getString(6));
                productInfo.setUserNameFK(cursor.getString(7));

                allProductsList.add(productInfo);

            } while (cursor.moveToNext());
        }           

        db.close();
        return allProductsList;
    }

The image type in the DB should be changed from 'TEXT' to 'BLOB'. The following post explains in detail how to store and retrieve images using SQL lite: [Images & SQL Lite](https://stackoverflow.com/questions/7331310/how-to-store-image-as-blob-in-sqlite-how-to-retrieve-it) — Elletlar, Apr 15 '18 at 23:12

score 1 · Answer 1 · answered Apr 16 '18 at 01:25

Instead of trying to convert the bitmap to a String. Convert the bitmap to a byte[] and store that.

e.g.

public byte[] BitMapToString(Bitmap bitmap){
    ByteArrayOutputStream baos=new ByteArrayOutputStream();
    bitmap.compress(Bitmap.CompressFormat.PNG,100, baos);
    return b=baos.toByteArray();
}

Change your ProductInfo class to so that the member for the image is a byte[] along with change the getters and setters to handle byte[].

The insert member, assuming getThumnail now gets byte array, needs no change and will remain as :-

public void addProduct(ProductInfo productInfo)
    {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_PRODUCT_TITLE, productInfo.getTitle());
        values.put(KEY_PRODUCT_DETAILS, productInfo.getDescription());
        values.put(KEY_PRODUCT_DATE_OF_POST, productInfo.getDateOfPost().toString());
        values.put(KEY_PRODUCT_IMAGE, productInfo.getThumbnail());
        values.put(KEY_PRODUCT_PHONE, productInfo.getPhone());
        values.put(KEY_PRODUCT_PRICE, productInfo.getPrice());
        values.put(KEY_USER_NameFK, productInfo.getUserNameFK());

        db.insert(TABLE_PRODUCTS, null, values);
        db.close();
    }

The data will be store in the KEY_PRODUCT_IMAGE column as a BLOB (irrespective of how the column is defined (as long as the column isn't defined as INTEGER PRIMARY KEY and is therefore an alias of the rowid (the one restriction applied to any value can be stored in any column type)) :-

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class. Datatypes In SQLite Version 3

However, you should really change the column type to BLOB.

You would have to change the getAllProducts method to use the Cursor getBlob method as opposed to the getString method. So that would become :-

public List<ProductInfo> getAllProducts()
    {
        List<ProductInfo> allProductsList = new ArrayList<ProductInfo>();
        String selectQuery = "SELECT * FROM " + TABLE_PRODUCTS;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        Log.d("Database Operation", "Table Created");
        // looping through all rows and adding to list
        if(cursor.moveToFirst())
        {
            do
            {
                //byte[] c = (cursor.getString(4)).getBytes();
                ProductInfo productInfo = new ProductInfo();
                productInfo.setProductId(Integer.parseInt(cursor.getString(0)));
                productInfo.setTitle(cursor.getString(1));
                productInfo.setDescription(cursor.getString(2));
                productInfo.setDateOfPost(cursor.getString(3));
                productInfo.setThumbnail(cursor.getBlob(4)); //<<<< CHANGED
                productInfo.setPhone(cursor.getString(5));
                productInfo.setPrice(cursor.getString(6));
                productInfo.setUserNameFK(cursor.getString(7));

                allProductsList.add(productInfo);

            } while (cursor.moveToNext());
        }           
        db.close();
        return allProductsList;
    }

You would then have to code to convert the byte[] back into an bitmap/image.

Notes on storing Images

Note if the images are large generally greater than 100k or so then storing them in the database can result in problems when retrieving them as well as incurring processing overheads. In such cases it is better to save the images as files and save the path to those files in the database.

Something like `ByteArrayInputStream imageStream = new ByteArrayInputStream(productInfo.getThumbnail); Bitmap yourimage = BitmapFactory.decodeStream(imageStream);` — MikeT, Apr 22 '18 at 20:26
I tried it. also i tried this: Bitmap bm = BitmapFactory.decodeByteArray(img, 0, img.length); but always the resulted Bitmap object is null — Burhan Barhoush, Apr 22 '18 at 21:10

how to get image stored as string in android sqlite operation

1 Answers1

Notes on storing Images