how to store Image as blob in Sqlite & how to retrieve it?

Question

I want to store an image(from url) into a sqlite database.

For that I use:

db = new DataBase(getApplicationContext());
URL url = new URL("http://sree.cc/wp-content/uploads/schogini_team.png");
URLConnection ucon = url.openConnection();
InputStream is = ucon.getInputStream();
BufferedInputStream bis = new BufferedInputStream(is,128);
ByteArrayBuffer barb= new ByteArrayBuffer(128);

int current = 0;
while ((current = bis.read()) != -1) {
    barb.append((byte) current);
}

ContentValues filedata= new ContentValues();

filedata.put(DataBase.IMG_SRC,barb.toByteArray());

db.insert(DataBase.Table_Img, null, filedata);

In the Insert():

public void insert(String tableImg, Object object,
        ContentValues dataToInsert) {
    // TODO Auto-generated method stub
    String sql = "INSERT INTO "+tableImg+" ("+ID+","+IMG_SRC+") " +
            "VALUES ('"+1+"','"+dataToInsert+"')";
    db.execSQL(sql);
}

For the retrieval of image:

Cursor cursor = db.selectDataToShow(DataBase.Table_Img, DataBase.IMG_SRC);

byte[] imageByteArray=cursor.getBlob(cursor.getColumnIndex(DataBase.IMG_SRC));      
cursor.close();

ByteArrayInputStream imageStream = new ByteArrayInputStream(imageByteArray);
Bitmap theImage = BitmapFactory.decodeStream(imageStream);

System.out.println(">>>>>>>>>>>>>>>>>>>>>> "+theImage);

So here I got null.

And in my database the value of image stored as: Image=[B@43e5ac48]

in fetching db i got not exactly byte[]..... any help? – Siten Sep 08 '11 at 09:31 — Siten, Sep 08 '11 at 09:31

score 70 · Accepted Answer · edited May 08 '19 at 06:05

Here the code i used for my app

This code will take a image from url and convert is to a byte array

byte[] logoImage = getLogoImage(IMAGEURL);

private byte[] getLogoImage(String url){
     try {
             URL imageUrl = new URL(url);
             URLConnection ucon = imageUrl.openConnection();

             InputStream is = ucon.getInputStream();
             BufferedInputStream bis = new BufferedInputStream(is);

             ByteArrayBuffer baf = new ByteArrayBuffer(500);
             int current = 0;
             while ((current = bis.read()) != -1) {
                  baf.append((byte) current);
             }

             return baf.toByteArray();
     } catch (Exception e) {
          Log.d("ImageManager", "Error: " + e.toString());
     }
     return null;
}

To save the image to db i used this code.

 public void insertUser(){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String delSql = "DELETE FROM ACCOUNTS";
        SQLiteStatement delStmt = db.compileStatement(delSql);
        delStmt.execute();

        String sql = "INSERT INTO ACCOUNTS (account_id,account_name,account_image) VALUES(?,?,?)";
        SQLiteStatement insertStmt = db.compileStatement(sql);
        insertStmt.clearBindings();
        insertStmt.bindString(1, Integer.toString(this.accId));
        insertStmt.bindString(2,this.accName);
        insertStmt.bindBlob(3, this.accImage);
        insertStmt.executeInsert();
        db.close();
}

To retrieve the image back this is code i used.

public Account getCurrentAccount() {
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    String sql = "SELECT * FROM ACCOUNTS";
    Cursor cursor = db.rawQuery(sql, new String[] {});

    if(cursor.moveToFirst()){
        this.accId  = cursor.getInt(0);
        this.accName = cursor.getString(1);
        this.accImage = cursor.getBlob(2);
    }
    if (cursor != null && !cursor.isClosed()) {
        cursor.close();
    }
    db.close();
    if(cursor.getCount() == 0){
        return null;
    } else {
        return this;
    }
}

Finally to load this image to a imageview

logoImage.setImageBitmap(BitmapFactory.decodeByteArray( currentAccount.accImage, 
        0,currentAccount.accImage.length));

insertStmt.bindBlob(3, this.accImage); is used to insert the image where accImage is a byte array returned from getLogoImage method in first code block — blessanm86, Sep 07 '11 at 11:18
Are you able to set the byte array to a image view right after you convert the url to a byte array. If you are able to view the image then. Then we can know that something is going wronng in the db operations. Otherwise it an error in the way you access the image from the server. — blessanm86, Sep 07 '11 at 13:44
@blessenm I'm doing something like this. But with more pictures/byte array in sqlite..How can I avoid the bitmap out of memory error? — Mari_Yaguchi, Aug 27 '14 at 15:46
The cursor null check is pointless : you've already used it with the moveToFirst call. — RichieHH, Oct 10 '14 at 06:47
Why this ByteArrayBuffer not found, kindly help me ByteArrayBuffer baf = new ByteArrayBuffer(500); — Asad Mukhtar, Jan 21 '18 at 18:06

score 24 · Answer 2 · edited Jun 23 '16 at 15:20

in the DBAdaper i.e Data Base helper class declare the table like this

 private static final String USERDETAILS=
    "create table userdetails(usersno integer primary key autoincrement,userid text not null ,username text not null,password text not null,photo BLOB,visibility text not null);";

insert the values like this,

first convert the images as byte[]

