How to Store Image Retrieved from Url in a SQLite Database

How to store image retrieved from url in a SQLite database?

protected Drawable Imagehandler(String url) {
        try {
            url=url.replaceAll(" ", "%20");
            InputStream is = (InputStream)this.fetch(url);
            Drawable d = Drawable.createFromStream(is, "src");
            return d;
        } catch (MalformedURLException e)
        {   
            System.out.println(url);
            System.out.println("error at URI"+e);
            return null;
        } 
        catch (IOException e) 
        {
            System.out.println("io exception: "+e);
            System.out.println("Image NOT FOUND");
            return null;
        } 
    }

    protected Object fetch(String address) throws MalformedURLException,IOException {
        URL url = new URL(address);
        Object content = url.getContent();
        return content;
    }   

this will convert your imageUrl to Drawble at runtime, then set the Drawble to Imageview of Gallery

How to store image in SQLite database

You have to use "blob" to store image.

ex: to store a image in to db:

public void insertImg(int id , Bitmap img ) {   

    byte[] data = getBitmapAsByteArray(img); // this is a function

    insertStatement_logo.bindLong(1, id);       
    insertStatement_logo.bindBlob(2, data);

    insertStatement_logo.executeInsert();
    insertStatement_logo.clearBindings() ;

}

 public static byte[] getBitmapAsByteArray(Bitmap bitmap) {
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    bitmap.compress(CompressFormat.PNG, 0, outputStream);       
    return outputStream.toByteArray();
}

To retrieve a image from db:

public Bitmap getImage(int i){

    String qu = "select img  from table where feedid=" + i ;
    Cursor cur = db.rawQuery(qu, null);

    if (cur.moveToFirst()){
        byte[] imgByte = cur.getBlob(0);
        cur.close();
        return BitmapFactory.decodeByteArray(imgByte, 0, imgByte.length);
    }
    if (cur != null && !cur.isClosed()) {
        cur.close();
    }       

    return null;
} 

Display URL image in sqlite

Use
Picasso

This is very simple library which allows you to download and cache you images.

So in you case you should write something like:

Picasso.with(mContext)
.load(myList.get(position).getSqliteImage())
.into(holder.imageView);

how to store a jpg in an sqlite database with python

Finally I got it working thanks to Andrej Kesely's comment. The working solution is

imgobj = base64.b64encode(open('./fotocopies/checks/624.jpg').read())
con = sqlite3.connect("pybook.db")
cur = con.cursor()
qf="UPDATE data_fotocopies SET fotocopy='%s' WHERE refid=%d AND reftype=0"%(lite.Binary(fotocopy_blob),id)
cur.execute(qf) #yes, it is dangerous for injection`

and retrieving the image from the database is done as:

qf="SELECT fotocopy FROM data_fotocopies WHERE refid=%d and reftype=0"%self.check_id
self.cur.execute(qf)
try:
    fd=base64.b64decode(self.cur.fetchall()[0][0])
    byting = GLib.Bytes(fd)
    self.fotocopy = Gio.MemoryInputStream.new_from_bytes(byting)
    ...
self.fotocopy_ent=self.builder.get_object("fotocopy")         # as it is made in glade
pixbuf = GdkPixbuf.Pixbuf.new_from_stream(self.fotocopy,None) #finally the pixbuf although 
                                                              #it produces errors if I have
                                                              #no stream/image to "feed" it.
self.fotocopy_ent.set_from_pixbuf(pixbuf)

Still can't figure out why all other solutions I've found don't work. I use Python 2.7.6 ang gtk3, but this one I subit does.

Thank you all for your help.

Saving Images into SQLite database

BLOBs are Binary Large OBjects. First you need to convert the image to a binary object.

def convertToBinaryData(imageLocation):
    #Convert digital data to binary format
    with open(imageLocation, 'rb') as file:
        blobData = file.read()
    return blobData

The rest is a simple insert, make sure you are connected. Create an insert statement, inject your binaries into this statement.

 insert = """ INSERT INTO 'images' ('id', 'image') VALUES (?, ?) """
 id = 1
 image = convertToBinary(imageLocation)
 cursor.execute(insert, (id, image))
 connection.commit()

These functions are omitting how to create a connection and get a cursor, however full example can be found at: https://pynative.com/python-sqlite-blob-insert-and-retrieve-digital-data/

How to store(bitmap image) and retrieve image from sqlite database in android?

Setting Up the database

public class DatabaseHelper extends SQLiteOpenHelper {
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "database_name";

    // Table Names
    private static final String DB_TABLE = "table_image";

    // column names
    private static final String KEY_NAME = "image_name";
    private static final String KEY_IMAGE = "image_data";

    // Table create statement
    private static final String CREATE_TABLE_IMAGE = "CREATE TABLE " + DB_TABLE + "("+ 
                       KEY_NAME + " TEXT," + 
                       KEY_IMAGE + " BLOB);";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        // creating table
        db.execSQL(CREATE_TABLE_IMAGE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);

        // create new table
        onCreate(db);
    }
}

Insert in the Database:

public void addEntry( String name, byte[] image) throws SQLiteException{
    SQLiteDatabase database = this.getWritableDatabase();
    ContentValues cv = new  ContentValues();
    cv.put(KEY_NAME,    name);
    cv.put(KEY_IMAGE,   image);
    database.insert( DB_TABLE, null, cv );
}

Retrieving data:

 byte[] image = cursor.getBlob(1);

Note:

1. Before inserting into database, you need to convert your Bitmap image into byte array first then apply it using database query.
2. When retrieving from database, you certainly have a byte array of image, what you need to do is to convert byte array back to original image. So, you have to make use of BitmapFactory to decode.

Below is an Utility class which I hope could help you:

public class DbBitmapUtility {

    // convert from bitmap to byte array
    public static byte[] getBytes(Bitmap bitmap) {
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        bitmap.compress(CompressFormat.PNG, 0, stream);
        return stream.toByteArray();
    }

    // convert from byte array to bitmap
    public static Bitmap getImage(byte[] image) {
        return BitmapFactory.decodeByteArray(image, 0, image.length);
    }
}

Further reading

If you are not familiar how to insert and retrieve into a database, go through this tutorial.

---

Related Topics