Rectangle 27 123

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 );
}
byte[] image = cursor.getBlob(1);
  • Before inserting into database, you need to convert your Bitmap image into byte array first then apply it using database query.
  • 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.

@Lazy Ninja, add a quote(") before CREATE TABLE, i'm not able to do this minor edit.

@LazyNinja, I am using your answer to help me store and retreive bitmap images and strings. This post is so helpful! I had a question about how to "Insert into the Database". On the line "database.insert( DB_TABLE, null, cv );", how are you defining database?

@Natalie use SQLiteDatabase database = this.getWritableDatabase(); I have added it in the source code. And I think the following link could be useful: mrbool.com/how-to-insert-data-into-a-sqlite-database-in-android/

Very clear and helpful.Thanks a ton @LazyNinja

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

android image sqlite bitmapimage
Rectangle 27 17

CREATE TABLE " + DB_TABLE + "("+ 
                   KEY_NAME + " TEXT," + 
                   KEY_IMAGE + " BLOB);";

Insert in the Database:

public void addEntry( String name, byte[] image) throws SQLiteException{
    ContentValues cv = new  ContentValues();
    cv.put(KEY_NAME,    name);
    cv.put(KEY_IMAGE,   image);
    database.insert( DB_TABLE, null, cv );
}
byte[] image = cursor.getBlob(1);
  • Before inserting into database, you need to convert your Bitmap image into byte array first then apply it using database query.
  • 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);
    }
}

Nice, but you should dispose the stream in getBytes(Bitmap bitmap)

stream.close
return stream.toByteArray();

android - How to store image in SQLite database - Stack Overflow

android sqlite android-emulator
Rectangle 27 15

CREATE TABLE " + DB_TABLE + "("+ 
                   KEY_NAME + " TEXT," + 
                   KEY_IMAGE + " BLOB);";

Insert in the Database:

public void addEntry( String name, byte[] image) throws SQLiteException{
    ContentValues cv = new  ContentValues();
    cv.put(KEY_NAME,    name);
    cv.put(KEY_IMAGE,   image);
    database.insert( DB_TABLE, null, cv );
}
byte[] image = cursor.getBlob(1);
  • Before inserting into database, you need to convert your Bitmap image into byte array first then apply it using database query.
  • 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);
    }
}

Nice, but you should dispose the stream in getBytes(Bitmap bitmap)

stream.close
return stream.toByteArray();

android - How to store image in SQLite database - Stack Overflow

android sqlite android-emulator
Rectangle 27 0

This is an answer to the question in the title, as it ranks #1 for [php sqlite blob]. This doesn't use PDO as I had trouble with it. You have to use prepared statement, you usually can't execute a literal SQL insert as the size is too big with a blob. This is an update rather than an insert but it's basically the same.

// example variables
$row_id=1;
$image_filename="/home/mywebsite/public_html/images/an_image.png";
$sqlite_table_name="user_images";
$database_filename="database.sqlite";
// the blob field in the sqlite table is called ZIMAGE and the id field is ZID.

// Code
if (file_exists($image_filename)) {
$db = new SQLite3($database_filename);
$query = $db->prepare("UPDATE '{$sqlite_table_name}' SET ZIMAGE=? WHERE ZID=?");
$image=file_get_contents($image_filename);
$query->bindValue(1, $image, SQLITE3_BLOB);
$query->bindValue(2, $row_id, SQLITE3_TEXT);
$run=$query->execute();
}

I don't think there's any hard rules in programming, I store images in blobs in Sqlite in certain situations. I also scrape webpages with regular expressions instead of Xpath! Whatever gets the job done.

People, keep in mind $db->escapeString is not Binary Safe as I thought it was, so thank you for this solution!

PHP: Inserting Blob Image to SQLite table - Stack Overflow

php sqlite cakephp blob
Rectangle 27 0

There's nothing special in storing image to SQLite. Just create table with BLOB record type and do smth like:

protected long saveBitmap(SQLiteDatabase database, Bitmap bmp)
{
    int size = bmp.getRowBytes() * bmp.getHeight(); 
    ByteBuffer b = ByteBuffer.allocate(size); bmp.copyPixelsToBuffer(b); 
    byte[] bytes = new byte[size];
    b.get(bytes, 0, bytes.length);
    ContentValues cv=new ContentValues();
    cv.put(CHUNK, bytes);
    this.id= database.insert(TABLE, null, cv);
}

Probably you migth want to save image chunk by chunk, since there's limits/recommended BLOB size (don't really recall how much)

can I only insert bitmap pictures or does it also work with gif's png's and so on?

It doesn't matter which format of image u're going to insert. Any image can be represented as Android Bitmap - which is not actually Windows BMP (name is confusing), but just internal representation of PNG/GIF/JPEG/and so on in Android

android - How to save images into Database - Stack Overflow

android database sqlite image
Rectangle 27 0