ByteArrayOutputStream baos = new ByteArrayOutputStream();  
Bitmap bitmap = ((BitmapDrawable)getResources().getDrawable(R.drawable.common)).getBitmap();
bitmap.compress(Bitmap.CompressFormat.PNG, 100, baos);   
byte[] photo = baos.toByteArray(); 
db.insertUserDetails(value1,value2, value3, photo,value2);

in DEAdaper class

 public long insertUserDetails(String uname,String userid, String pass, byte[] photo,String visibility) 
{
    ContentValues initialValues = new ContentValues();
    initialValues.put("username", uname);
    initialValues.put("userid",userid);
    initialValues.put("password", pass);
    initialValues.put("photo",photo);
    initialValues.put("visibility",visibility);
    return db.insert("userdetails", null, initialValues);
}

retrieve the image as follows

Cursor cur=your query;
while(cur.moveToNext())
{
     byte[] photo=cur.getBlob(index of blob cloumn);
}

convert the byte[] into image

ByteArrayInputStream imageStream = new ByteArrayInputStream(photo);
Bitmap theImage= BitmapFactory.decodeStream(imageStream);

I think this content may solve your problem

first check whether you retrieved the byte[] from cursor or not,using imageByteArray.length variable — Balaji.K, Sep 07 '11 at 10:49
check byte[] length i.e photo.lenght. If the value is greater than zero,then surely you will get the correct bitmap. If the length is zero,then check ur query. — Balaji.K, Sep 07 '11 at 12:35
"System.out.println(">>>>>>>>>>> "+imageByteArray.length);" what it prints — Balaji.K, Sep 07 '11 at 12:59
why `bitmap.compress(Bitmap.CompressFormat.PNG, 100, baos); `?? What about I do not want to compress my image? — Paranoid Android, Nov 14 '12 at 12:12
found my answer here http://stackoverflow.com/questions/10191871/converting-bitmap-to-bytearray-android — Paranoid Android, Nov 14 '12 at 12:14

score 1 · Answer 3 · answered Sep 07 '11 at 09:34

1

In insert()

public void insert(String tableImg, Object object,
        ContentValues dataToInsert) {

   db.insert(tablename, null, dataToInsert);
}

Hope it helps you.

answered Sep 07 '11 at 09:34

Nishant Shah

3,442
5
25
34

score 0 · Answer 4 · answered May 05 '15 at 14:38

for a ionic project


    var imgURI       = "";
    var imgBBDD      = ""; //sqllite for save into

    function takepicture() {
                var options = {
                    quality : 75,
                    destinationType : Camera.DestinationType.DATA_URL,
                    sourceType : Camera.PictureSourceType.CAMERA,
                    allowEdit : true,
                    encodingType: Camera.EncodingType.JPEG,
                    targetWidth: 300,
                    targetHeight: 300,
                    popoverOptions: CameraPopoverOptions,
                    saveToPhotoAlbum: false
                };

                $cordovaCamera.getPicture(options).then(function(imageData) {
                    imgURI = "data:image/jpeg;base64," + imageData;
                    imgBBDD = imageData;
                }, function(err) {
                    // An error occured. Show a message to the user
                });
            }

And now we put imgBBDD into SqlLite


     function saveImage = function (theId, theimage){
      var insertQuery = "INSERT INTO images(id, image) VALUES("+theId+", '"+theimage+"');"
      console.log('>>>>>>>');
      DDBB.SelectQuery(insertQuery)
                    .then( function(result) {
                        console.log("Image saved");
                    })
                    .catch( function(err) 
                     {
                        deferred.resolve(err);
                        return cb(err);
                    });
    }

A server side (php)


        $request = file_get_contents("php://input"); // gets the raw data
        $dades = json_decode($request,true); // true for return as array


    if($dades==""){
            $array = array();
            $array['error'] = -1;
            $array['descError'] = "Error when get the file";
            $array['logError'] = '';
            echo json_encode($array);
            exit;
        }
        //send the image again to the client
        header('Content-Type: image/jpeg');
        echo '';

Bhhruguni · Answer 5 · 2017-04-15T10:06:05.393

0

byte[] byteArray = rs.getBytes("columnname");  

Bitmap bm = BitmapFactory.decodeByteArray(byteArray, 0 ,byteArray.length);

edited Apr 15 '17 at 10:06

answered Apr 12 '17 at 11:35

Bhhruguni

87
1
2
9

score -1 · Answer 6 · answered Jul 25 '13 at 12:27

you may also want to encode and decode to/from base64

    function uncompress(str:String):ByteArray {
            import mx.utils.Base64Decoder;
            var dec:Base64Decoder = new Base64Decoder();
            dec.decode(str);
            var newByteArr:ByteArray=dec.toByteArray();        
            return newByteArr;
        }


    // Compress a ByteArray into a Base64 String.
    function compress(bytes:ByteArray):String { 
        import mx.utils.Base64Decoder; //Transform String in a ByteArray.
        import mx.utils.Base64Encoder; //Transform ByteArray in a readable string.
        var enc:Base64Encoder = new Base64Encoder();    
        enc.encodeBytes(bytes);
        return enc.drain().split("\n").join("");
    }

There's no good reason to go via base64 when you can store a byte array as this would just increase the storage required (and waste unnecessary time encoding). Base64 encoded is hardly readable. — Sveinung Kval Bakken, May 01 '19 at 22:51

how to store Image as blob in Sqlite & how to retrieve it?

6 Answers6

Linked

Related