how to add image to sqlite

Question

I am using SQLite DataBase to store various variables data in my Android App. Using a ImagePicker, I am taking Image from gallery to ImageHolder and I need to store those into database.

I have created the column_4_image. Not able to figure out, How to store Image to database. Found similar question, but they were not answering when will I get errors, if my Image size is above certain limit. How can I know the device limit image pushing limits.

This is my SQLiteHelper.java class

  @Override
    public void onCreate(SQLiteDatabase database) {

        String CREATE_TABLE="CREATE TABLE IF NOT EXISTS "+TABLE_NAME+" ("
                +Table_Column_ID+" INTEGER PRIMARY KEY, "
                +Table_Column_1_Name+" VARCHAR, "
                +Table_Column_2_PhoneNumber +" VARCHAR, "
                +Table_Column_3_Date+" VARCHAR,"
                +Table_Column_4_Image+" BLOB)";
        database.execSQL(CREATE_TABLE);
    }

This is my addDataActivity.java class

public void SQLiteTableBuild(){
    sqLiteDatabaseObj.execSQL("CREATE TABLE IF NOT EXISTS "
            +SQLiteHelper.TABLE_NAME+"(id "
            +SQLiteHelper.Table_Column_ID +" PRIMARY KEY AUTOINCREMENT NOT NULL, "
            +SQLiteHelper.Table_Column_1_Name+" VARCHAR, "
            +SQLiteHelper.Table_Column_2_PhoneNumber+" VARCHAR, "
            +SQLiteHelper.Table_Column_3_Date+" VARCHAR, "
            +SQLiteHelper.Table_Column_4_Image+" BLOB);");
}


public void InsertDataIntoSQLiteDatabase(){
    if(EditTextEmptyHold){
        SQLiteDataBaseQueryHolder = "INSERT INTO "+SQLiteHelper.TABLE_NAME+" (name,phone_number,date) VALUES('"+NameHolder+"', '"+NumberHolder+"','"+DateHolder+"');";
        sqLiteDatabaseObj.execSQL(SQLiteDataBaseQueryHolder);
        sqLiteDatabaseObj.close();
    } //else data insertion failed
}

Possible duplicate of [How to store image in SQLite database](https://stackoverflow.com/questions/9357668/how-to-store-image-in-sqlite-database) — Sunil Sunny, Dec 28 '17 at 09:56
Check your create table statement, you are missing a space before BLOB. Here Table_Column_4_Image+"BLOB)"; — asish, Dec 28 '17 at 09:58
SQLiteHelper.Table_Column_4_Image+"BLOB); change it to SQLiteHelper.Table_Column_4_Image+" BLOB);") — Satyam Anand, Dec 28 '17 at 10:06
Ok guyz I just want to know how can I take image on button click and put it in sqlite db by converting it, in my code. I have seen many questions but I'm not able to link it to my problem — Shubham Jangle, Dec 28 '17 at 10:23
instead of storing images in db, just save the image in your device, and in your db, save the location with filename in some column — Mohd Asif Ahmed, Dec 28 '17 at 10:40

MikeT · Accepted Answer · 2017-12-28T11:15:42.423

1) Extract the image from the source file as a byte array.

2) Prepare a ContentValues instance and populate it, then use the insert method e.g :-

    ContentValues cv = new ContentValues();
    cv.put(SQLiteHelper.Table_Column_1_Name,NameHolder);
    cv.put(SQLiteHelper.Table_Column_2_PhoneNumber,NumberHolder);
    cv.put(SQLiteHelper.Table_Column_3_Date,DateHolder);
    cv.put(SQLiteHelper.Table_Column_4_Image,your_image_as_a_byte_array);
    long result = sqLiteDatabaseObj.insert(SQLiteHelper.TABLE_NAME,null,cv);

To retrieve get the byte array using getBlob method e.g.

    byte[] myimageasbytearray = cursor.getBlob(cusror.getColumnIndex(SQLiteHelper.Table_Column_4_Image));

However, storing images themselves is not recommended.

A Cursor Window is limited to 2Mb thus images will greatly increase the chance of errors occurring due to this limitation.
Disk access(IO) will be significantly greater.
Data will fill the cache(s) thus again likely further increasing Disk access(IO).

The recommended way is to store the path to the image in the database.

how to add image to sqlite

1 Answers1