How to create a Table with a column of type BLOB in a DBAdapter

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

Solution

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>