How to create a Table with a column of type BLOB i

Question

I have a global DBAdapter and also for each Table one. In my global DB-Adapter I added the type "BLOB" to the column. But i don't get what i have to change in my DBAdapter for the specific Table. What I need to change there that it also works with the BLOB type.

So here you go:

Global DBAdapter:

package de.retowaelchli.filterit.database;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import de.retowaelchli.filterit.database.ADFilterDBAdapter;

public class DBAdapter {

    public static final String DATABASE_NAME = "filterit";

    public static final int DATABASE_VERSION = 1;

    public static final String CREATE_TABLE_ADFILTER = "create table adfilter (_id integer primary key autoincrement, "
    + ADFilterDBAdapter.NAME+","
    + ADFilterDBAdapter.KEYWORD+","
    + ADFilterDBAdapter.CACHE + ");";

    private static final String CREATE_TABLE_SFILTER = "create table sfilter (_id integer primary key autoincrement, "
    +SFilterDBAdapter.NAME+","
    +SFilterDBAdapter.KEYWORD+","
    +SFilterDBAdapter.SMILEY+ ");";

    private static final String CREATE_TABLE_ADMESSAGES = "create table admessages (_id integer primary key autoincrement, "
    +MessagesDBAdapter.PHONENUMBER+","
    +MessagesDBAdapter.MESSAGE+ ");";

    //HERE I CHANGED IT TO BLOB!

    private static final String CREATE_TABLE_SMILEY = " create table smiley (_id integer primary key autoincrement, "
    +SmileyDBAdapter.SOURCE+" BLOB ,"
    +SmileyDBAdapter.INFO+ ");";


    private final Context context; 
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    /**
     * Constructor
     * @param ctx
     */
    public DBAdapter(Context ctx)
    {
        this.context = ctx;

    }

    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(CREATE_TABLE_ADFILTER);
            db.execSQL(CREATE_TABLE_SFILTER);
            db.execSQL(CREATE_TABLE_ADMESSAGES);
            db.execSQL(CREATE_TABLE_SMILEY);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
        int newVersion) 
        {               
            // Adding any table mods to this guy here
        }
    } 

   /**
     * open the db
     * @return this
     * @throws SQLException
     * return type: DBAdapter
     */
    public DBAdapter open() throws SQLException 
    {
        this.DBHelper = new DatabaseHelper(this.context);
        this.db = this.DBHelper.getWritableDatabase();
        return this;
    }

    /**
     * close the db 
     * return type: void
     */
    public void close() 
    {
        this.DBHelper.close();
    }

}

This is my DBAdapter of this Table:

package de.retowaelchli.filterit.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SmileyDBAdapter {

        public static final String ROW_ID = "_id";
        public static final String SOURCE = "source";
        public static final String INFO = "info";

        private static final String DATABASE_TABLE = "admessages";

        private DatabaseHelper mDbHelper;
        private SQLiteDatabase mDb;

        private final Context mCtx;

        private static class DatabaseHelper extends SQLiteOpenHelper {

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

            @Override
            public void onCreate(SQLiteDatabase db) {
            }

            @Override
            public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            }
        }

        /**
         * Constructor - takes the context to allow the database to be
         * opened/created
         * 
         * @param ctx
         *            the Context within which to work
         */
        public SmileyDBAdapter(Context ctx) {
            this.mCtx = ctx;
        }


        public SmileyDBAdapter open() throws SQLException {
            this.mDbHelper = new DatabaseHelper(this.mCtx);
            this.mDb = this.mDbHelper.getWritableDatabase();
            return this;
        }

        /**
         * close return type: void
         */
        public void close() {
            this.mDbHelper.close();
        }


        public long createSmiley(String source, String info ){
            ContentValues initialValues = new ContentValues();
            initialValues.put(SOURCE, source);
            initialValues.put(INFO, info);
            return this.mDb.insert(DATABASE_TABLE, null, initialValues);
        }


        public boolean deleteSmiley(long rowId) {

            return this.mDb.delete(DATABASE_TABLE, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$
        }


        public Cursor getAllSmileys() {

            return this.mDb.query(DATABASE_TABLE, new String[] { ROW_ID,
                    SOURCE, INFO }, null, null, null, null, null);
        }


        public Cursor getSmiley(long rowId) throws SQLException {

            Cursor mCursor =

            this.mDb.query(true, DATABASE_TABLE, new String[] { ROW_ID, SOURCE,
                    INFO }, ROW_ID + "=" + rowId, null, null, null, null, null);
            if (mCursor != null) {
                mCursor.moveToFirst();
            }
            return mCursor;
        }


        public boolean updateSmiley(long rowId, String source, String info,
                String cache){
            ContentValues args = new ContentValues();
            args.put(SOURCE, source);
            args.put(INFO, info);

            return this.mDb.update(DATABASE_TABLE, args, ROW_ID + "=" + rowId, null) >0; 
        }

    }

What I need to change in my SmileyDBAdapter to bring it working. That it supports the type BLOB. Im really frustrated after trying this for serval hours -.-

Thx in Advance for your Answer

Best Regards

safari

Answer 1

I really want to display image in list or gridview by fetching from sqlite db and after searching i found one article which work for me. I hope it will helpful for you. Insert Image in SQLite and Display in ListView

Answer 2

Check this Code for Inserting the Image from the Web link and store in the Database as BLOB and shown in ImageView .