This is an answer to the question in the title, as it ranks #1 for [php sqlite blob]. This doesn't use PDO as I had trouble with it. You have to use prepared statement, you usually can't execute a literal SQL insert as the size is too big with a blob. This is an update rather than an insert but it's basically the same.

// example variables
$row_id=1;
$image_filename="/home/mywebsite/public_html/images/an_image.png";
$sqlite_table_name="user_images";
$database_filename="database.sqlite";
// the blob field in the sqlite table is called ZIMAGE and the id field is ZID.

// Code
if (file_exists($image_filename)) {
$db = new SQLite3($database_filename);
$query = $db->prepare("UPDATE '{$sqlite_table_name}' SET ZIMAGE=? WHERE ZID=?");
$image=file_get_contents($image_filename);
$query->bindValue(1, $image, SQLITE3_BLOB);
$query->bindValue(2, $row_id, SQLITE3_TEXT);
$run=$query->execute();
}

I don't think there's any hard rules in programming, I store images in blobs in Sqlite in certain situations. I also scrape webpages with regular expressions instead of Xpath! Whatever gets the job done.

People, keep in mind $db->escapeString is not Binary Safe as I thought it was, so thank you for this solution!

PHP: Inserting Blob Image to SQLite table - Stack Overflow

php sqlite cakephp blob
Rectangle 27 0

I know my answer is a too late,but it might be useful for some one who view this question,usually while stroring images in sqlite database table using BLOB,that will have an affect on the performance of database and will slow down the loading of data,when records start to increase in numbers.Without using BLOB,you can save and retrieve images from database,here is the post/link that will help you out to carry on and achieve the task

Hope it helps some one who are struck with images storage in database :)

iphone - How to Save And Retrieve Image From Sqlite Database? - Stack ...

iphone database xcode uiimage
Rectangle 27 0

This is not an answer, but there's 'unclarity' in this question, too long to put it as a comment

  • You mention CakePHP, but there's no CakePHP involved in your question
  • You mention 'SQLite', but you're using mysql_real_escape?

Regarding MySQL and storing images inside the database;

  • try to determin if you really need to store your image data inside the database, generally, it's better to store the image itself in the file system and the name of the image in the database

Here is a generic PDO step-by-step walk through for BLOBs;

PHP: Inserting Blob Image to SQLite table - Stack Overflow

php sqlite cakephp blob
Rectangle 27 0

You can save your images in Sqlite as blob and can retrieve it when required. Create table as

create table sometable(id integer primary key autoincrement,photo BLOB);

And save the image as

//convert image into 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();

//And now store this image
ContentValues initialValues = new ContentValues();   
initialValues.put("photo",photo);
return db.insert("sometable", null, initialValues);

And retrieve image as

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

json - android - How do I caching image and display offline? - Stack O...

android json sqlite caching
Rectangle 27 0

You can use SQLite Manager or Mozila SQLite Plugin(Google it). There you can add column for the image in your table of BLOB data type. Here you can easily add image. This is the best way as far i can tell you.

I made a table and inserted values in it through the terminal. When I select the values, I do find the values as well. But when I drag-drop it and copy it in Xcode4 and run a select query it states the table doesn't exist. Can you please guide me on this error?

why you are not going for a new one?

iphone - Insert an image (located on desktop) into the sqite3 database...

iphone sqlite xcode4 sqlite3 insert
Rectangle 27 0

This is not an answer, but there's 'unclarity' in this question, too long to put it as a comment

  • You mention CakePHP, but there's no CakePHP involved in your question
  • You mention 'SQLite', but you're using mysql_real_escape?

Regarding MySQL and storing images inside the database;

  • try to determin if you really need to store your image data inside the database, generally, it's better to store the image itself in the file system and the name of the image in the database

Here is a generic PDO step-by-step walk through for BLOBs;

PHP: Inserting Blob Image to SQLite table - Stack Overflow

php sqlite cakephp blob
Rectangle 27 0

There's nothing special in storing image to SQLite. Just create table with BLOB record type and do smth like:

protected long saveBitmap(SQLiteDatabase database, Bitmap bmp)
{
    int size = bmp.getRowBytes() * bmp.getHeight(); 
    ByteBuffer b = ByteBuffer.allocate(size); bmp.copyPixelsToBuffer(b); 
    byte[] bytes = new byte[size];
    b.get(bytes, 0, bytes.length);
    ContentValues cv=new ContentValues();
    cv.put(CHUNK, bytes);
    this.id= database.insert(TABLE, null, cv);
}

Probably you migth want to save image chunk by chunk, since there's limits/recommended BLOB size (don't really recall how much)

can I only insert bitmap pictures or does it also work with gif's png's and so on?

It doesn't matter which format of image u're going to insert. Any image can be represented as Android Bitmap - which is not actually Windows BMP (name is confusing), but just internal representation of PNG/GIF/JPEG/and so on in Android

android - How to save images into Database - Stack Overflow

android database sqlite image