Insertion of images to SQL

Question

I Googled some tutorials on the SQL database. And I faced some problem in inserting images into my database. I had tried many ways (such as setImageBitmap, setImageDrawable, setImageResource), but non of them work. I already inserted some of the images into my drawable-hdpi.

Here's some of my code for insertion:

public void insertIntoTable(){
    try{
        mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);

        mydb.execSQL("INSERT INTO " + TABLE + "(DNAME, DTAG, DSUMMARY, DINGRE, DSTEP) VALUES('Chicken Cabonara','Chicken','aaa', '3', '5')");
        mydb.execSQL("INSERT INTO " + TABLE + "(DNAME, DTAG, DSUMMARY, DINGRE, DSTEP) VALUES('Seafood Spagetti','Fish','bbb', '5', '3')");
        mydb.execSQL("INSERT INTO " + TABLE + "(DNAME, DTAG, DSUMMARY, DINGRE, DSTEP) VALUES('Tomyam Soup','Soup','ccc', '3', '1')");

       mydb.close();
    }
}

My code for showing data:

public void showTableValues(){
    try{
        mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
        Cursor allrows  = mydb.rawQuery("SELECT * FROM "+  TABLE, null);
        System.out.println("COUNT : " + allrows.getCount());
        Integer cindex = allrows.getColumnIndex("DNAME");
        Integer cindex1 = allrows.getColumnIndex("DTAG");
        Integer cindex2 = allrows.getColumnIndex("DSUMMARY");
        Integer cindex3 = allrows.getColumnIndex("DINGRE");
        Integer cindex4 = allrows.getColumnIndex("DSTEP");

        TextView t = new TextView(this);
        t.setText("========================================");
        Linear.addView(t);

        if(allrows.moveToFirst()){
            do{
                LinearLayout did_row   = new LinearLayout(this);
                LinearLayout dname_row = new LinearLayout(this);
                LinearLayout dtag_row= new LinearLayout(this);          
                LinearLayout dsummary_row= new LinearLayout(this);
                LinearLayout dingre_row= new LinearLayout(this); 
                LinearLayout dstep_row= new LinearLayout(this);

                final TextView did_  = new TextView(this);
                final TextView dname_ = new TextView(this);
                final TextView dtag_ = new TextView(this);
                final TextView dsummary_ = new TextView(this);
                final TextView dingre_ = new TextView(this);
                final TextView dstep_ = new TextView(this);
                final TextView   sep  = new TextView(this);

                String DID = allrows.getString(0);
                String DNAME= allrows.getString(1);
                String DTAG= allrows.getString(2);
                String DSUMMARY= allrows.getString(3);
                String DINGRE= allrows.getString(4);
                String DSTEP= allrows.getString(5);

                System.out.println("DNAME " + allrows.getString(cindex) + " DTAG : "+ allrows.getString(cindex1) + " DSUMMARY : "+ allrows.getString(cindex2) + " DINGRE : "+ allrows.getString(cindex3) + " DSTEP : "+ allrows.getString(cindex4));
                System.out.println("DID : "+ DID  + " || DNAME " + DNAME + "|| DTAG : "+ DTAG + "|| DSUMMARY : "+ DSUMMARY + "|| DINGRE : "+ DINGRE + "|| DSTEP : "+ DSTEP);

                did_.setText("ID : " + DID);
                did_row.addView(did_);
                Linear.addView(did_row);

                dname_.setText("NAME : "+ DNAME);
                dname_row.addView(dname_);
                Linear.addView(dname_row);

                dtag_.setText("TAG : " + DTAG);
                dtag_row.addView(dtag_);
                Linear.addView(dtag_row);

                dsummary_.setText("SUMMARY : "+ DSUMMARY);
                dsummary_row.addView(dsummary_);
                Linear.addView(dsummary_row);

                dingre_.setText("INGREDIENT : " + DINGRE);
                dingre_row.addView(dingre_);
                Linear.addView(dingre_row);

                dstep_.setText("STEP : "+ DSTEP);
                dstep_row.addView(dstep_);
                Linear.addView(dstep_row);

                sep.setText("---------------------------------------------------------------");
                Linear.addView(sep);
            }
            while(allrows.moveToNext());
        }
        mydb.close();
     }
}

Can someone show me the way of insertion image (png) into the database and also showing the image? Thank you.

You can save image path in database and save image in folder . this is best way — Nirav Ranpara, Nov 27 '12 at 12:34
Has been asked & answered here for example http://stackoverflow.com/questions/7331310/how-to-store-image-as-blob-in-sqlite-how-to-retrieve-it - but storing just the path to the image is usually a better idea. Storing small icons would be okay — zapl, Nov 27 '12 at 12:43
@NiravRanpara So, if the image is in this path, /SqlRecipe/res/drawable-hdpi/img1.png, I have to save this in my insertion as text? — , Nov 27 '12 at 12:48
@zapl I seen the link too, but the image is taken from the website. I had my own images. Thanks! — , Nov 27 '12 at 12:50
You can save only image name. and using Environment.getExternalStorageDirectory().getAbsolutePath() get image path — Nirav Ranpara, Nov 27 '12 at 12:53
@NiravRanpara Okay, I'll try that. Then how can I retrieve the images from database? — , Nov 27 '12 at 12:57
you have to get only image name from database . and using this Environment.getExternalStorageDirectory().getAbsolutePath() get image path you can get complete path. set this path in imageview — Nirav Ranpara, Nov 27 '12 at 13:01
Why this saving image to a folder and link to db is better? Do you have any performance benchmarks to prove that it is slower? Otherwise, it creates duplicated data which needs to be managed, many operations (e.g. deleting) need to be doubled and more complex than one SQL. — JaakL, Nov 27 '12 at 13:08

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

You have to insert the image as blob data. You can look into this link.

You can also refer Link1

edited May 23 '17 at 11:48

Community

1
1

answered Nov 27 '12 at 12:58

kittu88

2,451
5
40
80

Insertion of images to SQL

1 Answers1