Java Code :

CategoryDetails.Java

public class CategoryDetails extends Activity {

public NotesDbAdapter mDbHelper;
ByteArrayBuffer baf ;
 private Cursor mNotesCursor;
ImageView img;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);


    mDbHelper = new NotesDbAdapter(this);
    mDbHelper.open();
    new Task_like_flag().execute();

    img=(ImageView)findViewById(R.id.ImageView01);

}


public Bitmap convertBlobToBitmap(byte[] blobByteArray) {       
    Bitmap tempBitmap=null;        
    if(blobByteArray!=null)
    tempBitmap = BitmapFactory.decodeByteArray(blobByteArray, 0, blobByteArray.length);
    return tempBitmap;
}
      public class Task_like_flag extends AsyncTask<String, Void, Void> {
            private final ProgressDialog dialog = new ProgressDialog(CategoryDetails.this);
            JSONObject object_feed;
            // can use UI thread here
            protected void onPreExecute() {
              this.dialog.setMessage("Loading...");
              this.dialog.setCancelable(false);
              this.dialog.show();
            }

            @Override
            protected Void doInBackground(String... params) {
                URL url = null;
                try {
                    url = new URL("http://www.theblacksheeponline.com/uploaded/Quick_images/681314276069brewehas.jpg");
                } catch (MalformedURLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }  //http://example.com/image.jpg
                //open the connection
                URLConnection ucon = null;
                try {
                    ucon = url.openConnection();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                //buffer the download
                InputStream is = null;
                try {
                    is = ucon.getInputStream();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                BufferedInputStream bis = new BufferedInputStream(is,128);
                baf = new ByteArrayBuffer(128);
                //get the bytes one by one
                int current = 0;
                try {
                    while ((current = bis.read()) != -1) {
                            baf.append((byte) current);
                    }
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                  mDbHelper.createNote(baf.toByteArray());
                return null;
            }

              @Override
              protected void onPostExecute(Void result) {
                  byte[] imageByteArray;
                  Bitmap theImage = null;
                try{
                      mNotesCursor = mDbHelper.fetchAllNotes();
                      startManagingCursor(mNotesCursor);

                    if (mNotesCursor.moveToFirst()) {


                      do {
                          imageByteArray   =  mNotesCursor.getBlob(mNotesCursor.getColumnIndex(NotesDbAdapter.KEY_IMAGE));
                          ByteArrayInputStream imageStream = new ByteArrayInputStream(imageByteArray);
                          theImage= BitmapFactory.decodeStream(imageStream);

                      } while (mNotesCursor.moveToNext());
                  }
                }catch(Exception e){
                  Log.v("Excep", ""+e);
                }
                img.setImageBitmap(theImage);
                  if (this.dialog.isShowing()) {
                      this.dialog.dismiss();
                    }
              }
            }

  }

NotesDbAdapter.Class

public class NotesDbAdapter {

public static final String KEY_CATEGORY = "category";
public static final String KEY_DATE = "notes_date";
public static final String KEY_DESC = "item_desc";
public static final String KEY_PRIZE = "item_prize";
public static final String KEY_MODE = "mode";
public static final String KEY_MONTH = "month";
public static final String KEY_IMAGE = "img";


public static final String KEY_ROWID = "_id";

private static final String TAG = "NotesDbAdapter";
private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;

/**
 * Database creation sql statement
 */
private static final String DATABASE_CREATE =
        "create table notes (_id integer primary key autoincrement, "
                + "img BLOB not null);";



private static final String DATABASE_NAME = "data";
private static final String DATABASE_TABLE = "notes";
private static final int DATABASE_VERSION = 2;

private final Context mCtx;

private static class DatabaseHelper extends SQLiteOpenHelper {

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

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS notes");
        onCreate(db);
    }
}

/**
 * Constructor - takes the context to allow the database to be
 * opened/created
 * 
 * @param ctx the Context within which to work
 */
public NotesDbAdapter(Context ctx) {
    this.mCtx = ctx;
}

/**
 * Open the notes database. If it cannot be opened, try to create a new
 * instance of the database. If it cannot be created, throw an exception to
 * signal the failure
 * 
 * @return this (self reference, allowing this to be chained in an
 *         initialization call)
 * @throws SQLException if the database could be neither opened or created
 */
public NotesDbAdapter open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

public void close() {
    mDbHelper.close();
}


/**
 * Create a new note using the title and body provided. If the note is
 * successfully created return the new rowId for that note, otherwise return
 * a -1 to indicate failure.
 * 
 * @param title the title of the note
 * @param body the body of the note
 * @return rowId or -1 if failed
 */
public long createNote(byte[] img) {
     byte yes[]=img;
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_IMAGE, yes);
    Log.v("row", ""+mDb.insert(DATABASE_TABLE, null, initialValues));
    return mDb.insert(DATABASE_TABLE, null, initialValues);

}


/**
 * Return a Cursor over the list of all notes in the database
 * 
 * @return Cursor over all notes
 */
public Cursor fetchAllNotes() {
    return mDb.query(DATABASE_TABLE, null, null, null, null, null, null);
} }

Main.xml :

<?xml version="1.0" encoding="utf-8"?>
   <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
   >

  <ImageView        android:id="@+id/ImageView01"       android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:src="@drawable/icon"/>
 </LinearLayout>

Let me know if you find any Difficulty.

Thanks Venky..