How to store image in SQLite database
You have to use "blob" to store image.
ex: to store a image in to db:
public void insertImg(int id , Bitmap img ) {
byte[] data = getBitmapAsByteArray(img); // this is a function
insertStatement_logo.bindLong(1, id);
insertStatement_logo.bindBlob(2, data);
insertStatement_logo.executeInsert();
insertStatement_logo.clearBindings() ;
}
public static byte[] getBitmapAsByteArray(Bitmap bitmap) {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
bitmap.compress(CompressFormat.PNG, 0, outputStream);
return outputStream.toByteArray();
}
To retrieve a image from db:
public Bitmap getImage(int i){
String qu = "select img from table where feedid=" + i ;
Cursor cur = db.rawQuery(qu, null);
if (cur.moveToFirst()){
byte[] imgByte = cur.getBlob(0);
cur.close();
return BitmapFactory.decodeByteArray(imgByte, 0, imgByte.length);
}
if (cur != null && !cur.isClosed()) {
cur.close();
}
return null;
}
How can we insert an image in sqlite database(table)?
You can store an image as a BLOB, but you'd have to insert it as a a series of bytes using something like :-
INSERT INTO CUSTOMER (image_column, other_column) VALUES(x'0001020304........','data for the first other column');
So you'd need to convert the file into a hex string to save it.
However, it's not really recommended to store images but to rather store the path to the image and then retrieve the file when you want to display/use the image.
Saying that, SQLite can, for smaller images (say 100K), actually be more efficient 35% Faster Than The Filesystem.
How to insert your own image into a SQLite table?
One of the most important factors to consider is the size of the images. If the size is likely to be in excess of say 200k then there is an increasing risk of there being issues not with storing the image in the database but retrieving the image.
Without writing your own alternative of CursorWindow then a CursorWindow (a buffer to hold a sub-group of the rows in a Cursor) is limited to 2M in size (in later versions at some it I believe it was 1M). If a picture is approaching 2MB there is no way that the picture can be retrieved (even without considering space for other columns).
As such, the generally recommended approach is to not store images but store a means of retrieving the image from a file store elsewhere (you've mentioned the res folder, which could be fine but you may need to consider the size of the APK), you could then store the file path in the database.
There's a more comprehensive answer that covers the above including storing small images in the DB and larger images elsewhere (assets folder) here
How to insert and retrieve image from Sqlite database in Android Studio
The messsage indicates that bytes is null. The documentation for getBlob states :-
The result and whether this method throws an exception when the column
value is null or the column type is not a blob type is
implementation-defined.
As such I believe that getBlob is returning a null and therefore that the likliehood is that nulls are getting inserted.
Consider the following based upon your DatabaseHelper :-
mDB = new DatabaseHelper(this);
mDB.addData("Test001", "Test001", "email", "password", "xxc", null);
mDB.addData("Test002", "Test002", "email", "password", "xxc", new byte[]{0});
Cursor csr = mDB.getData();
DatabaseUtils.dumpCursor(csr);
while (csr.moveToNext()) {
bytes = csr.getBlob(6);
if (bytes == null) {
Log.d("OUCH", "Row " + String.valueOf(csr.getPosition()) + " is null");
} else {
Log.d("OK", "Row " + String.valueOf(csr.getPosition()) + " has byte array of length " + bytes.length);
}
Log.d("REPLICATE"," byte array length is " + bytes.length);
}
This adds two rows the first with null as the byte[] (image), the second has a valid albeit it short byte[].
The rows are inserted without issue.
The Data is extract without issue.
However the log will contain the following :-
2019-01-09 14:15:31.622 2783-2783/ptfc.populatetablefromcursor D/mydb: adding : Test001 TO mytable
2019-01-09 14:15:31.623 2783-2783/ptfc.populatetablefromcursor D/mydb: adding : Test002 TO mytable
2019-01-09 14:15:31.624 2783-2783/ptfc.populatetablefromcursor I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@453edcd
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: 0 {
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: ID=1
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: fn=Test001
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: ln=Test001
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: e_m=email
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: pass=password
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: mobnum=xxc
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: image=null
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: }
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: 1 {
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: ID=2
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: fn=Test002
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: ln=Test002
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: e_m=email
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: pass=password
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: mobnum=xxc
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: image=<unprintable>
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: }
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: 2 {
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: ID=3
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: fn=Test001
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out: ln=Test001
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out: e_m=email
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out: pass=password
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out: mobnum=xxc
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out: image=null
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out: }
2019-01-09 14:15:31.629 2783-2783/ptfc.populatetablefromcursor I/System.out: <<<<<
2019-01-09 14:15:31.629 2783-2783/ptfc.populatetablefromcursor D/OUCH: Row 0 is null
2019-01-09 14:15:31.630 2783-2783/ptfc.populatetablefromcursor D/AndroidRuntime: Shutting down VM
2019-01-09 14:15:31.632 2783-2783/ptfc.populatetablefromcursor E/AndroidRuntime: FATAL EXCEPTION: main
Process: ptfc.populatetablefromcursor, PID: 2783
java.lang.RuntimeException: Unable to start activity ComponentInfo{ptfc.populatetablefromcursor/ptfc.populatetablefromcursor.MainActivity}: java.lang.NullPointerException: Attempt to get length of null array
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2914)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3049)
at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1809)
at android.os.Handler.dispatchMessage(Handler.java:106)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:6680)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
Caused by: java.lang.NullPointerException: Attempt to get length of null array
at ptfc.populatetablefromcursor.MainActivity.onCreate(MainActivity.java:40)
at android.app.Activity.performCreate(Activity.java:7136)
at android.app.Activity.performCreate(Activity.java:7127)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2894)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3049)
at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1809)
at android.os.Handler.dispatchMessage(Handler.java:106)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:6680)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
The stackTrace is similar. showing that bytes.length results in the failure if a null is inserted.
There are many ways this could be fixed for example you could set the image column to have a DEFAULT value along with skipping the cv.put if the ProfileImage passed to the addData method is null e.g.
in the Database helper change + image + " BLOB ')"; to + image + " BLOB DEFAULT X'00')";
along with the following change in the addData method :-
if (Profileimg != null) {
cv.put(image, Profileimg);
}
- I can't recall how this will affect the image in the listview, although I think it handles it.
However, the root cause will be that the picture taking will be returning a null.
Additionally you will likely encounter other issues if the images
themselves are large as there are limitations (1M more recently 2M)
with the size of data that a CursorWindow (used by a Cursor) can
handle exceed or get close to 2M with 1 image and an exception is
guaranteed. With 1M images a CursorWindow will hold 1 at the most if
at all so you'd expect display issues.If images average around 100k then they can be stored in the DB and a
search could reveal the reasoning behind how SQlite can be more
efficient than a file system.
How can I add image file in SQLite database using Flask, Python
At first I want to say that storing large and medium pictures directly in SQLite is not a very good solution. Learn more here: https://www.sqlite.org/intern-v-extern-blob.html
For your problem, try to do as in the documentation:
https://flask.palletsprojects.com/en/1.1.x/patterns/fileuploads/
Note there is used:
# check if the post request has the file part
if 'file' not in request.files:
flash('No file part')
return redirect(request.url)
file = request.files['file']
# if user does not select file, browser also
# submit an empty part without filename
if file.filename == '':
flash('No selected file')
return redirect(request.url)
And perhaps this article will also help you:
Python SQLite BLOB to Insert and Retrieve file and images
Storing images in sqlite - Django
Django's ImageField and FileField are both just links to the location where the file is actually uploaded.
If you read Django's documentation on how to use imageField you may observe a upload_to attribute.
Both Fields are implemented in Django's ORM and are DB agnostic (i.e. should work on SQLite, MySQL or any other DB supported by Django).
You can check this out for examples on how to mange files.
The first example in the link shows a Car model and uploads the image to cars under the MEDIA_ROOT folder.
from django.db import models
class Car(models.Model):
name = models.CharField(max_length=255)
price = models.DecimalField(max_digits=5, decimal_places=2)
photo = models.ImageField(upload_to='cars')
Alternative
If you really need the image to live in your database you can always utilize django's BinaryField and save the whole image as BLOB.
from django.db import models
class Car(models.Model):
name = models.CharField(max_length=255)
price = models.DecimalField(max_digits=5, decimal_places=2)
photo_as_blob = models.BinaryField() # save photo as binary object
As you can see from other answers it is not generally considered a good practice to save big files directly in DB.
How to insert an Image to sqlite database
I solved the problem by placing:
Bitmap bmp = MediaStore.Images.Media.getBitmap(this.getContentResolver(), imageUri);
alone in the try-catch clause. I also replaced the:
db.insertImage(cv,editword,editcategory);
Toast.makeText(this, "Image Uploaded", Toast.LENGTH_SHORT).show();
in a new function
how to store a jpg in an sqlite database with python
Finally I got it working thanks to Andrej Kesely's comment. The working solution is
imgobj = base64.b64encode(open('./fotocopies/checks/624.jpg').read())
con = sqlite3.connect("pybook.db")
cur = con.cursor()
qf="UPDATE data_fotocopies SET fotocopy='%s' WHERE refid=%d AND reftype=0"%(lite.Binary(fotocopy_blob),id)
cur.execute(qf) #yes, it is dangerous for injection`
and retrieving the image from the database is done as:
qf="SELECT fotocopy FROM data_fotocopies WHERE refid=%d and reftype=0"%self.check_id
self.cur.execute(qf)
try:
fd=base64.b64decode(self.cur.fetchall()[0][0])
byting = GLib.Bytes(fd)
self.fotocopy = Gio.MemoryInputStream.new_from_bytes(byting)
...
self.fotocopy_ent=self.builder.get_object("fotocopy") # as it is made in glade
pixbuf = GdkPixbuf.Pixbuf.new_from_stream(self.fotocopy,None) #finally the pixbuf although
#it produces errors if I have
#no stream/image to "feed" it.
self.fotocopy_ent.set_from_pixbuf(pixbuf)
Still can't figure out why all other solutions I've found don't work. I use Python 2.7.6 ang gtk3, but this one I subit does.
Thank you all for your help.
Related Topics
How to Finish an Android Application
How to Get Free and Total Size of Each Storagevolume
How to Find Intent Source in Android
Load Large Images with Picasso and Custom Transform Object
How to Pop Fragment Off Backstack
Android Listview Setselection() Does Not Seem to Work
Android Studio: Unable to Start the Daemon Process
How to Tell Which App Was Selected by Intent.Createchooser
Start a Fragment via Intent Within a Fragment
Render Error in Android Studio 3.0 Layout Editor
How to Create Always-Top Fullscreen Overlay Activity in Android
How to Run Opencv Code Without Opencv Manager
Volley Out of Memory Error, Weird Allocation Attempt
Error Non-Default Constructors in Fragments
Bitmap, Bitmap.Recycle(), Weakreferences, and Garbage Collection
How to Correctly Start Activity from Postexecute in Android